Import file from Excel into Access macro

Import file from Excel into Access macro - VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

StephanieM - 29 May 2007 22:45 GMT

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

Reply to this Message

fredg - 29 May 2007 23:46 GMT

> 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
[quoted text clipped - 5 lines]
> Thanks!
> Stephanie

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.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

StephanieM - 29 May 2007 23:59 GMT

> > 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
[quoted text clipped - 27 lines]
> Please respond only to this newsgroup.
> I do not reply to personal e-mail

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

Reply to this Message

fredg - 30 May 2007 00:04 GMT

>>> 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
[quoted text clipped - 30 lines]
> I do know the file path, thanks! This is what I need to put into a
> module right?

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.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message