[AccessD] Change to a Different Excel WorkSheet via Access VBACode

Named ranges are the easiest way to attach to an excel file.  It can be on
a separate worksheet or the same one.  I'm dealing with a data download
that will come in every month or two in an excel worksheet that has all the
earmarks of summer intern creation.  There are spacer columns between the
data columns and there may be 15000 rows in the thing, so I created a macro
I can run on it that pops formulas into a contiguous range of cells to
reference the data from the "formatted" columns, then names the range the
continguous range so it can be attached for comparison to existing records.

Charlotte
On Thu, Jul 26, 2012 at 6:56 PM, Darryl Collins <
darryl at whittleconsulting.com.au> wrote:

> Hi Brad
>
> Try this
>
> tdf.Connect = "[Excel 12.0
> Xml;HDR=NO;IMEX=2;ACCDB=YES;DATABASE=C:\Test1.xlsx].[Feb]"
>
> to choose a sheet other than 1.  I would try it with named ranges too,
> they should work as well.
>
> Cheers
> Darryl.
>
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:
> accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
> Sent: Friday, 27 July 2012 11:04 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Change to a Different Excel WorkSheet via Access
> VBACode
>
> 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
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>
>
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com on behalf of Darryl Collins
> Sent: Thu 7/26/2012 7:51 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Change to a Different Excel WorkSheet via Access
> VBACode
>
> I always set the object, that way you can control what is going on....
>
> Dim objExcelSheet As Excel.Worksheet
> Dim objExcelWB As Excel.Workbook
>
> Set objExcelApp = CreateObject("Excel.Application")
>         objExcelApp.Visible = True ' or False if you don't want the user
> to see it happening.
>
>         With objExcelApp
>             .Workbooks.Open fileName:=strTargetPathAndName ' Full path and
> name as a variable here
>             Set objExcelWB = objExcelApp.ActiveWorkbook ' Workbook opened
> is always the active workbook, although you could also use set here
>         Set objExcelSheet = objExcelWB.Sheet2 ' Set the sheet
>
>         Or
>
>         Set objExcelSheet = objExcelWB.Worksheets("SheetNameHere")
>
>         'Get Data
>         strMyData = objExcelSheet.Range("B1").value
>
>         ' or Write Data
>         objExcelSheet.Range("B1").value = strMyData
>
> Blah blah blah
>
>
> HTH
> Darryl.
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:
> accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
> Sent: Friday, 27 July 2012 4:33 AM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Change to a Different Excel WorkSheet via Access VBA
> Code
>
> All,
>
> I have an Access application that obtains data from an Excel file as a
> Linked Table.
>
> There are two worksheets in the Excel file, Sheet1 and Sheet2.
>
> Currently the Access Table Def is pointing at Sheet1.
>
> Is it possible to use VBA code to change the Table Def to point at Sheet2?
>
> I have VBA code working to change the location of the entire Excel file.
>  This is working nicely.
>
> I just can't figure out how to change the Table Def to point at Sheet2
> instead of Sheet1 within the Excel file.
>
> Thanks,
> Brad
>
>
> --
> 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
>
>
>