CODEWITHSUNDEEP
ms-accessvbams-access-2010
Elixir
Elixir

Reputation: 303

External link new location VBA

The below code works great for refreshing an external link in vba however is there a way of changing the location of the link?

I can do this using linked table manager when ticking the 'Always prompt for new location', but I would like to do this via VBA, so that I could create a button for users to press to locate the new workbook

Select new workbook, Relink external excel workbook.

Function Relink()

    Set db = CurrentDb
    Set tdf = db.TableDefs("Sales")
    tdf.Connect = "Excel 5.0;HDR=YES;IMEX=2;" & _
    "DATABASE=C:\Sales.xlsb"
    tdf.RefreshLink

End Function

Upvotes: 0

Views: 1721

Answers (2)

Don George
Don George

Reputation: 1328

Here's a function I use to allow the user to browse to a file and select it. You can call this function to get a file name in the prior function instead of getting it from the table.

Public Function Get_File(Optional ftype = "xls")

Dim fd As Object
Const msoFileDialogFolderPicker = 4
Const msoFileDialogFilePicker = 3
Const msoFileDialogViewDetails = 2

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = False
fd.ButtonName = "Select"
fd.InitialView = msoFileDialogViewDetails
fd.Title = "Select File"
fd.InitialFileName = "MyDocuments\"
fd.Filters.Clear
fd.Filters.Add "Files", "*." & ftype & "*"

'Show the dialog box and get the file name
If fd.Show = -1 Then
    Get_File = fd.SelectedItems(1)
    Else
    Get_File = ""
    End If

End Function

Upvotes: 1

Don George
Don George

Reputation: 1328

I use this function to re-link my tables from a table, based on whether I am working on my c:\ drive or the network. I think you could modify this to have the user enter a file location, or use a file dialog to browse to a location.

Function relink_tables()

If Left(CurrentDb().Name, 2) = "C:" Then
    source = "local"
    Else: source = "network"
    End If
Set RS = CurrentDb.OpenRecordset("select * from [linked table source] where source='" & source & "'")
source = RS.Fields("path")

For Each R In References
    If InStr(R.Name, "Common Tables") > 0 Then Application.References.Remove R
    Next R
Application.References.AddFromFile source

x = 0
Set TDefs = CurrentDb().TableDefs
For Each table In TDefs
    If InStr(table.Connect, "Common Tables") = 0 Then GoTo NT
    table.Connect = ";DATABASE=" & source
    table.RefreshLink
    x = x + 1
NT:
    Next table
Finish:
MsgBox "remapped " & x & " tables"
End Function`enter code here`

Upvotes: 1

Related Questions