Importing CSV through PowerQuery to Excel

[torisevt]

Hi!

I have difficult task.

• I have multiple csv datafiles
• I have one previously made PowerQuery
• I want to import that csv file which I choose
• through PowerQuery to Excel (or is it to Excel and then PowerQuery?)

All this on Excel 2016.
Please bear me. I have worked with Excel hours before, but never on vba.

I add the codes when needed.

Could I have help with this task?

Here is the code. I get to the line where it says

ActiveWorkbook.Queries.Add Name:=strName,....

I get to Power Query that says that strPath is missing or doesn't behold anything.

Private Sub Workbook_Open()Dim intChoice As Integer
Dim strPath As String
Dim strName As String


'change the display name of the open file dialog
Application.FileDialog(msoFileDialogOpen).Title = _
    "Open DS Data file"
'Select the start folder
Application.FileDialog(msoFileDialogOpen _
    ).InitialFileName = "K:\tulokset"
'only allow the user to select one file
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'Remove all other filters
Call Application.FileDialog(msoFileDialogOpen).Filters.Clear
'Add a custom filter
Call Application.FileDialog(msoFileDialogOpen).Filters.Add( _
    "CSV Files Only", "*.csv")
'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogOpen).Show
'determine what choice the user made
If intChoice <> 0 Then
    'get the file path selected by the user
    strPath = Application.FileDialog( _
        msoFileDialogOpen).SelectedItems(1)
    
End If
strName = Mid(strPath, InStrRev(strPath, "\") + 1, InStrRev(strPath, ".") - InStrRev(strPath, "\") - 1)
Workbooks.Add


ActiveWorkbook.Queries.Add Name:=strName, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Lähde = Csv.Document(File.Contents(strPath),[Delimiter="";"", Columns=25, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Poistettu ylimmät rivit"" = Table.Skip(Lähde,48)," & Chr(13) & "" & Chr(10) & "    #""Poistettu alimmat rivit"" = Table.RemoveLastN(#""Poistettu ylimmät rivit"",5)," & Chr(13) & "" & Chr(10) & "    #""Muutettu tyyppi"" = Table.TransformColumnTypes(#""Poistettu " & _
        "alimmat rivit"",{{""Column1"", type date}, {""Column3"", Int64.Type}})," & Chr(13) & "" & Chr(10) & "    #""Korvattu arvo"" = Table.ReplaceValue(#""Muutettu tyyppi"","" #(tab)  3 "",""Toistotyö muutoksin"",Replacer.ReplaceValue,{""Column5""})," & Chr(13) & "" & Chr(10) & "    #""Korvattu arvo1"" = Table.ReplaceValue(#""Korvattu arvo"","" #(tab)  2 "",""Toistotyö"",Replacer.ReplaceValue,{""Column5""})," & Chr(13) & "" & Chr(10) & "    #""Korvattu ar" & _
        "vo2"" = Table.ReplaceValue(#""Korvattu arvo1"","" #(tab)  1 "",""Uusityö"",Replacer.ReplaceValue,{""Column5""})," & Chr(13) & "" & Chr(10) & "    #""Lajiteltu rivit"" = Table.Sort(#""Korvattu arvo2"",{{""Column3"", Order.Ascending}})," & Chr(13) & "" & Chr(10) & "    #""Nimetty sarakkeet uudelleen"" = Table.RenameColumns(#""Lajiteltu rivit"",{{""Column1"", ""Pvm""}, {""Column3"", ""Jobs""}, {""Column5"", ""Work""" & _
        "}, {""Column6"", ""Client""}, {""Column7"", ""Product""}, {""Column8"", ""Image""}, {""Column9"", ""aaa""}, {""Column10"", ""bbb""}, {""Column11"", ""ccc""}, {""Column13"", ""ddd""}, {""Column15"", ""eee""}, {""Column17"", ""fff""}, {""Column18"", ""ggg""}, {""Column19"", ""hhh""}, {""Column20"", ""iii""}, {" & _
        """Column21"", ""jjj""}, {""Column22"", ""kkk""}, {""Column23"", ""lll""}, {""Column24"", ""mmm""}, {""Column25"", ""nnn""}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Nimetty sarakkeet uudelleen"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=strName;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [strName]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = strName
        .Refresh BackgroundQuery:=False
    End With
End Sub

[torisevt]

Hi!


I run the code again and received these:


The table spreadsheet data must be in the same table
runtime 1004.jpg
debug.jpg






I thought to check the data connection this code should create for the query.


But it says that "Expression.Error: Import strPath doesn't match any import. Did you leave module reference off by any change?"
Error was in finnish so I take liberty to translate it.