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.
I order to do that I was thinking of adding this bit of code:
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
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