How do I use DoCmd.TransferSpreadsheet with this code? 1

[scotton]

Below is the code that allows the user to select the file that needs to be imported and displays the file and it's path in a listbox. It works great except it doesn't take the next step of importing the file into the database as a table. How do you tell access to import the spreadsheet that is displayed in the FileList?

TIA,
Sarah

Option Compare Database
Option Explicit

Private Sub cmdFileDialog_Click()

' This requires a reference to the Microsoft Office 11.0 Object Library.

Dim fDialog As Office.FileDialog
Dim varFile As Variant

' Clear the list box contents.
Me.FileList.RowSource = ""

' Set up the File dialog box.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
' Allow the user to make multiple selections in the dialog box.
.AllowMultiSelect = True

' Set the title of the dialog box.
.Title = "Select One or More Files"

' Clear out the current filters, and then add your own.
.Filters.Clear
.Filters.Add "Access Databases", "*.MDB"
.Filters.Add "Access Projects", "*.ADP"
.Filters.Add "All Files", "*.*"

' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
If .Show = True Then
' Loop through each file that is selected and then add it to the list box.
For Each varFile In .SelectedItems
Me.FileList.AddItem varFile
Next
Else
MsgBox "You clicked Cancel in the file dialog box."
End If
End With
End Sub

 

[hneal98]

Personally, I would not waste the time putting the path in a list box.

You can just do the work in the for each loop that you have by putting in DoCmd.TransferSpreadsheet and the parameters that it requires instead of the adding the path to a list box.

Is there a reason you want to show the path in a listbox?

 

[scotton]

I wanted to have the spreadsheets listed so the user would see what they're importing into the database but it's not necessary. How do I incorporate the docmd.transferspreadsheet and call upon it when I don't know what spreadsheet the user is transfering?

TIA,

Sarah

 

[hneal98]

Here is what the Help shows for it:

expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

And here is an example of how to use it:

DoCmd.TransferSpreadsheet acImport, 3, "Employees","C:\Lotus\Newemps.wk3", True, "A1:G12"

In your code, at the bottom, instead of having this:

         For Each varFile In .SelectedItems
            Me.FileList.AddItem varFile
         Next

You can put something like this:

For Each varFile In .SelectedItems
            DoCmd.TransferSpreadsheet acImport, 3, TableNameToTransferFileTo,varFile, True, "A1:G12" 
      Next

Let me know if this is not clear.

 

[PHV]

Something like this ?
With fDialog
.AllowMultiSelect = True
.Title = "Select One or More Files"
.Filters.Clear
.Filters.Add "Excel Files", "*.XLS"
If .Show = True Then
For Each varFile In .SelectedItems
DoCmd.TransferSpreadsheet acImport, , , varFile
Next
Else
MsgBox "You clicked Cancel in the file dialog box."
End If
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[scotton]

Okay, I changed my code to this:
Option Compare Database
Option Explicit

Private Sub cmdFileDialog_Click()

' This requires a reference to the Microsoft Office 11.0 Object Library.

Dim fDialog As Office.FileDialog
Dim varFile As Variant

' Clear the list box contents.
Me.FileList.RowSource = ""

' Set up the File dialog box.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.AllowMultiSelect = True
.Title = "Select One or More Files"
.Filters.Clear
.Filters.Add "Excel Files", "*.XLS"
If .Show = True Then
For Each varFile In .SelectedItems
DoCmd.TransferSpreadsheet acImport, , , varFile
Next
Else
MsgBox "You clicked Cancel in the file dialog box."
End If
End With
End Sub

And I get this error message:
Run-time erro '2495':
The action or method requires a table name arguement. It highlights the DoCmd.Transferspreadsheet and I believe it's because I'm not referencing any particular named file. I can't hard code the name of the file in the code because it can change every single time the user imports a file.
Any suggestions? TIA, Sarah

 

[hneal98]

Where are you transfering the file to?

 

[scotton]

I am transfering the file into the access database. I have several excel spreadsheets that need to be imported into this database once a week, and I'm creating forms so that anyone else in HR can run this database without me being there. I'm trying to simplify the process for the enduser. Hope this answers your question, and I appreciate your help with this.
TIA,
Sarah

 

[hneal98]

You are not indicating where you want the file transfered in your example. You should look under help for the TransferSpreadsheet command.