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.