I am trying to automate the process of getting data from an existing spreadsheet for use in the data model. I am able to do this as long as the file name and location are the same. I would like the VBA to allow me to select the the file rather than have the location/name hard coded.
current code:
Private Sub CommandButton1_Click()
'
' JobLookup Import
'
ActiveWorkbook.Queries.Add Name:="Sheet1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Excel.Workbook(File.Contents(""C:\GA Retail Tax Project\Job Sales Tax Table.xlsm""), null, true)," & Chr(13) & "" & Chr(10) & " Sheet1_Sheet = Source{[Item=""Sheet1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Job"", type tex" & _
"t}, {""State"", Int64.Type}, {""Sales Tax Code"", type text}, {""Customer"", type text}, {""Customer Name"", type text}, {""Job Name"", type text}, {""Job Address"", type text}, {""Job Address 2"", type text}, {""Job Address City"", type text}, {""Job Address Zip"", type text}, {""Date Open"", type any}, {""Contract Amount"", type text}, {""Date Closed"", type any}," & _
" {""Sales Rep"", type any}, {""Project Manager"", type any}, {""Engineer"", type any}})," & Chr(13) & "" & Chr(10) & " #""Removed Columns"" = Table.RemoveColumns(#""Changed Type"",{""Date Closed"", ""Sales Rep"", ""Project Manager"", ""Engineer""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Removed Columns"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Sheet1;Extended Properties=""""" _
, Destination:=Range("$A$4")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Sheet1]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Sheet1"
.Refresh BackgroundQuery:=False
End With
End Sub
Instead of this discrete path reference above: Source = Excel.Workbook(File.Contents(""C:\GA Retail Tax Project\Job Sales Tax Table.xlsm""), I want to include a file selection so I can pick the file from a varying network drive location. This data file will reside in a "month" folder on our server and therefore would change every month.
Can figure out how to include this feature in the code.
Bookmarks