LINKED TABLE MANAGER HELP!HELP!HELP! 1

[DIVINEDAR0956]

Okay guys,

I need to know that is there some VBA code that will let me update a linked excel table to access.

What I need is a VBA code that will allow me to open a file in a directory, then access the linked table manager to link the existing tables to the worksheets within that excel file.

All of my excel files have the same format so all would be linked to a table that already exist in access.

For instance, there is a worksheet in excel for the Projec Table, Task Table, Hours Table, Vendor Table and Rates Table. Each excel worksheet is designed to link to access.

Can anyone help me or is there a better way to link to an existing table when you open an excel file.

Thank you.

Darlene Sippio

 

[jimmythegeek]

You should be able to link from Excel to Access, and refresh the link each time you open the spreadsheet. You need to go to Data | Get External Data | Create Query. Once you create the query (it actually gets created in MSQuery), each time you open the spreadsheet, you can simply go to Data | Refresh Data and it will update your spreadsheet.

Not sure if this is what you are looking for. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[DIVINEDAR0956]

Thank you Mr. Lunde for your input I will try that. It may be what I've been looking for months now. Let you know how it works.

Thank you again.
Darlene Sippio
dsippio@comtechsystems.com

 

[essnrv]

Dim dbsTemp As Database
Dim strMenu As String
Dim strInput As String
' Open a Microsoft Jet database to which you will link a table.
Set dbsTemp = OpenDatabase("DB1.mdb&quot
' Call the ConnectOutput procedure. The third argument
' will be used as the Connect string, and the fourth
' argument will be used as the SourceTableName.
ConnectOutput dbsTemp, "ExcelTable", "DATABASE=C:\Excel\Samples\Q1Sales.xls","January Sales"

Sub ConnectOutput(dbsTemp As Database,strTable As String, strConnect As String,strSourceTable As String)
Dim tdfLinked As TableDef
Dim rstLinked As Recordset
Dim intTemp As Integer
' Create a new TableDef, set its Connect and
' SourceTableName properties based on the passed
' arguments, and append it to the TableDefs collection.
Set tdfLinked = dbsTemp.CreateTableDef(strTable)
tdfLinked.Connect = strConnect
tdfLinked.SourceTableName = strSourceTable
dbsTemp.TableDefs.Append tdfLinked
Set rstLinked = dbsTemp.OpenRecordset(strTable)

End Sub
Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[rmoss]

essnrv,

I am trying something similar to DIVINEDARO956, where I am trying to import the data from an Excel sheet into a temporary table for comparison to another table. I found the example you posted in the Access help files, but have one question - what exactly is "January Sales"? Is it a named range or the sheet name? What do you use as the SourceTableName for an Excel workbook?

rmoss

 

[DIVINEDAR0956]

If I'm not mistaken "January Sales" is a worksheet inside the excel xls file. Darlene Sippio
dsippio@comtechsystems.com

 

[rmoss]

I discovered on another web site that if the sheet name is "January Sales" then the SourceTableName would be "January Sales$" with the $ necessary. It works in the application I am working on, so the help file in Access is less than exact in this respect.