Change recorded macros absolute path to a file dialog prompt

Foolzrailer

New Member
Joined
Jun 12, 2017
Messages
15
Hello

I've recorded a Macro that imports some data from an Access database Query into a specific sheet in my Excel-file.

I would like to change the path, so that it prompts me for the location of the .mdb file instead.

VBA Code:
Sub Makro1()
'
' Makro1 Makro
'
    ActiveWorkbook.Queries.Add Name:="DDH_SuperNIF", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Kilde = Access.Database(File.Contents(""path\database.mdb""), [CreateNavigationProperties=true])," & Chr(13) & "" & Chr(10) & "    _DDH_SuperNIF = Kilde{[Schema="""",Item=""DDH_SuperNIF""]}[Data]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    _DDH_SuperNIF"
    Workbooks("SuperNIF_Regnvand.xlsm").Connections.Add2 _
        "Forespørgsel - DDH_SuperNIF", _
        "Forbindelse til forespørgslen 'DDH_SuperNIF' i projektmappen.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=DDH_SuperNIF;Extended Properties=" _
        , """DDH_SuperNIF""", 6, True, False
    ActiveWorkbook.Connections("Forespørgsel - DDH_SuperNIF").Refresh
    With ActiveSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. _
        Connections("Forespørgsel - DDH_SuperNIF"), Destination:=Range("$A$1")). _
        TableObject
        .RowNumbers = False
        .PreserveFormatting = True
        .RefreshStyle = 1
        .AdjustColumnWidth = True
        .ListObject.DisplayName = "DDH_SuperNIF"
        .Refresh
    End With
End Sub


I order to do that I was thinking of adding this bit of code:
Code:
Dim fileToOpen As Variant
Dim fileFilterPattern As String

fileFilterPattern = "Access Files (*.mdb*),*mdb*"
fileToOpen = Application.GetOpenFilename(fileFilterPattern)

If fileToOpen = False Then
        ' Input cancelled
MsgBox "No file selected."
Else
Recorded Macro here with this change:
Kilde = Access.Database(File.Contents("fileToOpen")

End If

However that syntax doesn't seem to be working. Any help would be much appreciated.

The basic purpose is to link data from Access to Excel and then in Excel do some calculations.

My first attempt at this was also posted here, but that used a different way of doing it: Link Access query to Excel using VBA
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top