VBA Error 1004 - QueryTable.Add

Question

I am getting a very strange error in VBA (1004) when trying to import a csv file.

Here is my code:

With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & ThisWorkbook.Path & "/" & "IJR" & ".csv", Destination _
    :=Range("$A$1"))
    .Name = "IJR"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = xlMacintosh
    .TextFileStartRow = 2
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(3, 9, 9, 9, 9, 9, 1)
    .Refresh BackgroundQuery:=False
    .UseListObject = False
End With

It gives me the error on .Refresh BackgroundQuery:=False.

IJR.csv IS a file in the same directory as my workbook. I have a bunch of csv files in the directory. The strange thing is, when I change "IJR" to "AA" or "HES" (which are also csv files in the directory), the code works. Most of the csv filenames do not work ("USO.csv", "AAL.csv", "AAPL.csv", "HD.csv", etc.), however a few of them do (so far just "AA.csv" and "HES.csv"). Theyre all identically formatted csv files with the same number of columns. I am completely at a loss. Maybe I'm making some bonehead error, but I can't spot it.

Another bit of useful info: I am running Excel 2016 for Mac. I have a very similar piece of code that runs if the OS is Windows (due to small differences in the VBA code for Mac and Windows). It works for every csv file, no issue. That code, for reference, is:

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & ThisWorkbook.Path & "\" & symb & ".csv", Destination _
    :=Range("$A$1"))
    .Name = symb
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 2
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(3, 9, 9, 9, 9, 9, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

Update 3/10/16: I got it to work in Excel 2011 for Mac (by changing the "/" in the path to Application.PathSeparator), but it still does the same thing in Excel 2016 for Mac. May just be a bug in the new Excel.

If the same code works for some CSV files and not for others, I would guess the issue is some difference between the CSV files. What happens when you just open these files in Excel (instead of adding the QueryTable)? Do they open? Look correct? — Byron Wall
– Byron Wall, Commented Mar 4, 2016 at 15:36
They all open and look correct. Again, everything works fine with Excel 13 for Windows. It's only when I try it in Excel 16 for Mac that I have this issue. — rspears69
– rspears69, Commented Mar 5, 2016 at 2:07

ɐlǝx · Accepted Answer · 2017-06-28 10:18:24Z

3

I'm having a similar problem and after thorough testing I'm convinced this is a big in Excel 2016 for Mac and has not been fixed at 28 July 2017.

The workaround you might be able to use is:

Import each of your text files manually.
Then delete the connection (and the data).
Once you have imported a file at a location ONCE, you can do so again using VBA.

Unfortunately this is not really dynamic, and will not work in cases like my where folders actually change.

Hope it helps.

edited Jun 28, 2017 at 10:18

ɐlǝx

1,3922 gold badges17 silver badges23 bronze badges

answered Jun 28, 2017 at 10:01

Jacques de Vos

614 bronze badges

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

Comments

Jacques de Vos · Accepted Answer · 2017-06-28 10:26:09Z

3

OK, I've found a better solution:

The actual (completely hidden) problem is that the sandbox security mode does not allow access to that file, and does not prompt you as it should. Read more about this http://www.rondebruin.nl/mac/mac034.htm.

My workaround is to open the file using "Open", which prompts for the permission. We don't do anything with the open file. After permission is granted your code above will work.

Dim MyData As String, strData() As String

Open "/Users/myuser/Documents/myfile.csv" For Binary As #1 
MyData = Space$(LOF(1)) 
Get #1, , MyData 
Close #1

answered Jun 28, 2017 at 10:26

Jacques de Vos

614 bronze badges

1 Comment

Ben McIntyre Over a year ago

excellent answer. here's an updated link (above has expired): macexcel.com/examples/setupinfo/sandbox/index.html

Collectives™ on Stack Overflow

VBA Error 1004 - QueryTable.Add

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related