UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Importing Standard spreadsheet template    
 
   
parekhs
post Jun 3 2004, 04:40 PM
Post#1



Posts: 3
Joined: 21-May 04



Hello everyone
I have a question and thought somebody might be able to help. I have created an excel spreadsheet template that is available to our customers to populate. There are 2 fields in the template which are a code and a rate. I need to import this into a table that has the customerid, code and rate. I am able to import just the code and the rateby itself into a table that has those 2 columns through a macro, but I need to have an interface where one of our users can enter a customerid to go along with the other fields. It almost seems to me that the data from the excel spreadsheet will have to be held at a temporary location and then somehow appended with the customerid. Also, every time I run this macro, I need the user to be able to specify the location of the file as well instead of it being hardcoded. Does anybody have an idea on how to do something like this?
Any help will be appreciated.
Thanks
Sameer
Go to the top of the page
 
kfield7
post Jun 3 2004, 07:15 PM
Post#2



Posts: 841
Joined: 12-November 03
From: Iowa Lot


There are posts on this site related to both your questions.
Regarding importing, I typically use a temporary table, but here is some interesting discussion:
ExcelImport
My method would be something like to use VB code (see "transfer" in help) to
1) import to temp table
2) examine/edit the table
3) use query to import the cleaned up table to the destination table
4) examine data not imported
do a search on files, explorer, or API to find methods of pulling up a Windows API explorer-stye file requester. Let me know if you can't find one, I have some code attached to 2 or 3 of my databases.
Go to the top of the page
 
parekhs
post Jun 4 2004, 09:43 AM
Post#3



Posts: 3
Joined: 21-May 04



KField7
Thanks for the help
I think the transfer does work. I tried a little sample out and it seems ok. The only thing I am not sure of is that when doing something like that, I have to make sure there is only one user trying to do it. Since the data will have to undergo some modification in the temp table, I cannot let multiple users access it at the same time.
Also, I was not able to find any information on using the windows api explorer style file requestor. I would appreciate if you would be able to help me in that direction. Also, I was planning to use a macro that does the transfer, and it seems that the file location is a required attribute, and I do not know how to make it a parameter. Maybe the code you have does that, but if not let me know if you do know something about it.
Thanks
Go to the top of the page
 
kfield7
post Jun 4 2004, 02:50 PM
Post#4



Posts: 841
Joined: 12-November 03
From: Iowa Lot


users: My work has not often required me to restrict users. try a search on this site for "restrict" or something like that. also search Access help menu. I think you open Access with a certain command to prevent other users from getting on.
et excel sheet, from a button on a form:
Private Sub bGetSS_Click() 'name of my button
'Get sheet name
Dim sOpenFile As String
'
'display the open file dialog box
sOpenFile = OpenFileDialog("Import Data File")
If sOpenFile <> "" Then
'the user has selected a file to import, the filename and path
'are contained in the variable sOpenFile
sOpenFile = Trim$(sOpenFile) 'remove all trailing spaces
'make sure it's an Excel file
If Right(sOpenFile, 4) <> ".xls" Then
MsgBox "You must select an Excel (.xls) file.", vbOKOnly
Else
KRunQuery ("qImportDeleteTableExcel")' just a call to the same query running code that Access Wizard generates, which I saved as a subroutine. This query deletes the data in the temporary import table. You would need to create your own delete query, or substitute code to do so.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblImportExcel", sOpenFile, True
MsgBox "Initial transfer from spreadsheet to Import Table is COMPLETE! Proceed to next step.", vbOKOnly
End If
End If
End Sub
----------------
file dialog -- place this in a module
Option Explicit ' Require variables to be declared before being used.
Option Compare Database ' Use database order for string comparisons.
Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Boolean
Declare Function GetSaveFileName Lib "comdlg32.dll" Alias _
"GetSaveFileNameA" (pOpenfilename As OPENFILENAME) As Boolean
Type MSA_OPENFILENAME
' Filter string used for the Open dialog filters.
' Use MSA_CreateFilterString() to create this.
' Default = All Files, *.*
strFilter As String
' Initial Filter to display.
' Default = 1.
lngFilterIndex As Long
' Initial directory for the dialog to open in.
' Default = Current working directory.
strInitialDir As String
' Initial file name to populate the dialog with.
' Default = "".
strInitialFile As String
strDialogTitle As String
' Default extension to append to file if user didn't specify one.
' Default = System Values (Open File, Save File).
strDefaultExtension As String
' Flags (see constant list) to be used.
' Default = no flags.
lngFlags As Long
' Full path of file picked. When the File Open dialog box is
' presented, if the user picks a nonexistent file,
' only the text in the "File Name" box is returned.
strFullPathReturned As String
' File name of file picked.
strFileNameReturned As String
' Offset in full path (strFullPathReturned) where the file name
' (strFileNameReturned) begins.
intFileOffset As Integer
' Offset in full path (strFullPathReturned) where the file extension begins.
intFileExtension As Integer
End Type
Const ALLFILES = "All Files"
Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As Long
nMaxCustrFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustrData As Long
lpfnHook As Long
lpTemplateName As Long
End Type
Const OFN_ALLOWMULTISELECT = &H200
Const OFN_CREATEPROMPT = &H2000
Const OFN_EXPLORER = &H80000
Const OFN_FILEMUSTEXIST = &H1000
Const OFN_HIDEREADONLY = &H4
Const OFN_NOCHANGEDIR = &H8
Const OFN_NODEREFERENCELINKS = &H100000
Const OFN_NONETWORKBUTTON = &H20000
Const OFN_NOREADONLYRETURN = &H8000
Const OFN_NOVALIDATE = &H100
Const OFN_OVERWRITEPROMPT = &H2
Const OFN_PATHMUSTEXIST = &H800
Const OFN_READONLY = &H1
Const OFN_SHOWHELP = &H10
Function OpenFileDialog(DialogTitle As String) As String
Dim msaof As MSA_OPENFILENAME

