Import unknow Excel sheet to Access

[LB79]

Hello,

Please can someone tell me how to import an Excel worksheet in VBA when the name is unknown? I can use DoCmd to import using the sheet name, but because the sheet name is ever changing I need something more dynamic.

Thanks

 

[chergh]

A bit more info would help

Why does the name change?
How is the new name determined?
How many sheets are in the workbook?

 

[LB79]

Thanks for the reply.
The sheet will always be the second sheet of the workbook.
Not sure why the logic of the changing sheet names makes a difference, but its from another departments automatic reporting system which names the sheet as a date and time stamp, which is frustrating when you're trying to automate things.

 

[chergh]

Thanks for the reply.
The sheet will always be the second sheet of the workbook.
Not sure why the logic of the changing sheet names makes a difference, but its from another departments automatic reporting system which names the sheet as a date and time stamp, which is frustrating when you're trying to automate things.

Well if you know the why and how the sheet name changes you can sometimes be able to include that logic in your vba to be able to identify the correct sheet.

Anyway you have a couple of options.

1. Use the ordinal position of the sheet. This isn't something I am a fan off but if the output is being produced by another system it should be consistent.

2. If only one sheet in the workbook has a date/time stamp as a sheet name you could parse the name of each sheet in the book until the isdate function returns true.

3. You could also add a combo control to your form which is populated by the names of each sheet in the workbook. You could then select the sheet you want to import manually from the list.

4. If there is a consistent layout you could look for particular values in each of the worksheets and when they match import that sheet.

There are other ways no doubt but these are the first ones that come to mind.

 

[ghudson]

Can you list an example of the different sheet names in your most current file? Is the date used in the second sheet name always the date of the current day?

 

[LB79]

Thanks Chergh.

GHudson - an example is 20100807 - this date is in reverse format and although it ran on the 7th August, we might not recieve it until a few days later.