Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reattach Excel Tables (Named Ranges) (Access 97)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reattach Excel Tables (Named Ranges) (Access 97)

    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.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reattach Excel Tables (Named Ranges) (Access 97)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •