Access VBA - Relink external excel workbook

0
votes

How do I get MS Access to relink an external excel workbook via VBA macro?

I can do this using linked table manager 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

  1. Select new workbook
  2. Relink external excel workbook
DoCmd.transferSpreadsheet aclink,,"Sales", "C:\Sales.xlsb", true, "Sales!E2:BC200"
2
ms-accessvbams-access-2010

2 Answers

0
votes

I use the following code to reconnect to linked tables.

Public Function FixTableLink()

Dim db As Database
Dim strPath As String
Dim strConnect As String

strPath = CurrentProject.Path
strPath = strPath & "\DatabaseName.extention"

strConnect = ";DATABASE=" & strPath

Set db = CurrentDb

For Each tbl In db.TableDefs

    If Nz(DLookup("Type", "MSysObjects", "Name = '" & tbl.name & "'"), 0) = 6 And tbl.Connect <> strConnect Then

        tbl.Connect = strConnect
        tbl.RefreshLink

    End If

Next tbl

End Function

Change strPath to the path of your backend

You can use the following code to open a dialog box to search for the file path

Function SelectFile() As String

        On Error GoTo ExitSelectFile

        Dim objFileDialog    As Object
        Set objFileDialog = Application.FileDialog(1)

        With objFileDialog

            .AllowMultiSelect = False
            .Show

            Dim varSelectedItem As Variant

            For Each varSelectedItem In .SelectedItems

                SelectFile = varSelectedItem

            Next varSelectedItem

       End With

    ExitSelectFile:

    Set objFileDialog = Nothing

End Function

'File type filters can be added to the filedialog property using the following syntax:

   '.Filters.Clear
   '.Filters.Add "File Type Description", "*.file extension"

''Start folder can be specified using:
'.initialfilename="folder path"

Then in the first code block you can use

strPath =selectfile

0
votes

Something like this, perhaps.

Dim InputFile As String
Dim InputPath As String

InputPath = "C:\ExcelPath\"
InputFile = Dir(InputPath & "*.xls")

Do While InputFile <> ""

    DoCmd.TransferSpreadsheet acLink, , "Your table name","Path to your workbook file", True, "Sheet1!RangeYouNeed"
    InputFile = Dir
Loop