import excel into access 1

[cthai]

Hello -
I have a situation where I need to “import” an excel file into access but the file name constantly change. Is there a way I can import the file by the date? The file come in as ‘report20110419052731.xls’ the 20110419 is the date stamp. If this is not a good option – is there a way to create a button and when a user click on it they can choice the file they want to import?

any suggestion? thanks you

 

[PHV]

What is your actual code to import the excel file ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cthai]

this is the code i currently use and it does import the data to the table - but i have to change the report name everyday. i would like the code to pick the lastest xls from dailyreport folder and import it into Report table.

DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Report", "C:\dailyreport\'report20110419052731.xls'  ", True

 

[PHV]

Replace this:
"C:\dailyreport\'report20110419052731.xls' "
with this:
"C:\dailyreport\" & Dir("C:\dailyreport\'report" & Format(Now, "yyyymmdd") & "*.xls'")

BTW, you really have single quotes in the pathname ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cthai]

Thanks PHV!

that works wonderful!

sorry - it was a typo there is no single quotes in the pathname...