Import file from Excel into Access macro - VBA

S

StephanieM

I need to do something simple. How can I automate being able to
import a file into access that I get to select. I have some files
that I want to append together, but the file names vary slightly each
week.

any thoughts on how to do this?

Thanks!
Stephanie
 
F

fredg

I need to do something simple. How can I automate being able to
import a file into access that I get to select. I have some files
that I want to append together, but the file names vary slightly each
week.

any thoughts on how to do this?

Thanks!
Stephanie
Click to expand...

Does the user know the actual name of the file?
You can use code to import the Excel file.

Assuming the you know where the file is stored.....

Dim strFile as String
strFile = InputBox("What is the name of the file, i.e.
Sales2007Week2")

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"MyTableName", _
"c:\MyFolderPath\" & strFile & ".xls", False, "WorksheetName!A1:C30"

When prompted, enter the name of the workbook (without the .xls
extension). Change MyFolderPath to the path to the folder, and
MyTableName to whatever the Access table name is.
 
S

StephanieM

Does the user know the actual name of the file?
You can use code to import the Excel file.

Assuming the you know where the file is stored.....

Dim strFile as String
strFile = InputBox("What is the name of the file, i.e.
Sales2007Week2")

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"MyTableName", _
"c:\MyFolderPath\" & strFile & ".xls", False, "WorksheetName!A1:C30"

When prompted, enter the name of the workbook (without the .xls
extension). Change MyFolderPath to the path to the folder, and
MyTableName to whatever the Access table name is.
Click to expand...

I do know the file path, thanks! This is what I need to put into a
module right?
 
F

fredg

I do know the file path, thanks! This is what I need to put into a
module right?
Click to expand...

You can put it into a Module or directly in the click event of a
command button on a form. It depends upon how you expect to call it.
 
Top