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

Hi Brad

No, it will not delete a link. If a linked table with that name already exists, an error will be raised.

To adjust the link information of an existing linked table, use the RefreshLink method:

    tdfLinked.Connect = strNewConnect
    tdfLinked.RefreshLink

/gustav


>>> BradM at blackforestltd.com 27-07-12 16:33 >>>
Gustav,

Thanks for your assistance.

With the sample code that you shared, I now have a method to change the Excel "Sheet" from Sheet1 to Sheet2 to Sheet3, etc.

This method currently deletes the Linked Table with the old sheet name and then re-adds the Linked Table with the new sheet name.

This works, but I have some concern about using a Delete and Re-add over and over again.

Is there a way to simply change the Sheet name without needing to Delete and Re-add the Linked Table?

Thanks,
Brad 



-----Original Message-----
From: accessd-bounces at databaseadvisors.com on behalf of Gustav Brock
Sent: Fri 7/27/2012 4:38 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Change to a Different Excel WorkSheet viaAccess	VBACode
 
Hi Brad

What confuses you is that an Excel file is not a table but a database. Further, as you are not referencing a Named Range (as noted by Charlotte and my favourite feature of Excel to recomend) but a WorkSheet, the table name is the WorkSheet name postfixed a $-sign. 

Thus the example goes:

<code>
Public Sub AttachExcel()

    Dim dbs         As DAO.Database
    Dim tdfLinked   As DAO.TableDef
    Dim strConnect  As String
    Dim strTblName  As String
     
    Set dbs = CurrentDb
    strConnect = "Excel 8.0;DATABASE=" & "d:\folder\file.xls"
    strTblName = "SomeSheetName$"
         
    Set tdfLinked = dbs.CreateTableDef("xlsSomeSheetName")
    tdfLinked.Connect = strConnect
    tdfLinked.SourceTableName = strTblName
    dbs.TableDefs.Append tdfLinked

End Sub
</code>

/gustav


>>> BradM at blackforestltd.com 27-07-12 3:04 >>>
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~