Error in automatically import of CSV through VBA

Question

I recently made a xlsm. file in which I automatically open a CSV file, that is located in in the same map as my workbook. On my pc it works fine, but on the pc with the fella that I'm working with, it just doesn't open. He receives the "1004 “Application-defined or Object-defined" error.

Here is the code from the macro that imports the CSV file. Maybe it goes wrong at the path that I'm referring to? My friend has another pc, so.. I don't know. Help is appreciated.

Sub Auto_Open()
'
' Imports CSV

'
    Range("A9:BU156").Select
    Selection.Delete Shift:=xlToLeft
    Range("A8").Select
    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Name\Dropbox\YGH\BYMKEW8 - Deel II\Uitwerking - kopie\Eerstejaars studenten ingeschreven hbo - kopie.csv" _
        , Destination:=Range("$A$9"))
        .Name = "Eerstejaars studenten ingeschreven hbo - kopie"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierNone
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Blad1").Select
    Range("A9:R9").Select
    With Selection.Font
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Font.Bold = True
    Columns("A:A").ColumnWidth = 11.86
    Columns("F:F").ColumnWidth = 25.86
    Columns("G:G").ColumnWidth = 18
    Columns("H:H").ColumnWidth = 21.14
    Columns("I:I").ColumnWidth = 15.86
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    Columns("J:J").ColumnWidth = 17.14
    Columns("J:J").ColumnWidth = 21.57
    Columns("J:J").ColumnWidth = 34.43
    Columns("J:J").ColumnWidth = 26
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
        Range("A9:R125").Select
    ActiveWorkbook.Names.Add Name:="List", RefersToR1C1:="=Blad1!R9C1:R125C18"
    Range("A1").Select
End Sub

Since you are using range this may be relevant: stackoverflow.com/q/17980854/1531971 — user1531971
– user1531971, Commented May 28, 2018 at 14:39

Vityata · Accepted Answer · 2018-05-28 13:25:11Z

1

Your friend probably does not have this path available:

C:\Users\Name\Dropbox\YGH\BYMKEW8 - Deel II\Uitwerking - kopie\Eerstejaars studenten ingeschreven hbo - kopie.csv

Thus getting 1004 this row comes:

.Refresh BackgroundQuery:=False

answered May 28, 2018 at 13:25

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ferdie Over a year ago

And do you have any idea how to change the code, so that he is able to import the CSV? Thanks

Vityata Over a year ago

@Ferdie - ask him/her to write change the path. Or read the path from somewhere. Or use Windows IDE to get the path.

Collectives™ on Stack Overflow

Error in automatically import of CSV through VBA

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related