' Set options for the dialog box.
msaof.strDialogTitle = DialogTitle
msaof.strFilter = MSA_CreateFilterString("Excel Files", "*.xls", "All Files", "*.*")

' Call the Open dialog routine.
MSA_GetOpenFileName msaof

' Return the path and file name.
OpenFileDialog = Trim(msaof.strFullPathReturned)
End Function
Function MSA_CreateFilterString(ParamArray varFilt() As Variant) As String
' Creates a filter string from the passed in arguments.
' Returns "" if no argumentss are passed in.
' Expects an even number of argumentss (filter name, extension), but
' if an odd number is passed in, it appends "*.*".

Dim strFilter As String
Dim intRet As Integer
Dim intNum As Integer
intNum = UBound(varFilt)
If (intNum <> -1) Then
For intRet = 0 To intNum
strFilter = strFilter & varFilt(intRet) & vbNullChar
Next
If intNum Mod 2 = 0 Then
strFilter = strFilter & "*.*" & vbNullChar
End If

strFilter = strFilter & vbNullChar
Else
strFilter = ""
End If
MSA_CreateFilterString = strFilter
End Function
Function MSA_ConvertFilterString(strFilterIn As String) As String
' Creates a filter string from a bar ("|") separated string.
' The string should pairs of filter|extension strings, i.e. "Access Databases|*.mdb|All Files|*.*"
' If no extensions exists for the last filter pair, *.* is added.
' This code will ignore any empty strings, i.e. "||" pairs.
' Returns "" if the strings passed in is empty.

Dim strFilter As String
Dim intNum As Integer, intPos As Integer, intLastPos As Integer
strFilter = ""
intNum = 0
intPos = 1
intLastPos = 1
' Add strings as long as we find bars.
' Ignore any empty strings (not allowed).
Do
intPos = InStr(intLastPos, strFilterIn, "|")
If (intPos > intLastPos) Then
strFilter = strFilter & Mid(strFilterIn, intLastPos, intPos - intLastPos) & vbNullChar
intNum = intNum + 1
intLastPos = intPos + 1
ElseIf (intPos = intLastPos) Then
intLastPos = intPos + 1
End If
Loop Until (intPos = 0)

' Get last string if it exists (assuming strFilterIn was not bar terminated).
intPos = Len(strFilterIn)
If (intPos >= intLastPos) Then
strFilter = strFilter & Mid(strFilterIn, intLastPos, intPos - intLastPos + 1) & vbNullChar
intNum = intNum + 1
End If

