[AccessD] Change to a Different Excel WorkSheet via Access VBACode

Rocky, Jim, Mark, Darryl,

Thanks for the assistance.  I don’t think that I explained what I am trying to do very well in my earlier post.

I have a test Access application that has a “Linked Table” (an Excel file) named ExcelFile.

This Excel file has multiple WorkSheets - Sheet1, Sheet2. (Jan, Feb, Mar, Apr, etc. in the “real” system)

When I established the Linked Table, I had to tell Access which work-sheet to point at.  I chose Sheet1.

Now, I would like to change this setting from Sheet1 to Sheet2 (Jan to Feb in the real system).

Below is some code that I have that changes the Table Def for the table named “ExcelFile”.  This code works nicely for changing the name of the entire Excel File (Test1.xlsx to Test2.xlsx for example.)

What I would like to be able to do with such code is to change from Sheet1 to Sheet2 (within Test1.xlsx).

I have done quite a bit of digging and experimenting.  

Is this possible with a Linked Table?  If not, I may need to switch from a Linked Table to using a different approach for obtaining this data.

Thanks,
Brad    


~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub Update_Table_Def()

Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = CurrentDb()
Set tdf = db.TableDefs!ExcelFile

tdf.Connect = "Excel 12.0 Xml;HDR=NO;IMEX=2;ACCDB=YES;DATABASE=C:\Test1.xlsx"

tdf.RefreshLink

Set tdf = Nothing
Set db = Nothing

End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~






-----Original Message-----
From: accessd-bounces at databaseadvisors.com on behalf of Darryl Collins
Sent: Thu 7/26/2012 7:51 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Change to a Different Excel WorkSheet via Access VBACode
 
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  


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.