Can you link the newest Excel sheet to a table?

[Loktar]

Hello everyone,

I have a table in my db that is a linked Excel spreadsheet. This file is created using an "Export to Excel" function in QuickBooks, where data is exported to the spreadsheet in a new sheet (e.g. if Sheet1 exists, new data is put in Sheet2).

I was wondering if there is a way for my linked table to always look at the newest sheet in the Excel file. Right now I have to manually change the name of the sheet I want to Sheet1 after every export. (The access table is linked with TABLE=Sheet1$). Is there any parameter such as TABLE=NewestSheet$?

I was thinking of posting this in an Excel forum, but since it deals with Access I figured here would work

Thanks in advance for any help.

 

[Blorf]

How many sheets will you end up with in a single file?

If you write a for loop starting backwards in VBA to link the spreadsheet, you can say link sheet(X), and on error loop back one until you find one.

This is the only thing I can think of, so long as the sheets will always be sheet1, 2, 3 and so on.



Ascii dumb question, get a dumb Ansi

 

[SkipVought]

Hi,

If you are looking for the LAST sheet

Sheets(Sheets.Count)

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Loktar]

Thanks for the replies. I can't figure out how to get Access to change the link after a table is made so I'm not sure how to change how it looks at the Excel sheet. Maybe I'll use that Sheets(Sheet.Count) in a VBA script to delete the other sheets when a new one is exported.

Thanks again for the pointers!

 

[SkipVought]

Well, post the code that you ALREADY have.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Loktar]

I was hoping that Access had a feature that would let me do this without using any code, but I'm pretty sure that's not the case anymore. I'm headed home in a few minutes but as I continue to work on this problem tomorrow I'll keep the thread up to date and post my solution when (if) I find one. Thanks again for the pointers!

 

[Loktar]

Today I came up with a solution for the problem, although it has nothing to do with Access, but since I started this thread here I figure I might as well end it here..

Like I said above, I was exporting data from QuickBooks to Excel, so in Excel I wrote some code in the worksheets SheetChange function that updates the names of sheets, making the new one named Sheet1 and the old ones Backup1 - Backup5, with any more than 5 getting deleted.

Thanks again for throwing me some pointers. If anyone wants this code I'll post it.

 

[Blorf]

Cool.

Ascii dumb question, get a dumb Ansi