Run-time error '5' upon CSV import (Excel 2011, Mac)

Question

I have been crawling the site for solutions to my problem. I use Excel 2011 for Mac OS X 10.10.5.

My situation is this: I have a bunch of CSV files that i need to import into Excel for further statistical analysis. Here is an example of one of my CSV files (shared with google drive). The data is delimited by comma, and should be imported to cell A1 in all sheets (for clarification, I do not wish to have all data in A1. That would be silly now, wouldn't it. CSV data should start here, and span across column A and B, down to row number ~1200 or whatever length it will be). The sheet a given CSV file is imported to should be named after the CSV file (without ".csv") as I will be calling data later by using the sheet names. Using the import wizard is extremely tedious, and with 180 imports coming up, a VBA code / macro would help a lot as it would take me 6 very focused hours (and I like to do smart stuff in excel)

Currently I have a code which adds new sheets, but it does not work as

(1) Data is not imported - I get a runtime error '5' - Invalid procedure call or argument.

(2) Sheets are named with the file type extension .csv.

Any ideas as to why I get an error after this?:

With ActiveSheet.QueryTables.Add( _
        Connection:="TEXT;" & Fname, _
        Destination:=Range("A1"))l

Current code:

Sub CSVIMPORTTEST2()
Dim MyPath As String
Dim MyScript As String
Dim MyFiles As String
Dim MySplit As Variant
Dim N As Long
Dim Fname As String
Dim mybook As Worksheet
On Error Resume Next
MyPath = MacScript("return (path to documents folder) as String")
'Or use MyPath = "Macintosh HD:Users:YourUserName:Desktop:TestFolder:"

MyScript = "set applescript's text item delimiters to (ASCII character 10) " & vbNewLine & _
    "set theFiles to (choose file of type " & _
  " (""public.comma-separated-values-text"") " & _
    "with prompt ""Please select a file or files"" default location alias """ & _
    MyPath & """ multiple selections allowed true) as string" & vbNewLine & _
    "set applescript's text item delimiters to """" " & vbNewLine & _
    "return theFiles"

MyFiles = MacScript(MyScript)
On Error GoTo 0

If MyFiles <> "" Then
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With
MySplit = Split(MyFiles, Chr(10))
For N = LBound(MySplit) To UBound(MySplit)

    'Get file name only and test if it is open
    Fname = Right(MySplit(N), Len(MySplit(N)) - InStrRev(MySplit(N), _
    Application.PathSeparator, , 1))

        Set mybook = Nothing
            On Error Resume Next
            Set mybook = Sheets.Add(After:=Sheets(Worksheets.Count))
            mybook.Name = Fname
            On Error GoTo 0
     Next

Worksheets(Fname).Activate

With ActiveSheet.QueryTables.Add( _
    Connection:="TEXT;" & Fname, _
    Destination:=Range("A1"))
.Name = "CSV" & Worksheets.Count + 1
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlMacintosh
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
End With

      End If

End Sub

Hope someone out there is able to help

Best regards Emil Hoeck

What is the value of `Fname` when it fails? Is it the full path? — Comintern, Dec 25 '15 at 19:54
It looks like `Fname` only contains the file name and not the full path to the file, in which case Excel will not know where the file is! — SierraOscar, Dec 25 '15 at 20:28

score 0 · Answer 1 · answered Dec 25 '15 at 21:44

I'm not familiar with VBA-code or the parameters for Excel you're using, but you might want to check a few things:

First, you could do a debugprint of the filename(s) and the names of the sheets - just to make sure (especially Fname).

Now for the csv-file - the first lines look like this (don't mind the special characters here.. - these are ok in the file, UTF-8):

DATE,2015-11-30 08:30:36
SAMPLE RATE,1

BAR 1: KlÃ¸e
Range: 0 - 100
Labels: Ingen klÃ¸e - null - VÃ¦rst tÃ¦nkelige klÃ¸e

TIME,VALUE
0,0.0
1,0.0

and the parameters:

.FieldNames = True
.TextFileStartRow = 1
.TextFileCommaDelimiter = True

but your fieldnames start on row 8, and the data at row 9, while you want that data to start in cell A1. Maybe you should tune the parameters here.

Also, around line 610 there's another header:

600,63.0
601,63.0
BAR 2: Smerte
Range: 0 - 100
Labels: Ingen smerte - null - VÃ¦rst tÃ¦nkelige smerte

TIME,VALUE
0,0.0
1,0.0

You probably don't want that in your data either.

Don't know what this means, but looks strange if you only have 2 columns:

.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)

Run-time error '5' upon CSV import (Excel 2011, Mac)

1 Answers1