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
Import an Excel Spreadsheet Without a Specified Name
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import an Excel Spreadsheet Without a Specified Name
Will there be just a single .xlsx workbook in the C:\Walmart\Import-CDS\Dominos folder?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Import an Excel Spreadsheet Without a Specified Name
Hi Hans,
Yes, they only anticipate 1 file at this point.
Thanks,
Leesha
Yes, they only anticipate 1 file at this point.
Thanks,
Leesha
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import an Excel Spreadsheet Without a Specified Name
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
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Import an Excel Spreadsheet Without a Specified Name
Thanks Hans!
That worked like a charm!
Leesha
That worked like a charm!
Leesha