Importing through a form

[paul123456]

Hi i am have one of the employees log in to the server and open the back-end database so that the person can import information from an excel file in the database..is there a way to import from the front end database? i would like an option on the switchboard manager that has import on it and basically runs the import wizard. is this possible to do? Thanks, PAUL

 

[HESCott]

I think I understand what you're trying to do.

There is a data file which is the same format every time, but with new data. You want a user to be able to press a button to import the data automatically.

Look at the "DoCmd.TransferText" method. You will need to create an import specification for the data using the "Advanced" feature of the import wizard, save the specification, and then reference it in the call to TransferText.

Play with it a bit and then let me know if you have any questions.

 

[paul123456]

I can't find the advance feature for the import? were is this located. I am using Office Xp Thanks, PAUL

 

[HESCott]

I apologize - the "Advanced" button is available for a Text Import, but not for an import from an Excel file.

You actually need to use the "TransferSpreadsheet" method, which does not require an import specification.

Mea culpa.

 

[paul123456]

Works great..what i did is created a macro. do you know of a way that i can specify were the file is located every time? instead of putting it in the macro? Thanks, PAUL

 

[HESCott]

I have some code that you can drop into a module (which I found right here on Tek-Tips) that allows you to do this. (I wish I could find the exact post that I copied this from so I could give proper credit - I have done several searches, but I cannot find the specific post or FAQ.)

In Access, open a Module, New. Then paste in this code:

'*********************** BEGIN COPY *************************
'the open filename api
Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As gFILE) As Long

' the gFILE type needed by the open filename api
Type gFILE
  lStructSize As Long
  hwndOwner As Long
  hInstance As Long
  lpstrFilter As String
  lpstrCustomFilter As String
  nMaxCustFilter 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
  lCustData As Long
  lpfnHook As Long
  lpTemplateName As String
  
End Type


Function FileToOpen(Optional StartLookIn) As String
'Purpose: Calls the open file api to let the user select the file to open
'returns: string value which contains the path to the file selected. "" = no file seleted

  Dim ofn As gFILE
  Dim path As String
  Dim filename As String
  Dim a As String
  
StartOver:
ofn.lStructSize = Len(ofn)
ofn.lpstrFilter = "Text Files (*.csv)" _
   + Chr$(0) + "*.csv" + Chr$(0) + "All Files (*.*)" + Chr$(0) + "*.*" + Chr$(0)
ofn.lpstrFile = Space$(254)
ofn.nMaxFile = 255
ofn.lpstrFileTitle = Space$(254)
ofn.nMaxFileTitle = 255

If Not IsMissing(StartLookIn) Then
  ofn.lpstrInitialDir = StartLookIn
Else
  ofn.lpstrInitialDir = "c:\some default directory"
End If

ofn.lpstrTitle = "Please find and select the document to open"
ofn.flags = 0

a = GetOpenFileName(ofn)
If (a) Then
  path = Trim(ofn.lpstrFile)
  filename = Trim(ofn.lpstrFileTitle)
  If Dir(path) <> &quot;&quot; Then FileToOpen = -1
  FileToOpen = Trim(ofn.lpstrFile)
Else
  FileToOpen = &quot;&quot;
  path = &quot;&quot;
  filename = &quot;&quot;
End If

FileToOpen = path

End Function

'*********************** END COPY *************************

Once this module has been saved, you can put this code behind the button click:

  Dim filename As String
  filename = FileToOpen()
   
  If (Len(filename)) Then
    DoCmd.Hourglass True
    DoCmd.TransferSpreadheet (w/your parameters)
    DoCmd.Hourglass False
  End If

 

[HESCott]

Actually, there's at least one line in there that you're going to want to change. The line:

ofn.lpstrFilter = "Text Files (*.csv)" _
   + Chr$(0) + "*.csv" + Chr$(0) + "All Files (*.*)" + Chr$(0) + "*.*" + Chr$(0)

would better suit your purposes if changed to:

ofn.lpstrFilter = "Microsoft Excel (*.xls)" _
   + Chr$(0) + "*.xls" + Chr$(0) + "All Files (*.*)" + Chr$(0) + "*.*" + Chr$(0)

(Obviously I use this to do a text import, but the process should work fine with a spreadsheet import.)

 

[paul123456]

For some reason its not working for me...this is what ive done i copied this exact thing into a new module and saved it

'*********************** BEGIN COPY *************************
'the open filename api
Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As gFILE) As Long

' the gFILE type needed by the open filename api
Type gFILE
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter 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
lCustData As Long
lpfnHook As Long
lpTemplateName As String

End Type


Function FileToOpen(Optional StartLookIn) As String
'Purpose: Calls the open file api to let the user select the file to open
'returns: string value which contains the path to the file selected. "" = no file seleted

Dim ofn As gFILE
Dim path As String
Dim filename As String
Dim a As String

StartOver:
ofn.lStructSize = Len(ofn)
ofn.lpstrFilter = "Microsoft Excel (*.xls)" _
+ Chr$(0) + "*.xls" + Chr$(0) + "All Files (*.*)" + Chr$(0) + "*.*" + Chr$(0)
ofn.lpstrFile = Space$(254)
ofn.nMaxFile = 255
ofn.lpstrFileTitle = Space$(254)
ofn.nMaxFileTitle = 255

If Not IsMissing(StartLookIn) Then
ofn.lpstrInitialDir = StartLookIn
Else
ofn.lpstrInitialDir = "c:\some default directory"
End If

ofn.lpstrTitle = "Please find and select the document to open"
ofn.flags = 0

a = GetOpenFileName(ofn)
If (a) Then
path = Trim(ofn.lpstrFile)
filename = Trim(ofn.lpstrFileTitle)
If Dir(path) <> &quot;&quot; Then FileToOpen = -1
FileToOpen = Trim(ofn.lpstrFile)
Else
FileToOpen = &quot;&quot;
path = &quot;&quot;
filename = &quot;&quot;
End If

FileToOpen = path

End Function

'*********************** END COPY *************************



then i created a button and on click i event procedure i put:

Dim filename As String
filename = FileToOpen()

If (Len(filename)) Then
DoCmd.Hourglass True
DoCmd.TransferSpreadheet (w/your parameters)
DoCmd.Hourglass False
End If

when i execute the button it give me an error and goes to the button event procedure?
Thanks, PAUL

 

[paul123456]

I think the errror is coming from (w/your parameters)
in the button command? what do i put for this? Thanks, PAUL

 

[HESCott]

Sorry, Paul!

Here - try this line in place of the one with &quot;(w/ your parameters)&quot;:

    DoCmd.TransferSpreadheet acImport, acSpreadsheetTypeExcel9, "TableNameToPutDataIn", filename, True

Some things you may need to change:

1.) Replace the text TableNameToPutDataIn with the name of the table that you want the data imported into

2.) acSpreadsheetTypeExcel9 - replace the final character &quot;9&quot; with 3, 4, 5, 7 or 8 to reflect the version of the Excel spreadsheet being imported (Office XP=9).

3.) If the first row of the spreadsheet does NOT contain field names, you must change the last value on the line (True) to False.

4.) If you need to import just a range of cells from your worksheet, you can add another ,&quot;parameter&quot; to this line where &quot;parameter&quot; identifies a valid cell range or the name of a range in the worksheet.