Import an Excel Spreadsheet Without a Specified Name

[Leesha]

Hi,
I have a user that needs to import a spreadsheet on a monthly basis and the name will not always be the same. The folder destination will be. I'm wondering what the code would look like. They are using .xlsx format for the spreadsheet. This is what I've been using but it won't work now that the file name will change.

'Imports Domino's spreadsheet
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblDominosImportTemp", "C:\Walmart\Import-CDS\Dominos\Dominos.xlsx", True, ""


Thanks!
Leesha

[HansV]

Will there be just a single .xlsx workbook in the C:\Walmart\Import-CDS\Dominos folder?

[Leesha]

Hi Hans,
Yes, they only anticipate 1 file at this point.
Thanks,
Leesha

[HansV]

You could do it like this:

Code: Select all

    Dim strFolder As String
    Dim strFile As String
    ' Fixed folder path
    strFolder = "C:\Walmart\Import-CDS\Dominos\"
    ' Find filename
    strFile = Dir(strFolder & "*.xlsx")
    ' Get out if there is no .xlsx workbook
    If strFile = "" Then
        MsgBox "No .xlsx file in folder!", vbExclamation
        Exit Sub
    End If
    ' Import Domino's spreadsheet
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, _
        "tblDominosImportTemp", strFolder & strFile, True

[Leesha]

Thanks Hans!
That worked like a charm!
Leesha