OOTHUB Technologies

Why does OpenText Runtime error 1004 even though file can be imported by hand

Asked 3 days ago Modified today Viewed 106 times Asked By ganesh kumar padal

With Excel 365 for MAC/OS I get error 1004 when trying to open a *.csv file using VBA OpenText.

I receive .csv files (semicolon delimiter) from my bank and want to automate importing the data into worksheets. To that end, I recorded a macro while importing one of the files. The recording produced the following little function:

    Public Function openSource(fileToOpen As String) As Excel.Workbook
    'Open the text file with semicolon delimiters

    Debug.Print fileToOpen
    Workbooks.OpenText FileName:= _
            fileToOpen, Origin:= _
            65001, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
            , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
            Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
            Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
            16, 1), Array(17, 1), Array(18, 1), Array(19, 1)), TrailingMinusNumbers:=True

End Function
`

This crashes immediately with the feared 1004 code. I must admit that I can't make heads or tails of what is happening here. I hope someone can point me in the right direction.