' Add *.* if there's no extension for the last string.
If intNum Mod 2 = 1 Then
strFilter = strFilter & "*.*" & vbNullChar
End If

' Add terminating NULL if we have any filter.
If strFilter <> "" Then
strFilter = strFilter & vbNullChar
End If

MSA_ConvertFilterString = strFilter
End Function
Private Function MSA_GetSaveFileName(msaof As MSA_OPENFILENAME) As Integer
' Opens the file save dialog.

Dim of As OPENFILENAME
Dim intRet As Integer
MSAOF_to_OF msaof, of
of.Flags = of.Flags Or OFN_HIDEREADONLY
intRet = GetSaveFileName(of)
If intRet Then
OF_to_MSAOF of, msaof
End If
MSA_GetSaveFileName = intRet
End Function
Function MSA_SimpleGetSaveFileName() As String
' Opens the file save dialog with default values.
Dim msaof As MSA_OPENFILENAME
Dim intRet As Integer
Dim strRet As String

intRet = MSA_GetSaveFileName(msaof)
If intRet Then
strRet = msaof.strFullPathReturned
End If

MSA_SimpleGetSaveFileName = strRet
End Function
Private Function MSA_GetOpenFileName(msaof As MSA_OPENFILENAME) As Integer
' Opens the Open dialog.

Dim of As OPENFILENAME
Dim intRet As Integer
MSAOF_to_OF msaof, of
intRet = GetOpenFileName(of)
If intRet Then
OF_to_MSAOF of, msaof
End If
MSA_GetOpenFileName = intRet
End Function
Function MSA_SimpleGetOpenFileName() As String
' Opens the Open dialog with default values.
Dim msaof As MSA_OPENFILENAME
Dim intRet As Integer
Dim strRet As String

intRet = MSA_GetOpenFileName(msaof)
If intRet Then
strRet = msaof.strFullPathReturned
End If

MSA_SimpleGetOpenFileName = strRet
End Function
Private Sub OF_to_MSAOF(of As OPENFILENAME, msaof As MSA_OPENFILENAME)
' This sub converts from the Win32 structure to the Microsoft Access structure.

msaof.strFullPathReturned = Left(of.lpstrFile, InStr(of.lpstrFile, vbNullChar) - 1)
msaof.strFileNameReturned = of.lpstrFileTitle
msaof.intFileOffset = of.nFileOffset
msaof.intFileExtension = of.nFileExtension
End Sub
Private Sub MSAOF_to_OF(msaof As MSA_OPENFILENAME, of As OPENFILENAME)
' This sub converts from the Microsoft Access structure to the Win32 structure.

Dim strFile As String * 512
' Initialize some parts of the structure.
of.hwndOwner = Application.hWndAccessApp
of.hInstance = 0
of.lpstrCustomFilter = 0
of.nMaxCustrFilter = 0
of.lpfnHook = 0
of.lpTemplateName = 0
of.lCustrData = 0

If msaof.strFilter = "" Then
of.lpstrFilter = MSA_CreateFilterString(ALLFILES)
Else
of.lpstrFilter = msaof.strFilter
End If
of.nFilterIndex = msaof.lngFilterIndex

of.lpstrFile = msaof.strInitialFile _
& String(512 - Len(msaof.strInitialFile), 0)
of.nMaxFile = 511
of.lpstrFileTitle = String(512, 0)
of.nMaxFileTitle = 511
of.lpstrTitle = msaof.strDialogTitle
of.lpstrInitialDir = msaof.strInitialDir

of.lpstrDefExt = msaof.strDefaultExtension
of.Flags = msaof.lngFlags

of.lStructSize = Len(of)
End Sub
Go to the top of the page
 
parekhs
post Jun 4 2004, 05:18 PM
Post#5



Posts: 3
Joined: 21-May 04



kfield7
Oappreciate your help
It works like a champ
Thanks
Go to the top of the page
 
kfield7
post Jun 4 2004, 11:05 PM
Post#6



Posts: 841
Joined: 12-November 03
From: Iowa Lot


You're very welcome.
Can't take too much credit though, I don't recall where I got the file dialog code, it looks like MS reference material. If I knew from whence it came, I would give proper credit.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th May 2018 - 05:11 AM