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

Brad Marks BradM at blackforestltd.com
Fri Jul 27 21:55:53 CDT 2012


Charlotte,

Thanks for the clarification.

I am still learning things about Access.

It sounds like deleting and re-adding a Link to an Excel file is the easiest way to change the Excel Worksheet Name and that taking this approach should not cause any problems.  

Thanks,
Brad   


-----Original Message-----
From: accessd-bounces at databaseadvisors.com on behalf of Charlotte Foust
Sent: Fri 7/27/2012 9:34 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Change to a Different Excel WorkSheet via AccessVBACode
 
Brad,

You're confusing links with local tables.  Deleting and adding local tables
causes bloat, as does running queries, and a bunch of other actions
required along the way.

Charlotte

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

> Charlotte,
>
> Thanks for your insights into Access Links.
>
> I do not have the experience with Access that you and the others here on
> AccessD have.
>
> When I first started working with Access, I believe that I read something
> about the dangers of deleting and re-adding tables.  I can't remember the
> details but I have been a bit concerned about this approach.
>
> Maybe there is nothing to worry about when doing this.
>
> Thanks again,
> Brad
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com on behalf of Charlotte Foust
> Sent: Fri 7/27/2012 9:40 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Change to a Different Excel WorkSheet via Access
> VBACode
>
> 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
>
>
> >
> >
> >
> >
> --
> 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
>
>
>
>
-- 
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.




More information about the AccessD mailing list