1

The following VBA code is part of a macro that pulls data in from a CSV file. It works fine on a number of PCs that run Excel 2007 and 2010, but fails with the following error on PCs running Excel 2013.

'Run-time error 1004: Application-defined or Object-defined error'

The error occurs at the following line

    .Refresh BackgroundQuery:=False

If I comment out this line, I don't get an error, but I don't get the data imported either.

Any help would be gratefully accepted.

With ActiveSheet.QueryTables.Add(Connection:=connectionName, Destination:=Range("$A$1"))
    .Name = "por800.csv"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 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
Community
  • 1
  • 1
tommill
  • 15
  • 4
  • 1
    What if you take the BackgroundQuery:=False off and just use .Refresh? – MatthewD Jul 21 '15 at 16:00
  • Possibly too many connections? Your not deleting the connection after adding each time so they can stack up in Excel between saves. You may need to loop and delete them all. –  Jul 21 '15 at 16:48
  • Hi. Thanks for the suggestion. I don't know VBA well enough to know whether that will work or not. I will test it in the morning (the users in question have gone home now and they all have laptops!). – tommill Jul 21 '15 at 17:07
  • Hi pathDongle. I'm not a VBA specialist. How do I clear down connections in VBA? – tommill Jul 21 '15 at 17:09
  • Have a look at this post http://stackoverflow.com/questions/19903428/killing-connection-in-excel-vba. Ideally you should reuse the connection using refresh or delete the connection when complete. –  Jul 21 '15 at 17:15

0 Answers0