If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Import XLS file using browse

I have a table in access that I need to import an excel spreadsheet. I would like to be able to automate this as much as possible. Problem is that the file name changes everyday. My thoughts are that I could set a command button to open a browse menu and select the file then have access import the file.

Using the file system object allows you to inspect the extension and modified date of a file. Assuming that the Excel file is always in the same directory, you could code an import process to pick up the youngest Excel file that it finds there.

10% of magic is knowing something that no-one else does. The rest is misdirection.

Can't answer the first question since you haven't provided any details on HOW the name changes every day. Is there a pattern to it?

The file is saved everyday by a user. the name would be olditems101209.xls then olditems101309.xls so on and so on. I don't want to automate the procedure but more force the person responsible of updating the information to step through the process.

If you have Microsoft office loaded (probably do) then you can add a reference to it and use the Common Dialogs for browsing. I believe when you are adding a reference, press the Browse button, and link to the MSO.DLL in the folder:

C:\Program Files\Common Files\Microsoft Shared\OFFICE12

I am using Office XP. The folder will vary depending on the version you are using. THen I have the following code in the Click event of a button on my form: