Dancul
03-05-2019, 03:52 AM
Hello
I'm stuck for longer time, I can not figure out how to open csv in dialog window and import data from csv. The problem is that I need to import in utf-8 format ...
So I have code which open file dialog window and allow me select csv file. Then this code return path and file name of selected file. Let say it place it in Worksheet "CSV" in range A1 and A2.
I have got code from record function which properly import csv. But I can not figure out how to connect with first code. That it will automatically open csv which path and name are in A1 and A2...
Sub reccsv()
'
' reccsv Makro
'
'
ActiveWorkbook.Queries.Add Name:="ninja-forms-submission (3)", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Zdroj = Csv.Document(File.Contents(""C:\SystemIT\csv\ninja-forms-submission.csv""),[Delimiter="","", Columns=10, Encoding=65001, QuoteStyle=QuoteStyle.Csv])," & Chr(13) & "" & Chr(10) & " #""Záhlaví se zvýšenou úrovní"" = Table.PromoteHeaders(Zdroj, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Změněný typ"" = Table.TransformColumnTypes(#""Záhlaví se zvýšenou úrovní"",{{""#"", Int64.Type}, {" & _
"""Date Submitted"", type date}, {""Vaše jméno"", type text}, {""Váš Email"", type text}, {""Telefonní kontakt"", type text}, {""Pir/pur desky"", type text}, {""Text Zprávy"", type text}, {""Tlouška izolace [mm]"", Int64.Type}, {""Množství [m2]"", Int64.Type}, {""PSČ nebo Místo dodání "", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Změněný typ"""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""ninja-forms-submission (3)"";Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [ninja-forms-submission (3)]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "ninja_forms_submission__3"
.Refresh BackgroundQuery:=False
End With
Range("D16").Select
End Sub
I'm stuck for longer time, I can not figure out how to open csv in dialog window and import data from csv. The problem is that I need to import in utf-8 format ...
So I have code which open file dialog window and allow me select csv file. Then this code return path and file name of selected file. Let say it place it in Worksheet "CSV" in range A1 and A2.
I have got code from record function which properly import csv. But I can not figure out how to connect with first code. That it will automatically open csv which path and name are in A1 and A2...
Sub reccsv()
'
' reccsv Makro
'
'
ActiveWorkbook.Queries.Add Name:="ninja-forms-submission (3)", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Zdroj = Csv.Document(File.Contents(""C:\SystemIT\csv\ninja-forms-submission.csv""),[Delimiter="","", Columns=10, Encoding=65001, QuoteStyle=QuoteStyle.Csv])," & Chr(13) & "" & Chr(10) & " #""Záhlaví se zvýšenou úrovní"" = Table.PromoteHeaders(Zdroj, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Změněný typ"" = Table.TransformColumnTypes(#""Záhlaví se zvýšenou úrovní"",{{""#"", Int64.Type}, {" & _
"""Date Submitted"", type date}, {""Vaše jméno"", type text}, {""Váš Email"", type text}, {""Telefonní kontakt"", type text}, {""Pir/pur desky"", type text}, {""Text Zprávy"", type text}, {""Tlouška izolace [mm]"", Int64.Type}, {""Množství [m2]"", Int64.Type}, {""PSČ nebo Místo dodání "", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Změněný typ"""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""ninja-forms-submission (3)"";Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [ninja-forms-submission (3)]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "ninja_forms_submission__3"
.Refresh BackgroundQuery:=False
End With
Range("D16").Select
End Sub