So I made this macro to import a csv file from the downloads folder. I tested this on 2 different computers using the same version of excel. Both worked but when i sent this to the person I made it for they had error 5.
I have the 2016 version of excel. And the other person has the 2013 version of excel. I will upload the screenshot of the error message. As you see on the screenshot all the data is correct excep for the first 2 columns should be dates. Maybe this is why the error is there? Below you will see the macro.
Sub Boekingslijstinvoegen1()
'
' Boekingslijstinvoegen1 Macro
'
'
Dim naam As String
naam = Range("C6").Value
ActiveWorkbook.Queries.Add Name:="boekingslijst", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Bron = Csv.Document(File.Contents(""C:\Users\" & naam & "\Downloads\boekingslijst.csv""),[Delimiter="","", Columns=24, Encoding=65001, QuoteStyle=QuoteStyle.Csv])," & Chr(13) & "" & Chr(10) & " #""Headers met verhoogd niveau"" = Table.PromoteHeaders(Bron, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Type gewijzigd"" = Table.TransformColumnTypes(#""Headers met verhoogd niveau"",{{""Aanvangstijd sess" & _
"ie"", type datetime}, {""Boekingsdatum"", type datetime}, {""Naam klant"", type text}, {""Klant E-mailadres"", type text}, {""Klant telefoonnummer"", type text}, {""Klant adres"", type text}, {""Groepsgrootte"", Int64.Type}, {""Naam dienst"", type text}, {""Type dienst"", type text}, {""Medewerker"", type text}, {""Boekingsstatus"", type text}, {""Status opkomst"", " & _
"type text}, {""Betaalstatus"", type text}, {""Betaalmethode"", type text}, {""Betaalgegevens"", type text}, {""Betaald bedrag"", type text}, {""Verschuldigd bedrag"", type text}, {""Couponnaam"", type text}, {""Formulierveld 0"", type text}, {""Form antwoord 0"", type text}, {""Formulierveld 1"", type text}, {""Form antwoord 1"", type text}, {""Formulierveld 2"", ty" & _
"pe text}, {""Form antwoord 2"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Type gewijzigd"""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=boekingslijst;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [boekingslijst]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Application.CommandBars("Queries and Connections").Visible = False
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("K:K").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWorkbook.Queries("boekingslijst").Delete
End Sub
I have the 2016 version of excel. And the other person has the 2013 version of excel. I will upload the screenshot of the error message. As you see on the screenshot all the data is correct excep for the first 2 columns should be dates. Maybe this is why the error is there? Below you will see the macro.
Sub Boekingslijstinvoegen1()
'
' Boekingslijstinvoegen1 Macro
'
'
Dim naam As String
naam = Range("C6").Value
ActiveWorkbook.Queries.Add Name:="boekingslijst", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Bron = Csv.Document(File.Contents(""C:\Users\" & naam & "\Downloads\boekingslijst.csv""),[Delimiter="","", Columns=24, Encoding=65001, QuoteStyle=QuoteStyle.Csv])," & Chr(13) & "" & Chr(10) & " #""Headers met verhoogd niveau"" = Table.PromoteHeaders(Bron, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Type gewijzigd"" = Table.TransformColumnTypes(#""Headers met verhoogd niveau"",{{""Aanvangstijd sess" & _
"ie"", type datetime}, {""Boekingsdatum"", type datetime}, {""Naam klant"", type text}, {""Klant E-mailadres"", type text}, {""Klant telefoonnummer"", type text}, {""Klant adres"", type text}, {""Groepsgrootte"", Int64.Type}, {""Naam dienst"", type text}, {""Type dienst"", type text}, {""Medewerker"", type text}, {""Boekingsstatus"", type text}, {""Status opkomst"", " & _
"type text}, {""Betaalstatus"", type text}, {""Betaalmethode"", type text}, {""Betaalgegevens"", type text}, {""Betaald bedrag"", type text}, {""Verschuldigd bedrag"", type text}, {""Couponnaam"", type text}, {""Formulierveld 0"", type text}, {""Form antwoord 0"", type text}, {""Formulierveld 1"", type text}, {""Form antwoord 1"", type text}, {""Formulierveld 2"", ty" & _
"pe text}, {""Form antwoord 2"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Type gewijzigd"""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=boekingslijst;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [boekingslijst]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Application.CommandBars("Queries and Connections").Visible = False
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
Columns("J:J").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("K:K").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWorkbook.Queries("boekingslijst").Delete
End Sub