[AccessD] Change to a Different Excel WorkSheet via Access 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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~