Updating a Linked Excel File via VBA

[JoeM]

I am using Access 2007 and have a linked Excel file as one of my table objects. Via VBA, I would like to update (change) the link so that it links to a different Excel file (based on some user selections made on a Selection Form). I have searched the internet, and have found a bunch of threads that show how to update links to other databases, but have not much to help me out with the syntax of updating/changing the link to an Excel file.



Can anyone help me with the syntax of that?

[June7]

Maybe the same way an Access table link would be changed - delete the current link and create a new one:

http://p2p.wrox.com/access/32446-mak...table-vba.html
http://www.utteraccess.com/forum/Cha...d#entry1726907

Or not
Review
http://www.ehow.com/how_6906354_do-s...a-access_.html

[JoeM]

Thanks. I was thinking that should be an easy way to change the link without dropping the table, like you can when linking to other Access databases, like seen here: http://www.access-programmers.co.uk/...d.php?t=178633 simply by changing the value of the "Connect" property, but maybe that isn't the case (obviously, using "Database" in that argument doesn't apply to Excel spreadsheets).

Maybe there is not a way to do that with Excel links (I haven't been able to find it in my Google searches either). So I followed your advice and dropped the table and rebuilt the link, i.e.

'   First drop existing table
    DoCmd.RunSQL "DROP TABLE [ltbl-PT_Records]"
    
'   Recreate linked table
    DoCmd.TransferSpreadsheet acLink, , "ltbl-PT_Records", "H:\Data\joe.xls", True, "Participant$"
    MsgBox "Link updated!"

I hope you don't mind, but is it OK to not mark the thread as "SOLVED" for now?
I am curious to see if anyone might know of a way to update the Excel links like you can Access links without having to drop the table.

[June7]

I thought what you wanted was to link to a completely different named Excel file?

Have you tried the code in the link you reference? I don't think it will set the table link to a different named db/table. It expects to find the same db/table but just setting the link to a new directory path.

[JoeM]

Basically, we have a standard Excel template that clients return to us. So all the files are in the exact same format, they just have different file names. The user is going to browse to the file via a Selection Form. I am capturing that file name, and then was trying to just change the link on the currently linked Excel table via VBA.

Have you tried the code in the link you reference? I don't think it will set the table link to a different named db/table. It expects to find the same db/table but just setting the link to a new directory path.

I tried, because it does not appear to work for EXCEL files, as it is written, only ACCESS databases. It tossed back error messages. I don't think it likes this line when dealing with Excel files:

tbl.Connect = ";Database=" & tblDB

It may just be a simple syntax change, but I have been unable to find what it should be when referencing Excel files.

[June7]

Is this a permanent link you want to maintain and have many links to Excel files?

Would be easier to just rename the new file and replace the existing Excel file. Can be done programmatically.

[JoeM]

Is this a permanent link you want to maintain and have many links to Excel files?

Basically, this is a conversion database that formats a bunch of files we get for loading into a software program we have. The Excel file has 6 tabs, so I have 6 linked tables, one for each tab. So this conversion database gets re-used over and over with different files, hence the desire to be able to programmitically change the links.

Would be easier to just rename the new file and replace the existing Excel file. Can be done programmatically.

That's not a bad idea and certainly would work.

I guess I am at the point now where I already have a working solution (the code I posted in post #3), and now I am really just curious if there actually is a simple way to do what I first requested, which is to update/change Excel links via VBA without having to drop the table first.

If there's not, I have no problem implementing the workarounds we talked about. Just trying to augment my knowledge by trying to learn some new techniques I may not be familiar with!

Thanks

[vicrauch]

Here is what a linked Excel file connection string looks like:
Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\LinkedExc elFiles\DiscountLoss.xlsx

Here is how I change the link to Excel linked tables:

'  NewExcelPath will contain the path to the new Excel file to be linked to the Access database
'  myS is the work area to help make the new connect string
'  tdf is the object variable for the tabledef

              If Left(tdf.Connect, 5) = "Excel" Then   'Identify this as an Excel linked table
                myS = tdf.Connect
                x = InStr(myS, "DATABASE=") + 8
                tdf.Connect = Left(myS, x) & NewExcelPath    'Update Connect string with new path
                tdf.refreshLink                               'Refresh the link
                If Err = 0 Then                               'Check for an error and let the user know
                    Debug.Print s & vbTab & "Table: " & tdf.Name
                Else
                    Debug.Print "*** ERROR *** " & tdf.Name
                End If
              End If