[AccessD] Change to a Different Excel WorkSheet via Access VBA Code

Worksheets in Excel are referenced two ways:  by index or by sheet name

As far as I know when you link to an Excel file it will always connect to
Sheet1.

However, I think you can use creative programming to get what you want.

Open the Excel Workbook via code, move the "Sheet2" before "Sheet1"
     Sheets("Sheet2").Select
     Sheets(Sheet2".Move Before:=Sheets("Sheet1)
Close and save
Link to the Excel Workbook

If you need to, you could reverse the sheets back to where they were.

HTH
Jim


On Thu, Jul 26, 2012 at 1:33 PM, Brad Marks <BradM at blackforestltd.com>wrote:

> All,
>
> I have an Access application that obtains data from an Excel file as a
> Linked Table.
>
> There are two worksheets in the Excel file, Sheet1 and Sheet2.
>
> Currently the Access Table Def is pointing at Sheet1.
>
> Is it possible to use VBA code to change the Table Def to point at Sheet2?
>
> I have VBA code working to change the location of the entire Excel file.
>  This is working nicely.
>
> I just can’t figure out how to change the Table Def to point at Sheet2
> instead of Sheet1 within the Excel file.
>
> Thanks,
> Brad
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>