Runtime error 5 on older version while importing csv file

Amez

New Member
Joined
Jun 25, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Foutmelding reservatieoverzicht 250621.png
    Foutmelding reservatieoverzicht 250621.png
    189.8 KB · Views: 26

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Welcome to the Board!

Please translate the error message for us into English.
Or you can post your question in your native language here: Questions in Other Languages
 
Upvote 0
If you click on whatever button translates to "Debug", which line of code does it highlight?
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top