Import from Excel using TransferSpreadsheet

[Sephaerius]

I have a small issue I think should be fairly easy to resolve, but I'm a beginner so I'm stuck.


I need to pull spreadsheets from Excel into an Access 2010 database I'm building. Here's the code I'm using right now.

Function import_hl7()

On Error GoTo import_hl7_Err    

DoCmd.TransferSpreadsheet acImport, 8, "master_table", "C:\Documents and Settings\redacted\Desktop\test\redacted.xls", True, ""

import_hl7_Exit:
    Exit Function

import_hl7_Err:
    MsgBox Error$
    Resume import_hl7_Exit

End Function

This works just fine. The problem is that each day the spreadsheet will have a different name, so ideally I would like to be able to import the Excel document I currently have open. That way I can just open 'todays' document and run the script to import it without worrying about paths and file names.

Thank you,

[June7]

Sorry, TransferSpreadsheet doesn't work that way.

What you can do is populate a variable with the path\filename then use that variable in TransferSpreadsheet.

One way to retrieve path\filename is with FileDialog. Review http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

Sub ImportExcel()
    ' Requires reference to Microsoft Office 11.0 Object Library.
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant
    Dim strFile As String
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
       .AllowMultiSelect = False
       .Title = "Please select files"
       .Filters.Clear
       .Filters.add "Access Databases", "*.XL*"
       If .Show = True Then
          For Each varFile In .SelectedItems
             DoCmd.TransferSpreadsheet acImport, 8, "master_table", strFile, True, ""
          Next
       Else
          MsgBox "You clicked Cancel in the file dialog box."
       End If
    End With
End Sub

[Sephaerius]

June7,
Thank you for that... I think this will suffice for my needs, but it isn't working just yet. After I select my Excel document I receive 'error 2522 The action or method requires a File Name argument.' Debug takes me to the TransferSpreadsheet line. I think it doesn't like the strFile variable. Thoughts?

[pbaldy]

FYI, you can use late binding with that, replacing these 2 lines:

  Dim fDialog                 As Object    'Office.FileDialog
  Set fDialog = Application.FileDialog(3)    'msoFileDialogFilePicker

[pbaldy]

Try replacing that variable with varFile

[June7]

Ooops, sorry, I didn't use the strFile variable after all. Paul is correct, can just use varFile.

[Sephaerius]

Yep, using varFile in place of strFile seems to work perfectly! Thanks everyone!