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

Access isn't that flexible.  Links are kind of a special situation where
you have to replace a link, you can't just edit it.

Charlotte

On Fri, Jul 27, 2012 at 7:33 AM, Brad Marks <BradM at blackforestltd.com>wrote:

> 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
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>
> --
> 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.
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
>
>
> Website: http://www.databaseadvisors.com
>
>
>
>