Trying to query a text file in Excel VBA on Mac OS X

Question

I must be crazy for trying to use Excel (2015? It says copyright 2015) VBA on OSX. I'm trying to have Excel query some text files, called 33.txt, 34.txt, etc. in the same folder as my workbook. It chooses the file according to numbers in Column A. Excel gives me this error message: "Excel cannot find the text file to refresh this external data range.Check to make sure the text file has not been moved or renamed, then try the refresh again."

fileloc = ActiveWorkbook.Path & "/" & Range("A" & j) & ".txt"

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fileloc, Destination:=Range("P1"))
    .AdjustColumnWidth = False
    .RefreshStyle = xlOverwriteCells
    .Refresh BackgroundQuery:=False
End With

To make matters more frustrating, when I debug.print fileloc, it looks right. It looks like the exact location of the text file I want it to query.

score 0 · Answer 1 · edited May 23 '17 at 11:46

0

Oh man I got it, with help from here: VBA: .Refresh Run-Time Error

' Get the URL
fpath = ActiveWorkbook.Path
f_dummy = fpath & "/" & Range("A" & j) & ".txt"
fname = Dir(f_dummy)


With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fpath & "/" & fname, Destination:=Range("P1"))
    .AdjustColumnWidth = False
    .RefreshStyle = xlOverwriteCells
    .Refresh 'BackgroundQuery:=False
End With

edited May 23 '17 at 11:46

Community

1
1

answered Mar 13 '17 at 06:50

Nick Georgopoulos

13
3

Was I supposed to turn the number into a string first? – Nick Georgopoulos Mar 13 '17 at 06:51
Nope, that didn't work. Has to use Dir for some reason. – Nick Georgopoulos Mar 13 '17 at 06:56

Trying to query a text file in Excel VBA on Mac OS X

1 Answers1