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

I always set the object, that way you can control what is going on....

Dim objExcelSheet As Excel.Worksheet
Dim objExcelWB As Excel.Workbook

Set objExcelApp = CreateObject("Excel.Application")
        objExcelApp.Visible = True ' or False if you don't want the user to see it happening.
        
        With objExcelApp
            .Workbooks.Open fileName:=strTargetPathAndName ' Full path and name as a variable here
            Set objExcelWB = objExcelApp.ActiveWorkbook ' Workbook opened is always the active workbook, although you could also use set here
	Set objExcelSheet = objExcelWB.Sheet2 ' Set the sheet

	Or

	Set objExcelSheet = objExcelWB.Worksheets("SheetNameHere")

	'Get Data
	strMyData = objExcelSheet.Range("B1").value

	' or Write Data
	objExcelSheet.Range("B1").value = strMyData

Blah blah blah
	

HTH
Darryl.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Friday, 27 July 2012 4:33 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Change to a Different Excel WorkSheet via Access VBA Code

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