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

Brad:

Here's a snippet I use to cycle through a workbook with many worksheets:

' Cycle through worksheets - 1 sheet per assembly
    For intI = 1 To xlBook.Worksheets.Count
        Set xlSheet = xlBook.Sheets(intI)
        txtImportData = "Processing BOM Worksheet " & intI & " of " _
            & xlBook.Worksheets.Count & " - " & xlSheet.Name

where 

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlsheet As Excel.Worksheet

So you could probably just Set xlSheet = xlBook.Sheets(1) or maybe (2) -
I don't know if it's zero based or one.

HTH

Rocky


-------- Original Message --------
Subject: [AccessD] Change to a Different Excel WorkSheet via Access VBA
Code
From: "Brad Marks" <BradM at blackforestltd.com>
Date: Thu, July 26, 2012 11:33 am
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>

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