torisevt
06-21-2018, 01:08 AM
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?:think:)
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?:crying:
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
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?:think:)
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?:crying:
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