Reattach Excel Tables (Named Ranges) (Access 97)

Topic

New Reply

I’m trying to reattach a couple of linked excel 5 named ranges in access 97 via vba but not having much luck.

I’ve just about decided that I’ll have to drop the tables and recreate them.

Does anyone have a few hints re refreshing the links via vba or do I have to drop and recreate the tables?

thanks

Reply | Quote

Replies

If you post the code you’re trying to use, someone can straighten it out for you. It would also help if you tell us what isn’t working. “Not having any luck” is a little vague. I don’t see what the difference is between “reattaching” a couple of Excel ranges and dropping and recreating the link.

Reply | Quote

Tired frustration can make the message a little unclear I agree.

I was trying to refresh the attach string for the excel tables without dropping and recreating the attached tables.
In the end I modified a bit of code from the access help examples to drop and recreate the tables. My major problem was getting the syntax right for refering to a named range.

It’s all working now though.

Thanks.

Stewart

Dim strCurrAppDir As String

‘drop the existing tables first.
CurrentDb().TableDefs.Delete “subtable”
CurrentDb().TableDefs.Delete “Ydata”

‘ Call the ConnectOutput procedure. The first argument is the acess table alias, the second argument
‘ will be used as the Connect string, and the third argument will be used as the SourceTableName (named range in this case).

‘– Get the application’s path
strCurrAppDir = Left$(CurrentDb().Name, InStr(CurrentDb().Name, “HealthCentreDatabase.mdb”))
‘ reconnect the two tables using the connectoutput procedure
ConnectOutput “subTable”, “Excel 5.0;” & “DATABASE=” & strCurrAppDir & “ASPEC Report Template.xls;”, “subTable”
ConnectOutput “YData”, “Excel 5.0;” & “DATABASE=” & strCurrAppDir & “ASPEC Report Template.xls;”, “YData”

End Sub

Sub ConnectOutput(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 = CurrentDb().CreateTableDef(strTable)

tdfLinked.Connect = strConnect
tdfLinked.SourceTableName = strSourceTable
CurrentDb().TableDefs.Append tdfLinked

Set rstLinked = CurrentDb().OpenRecordset(strTable)

End Sub

Reply | Quote