Share via

Excel - Run-time error 5 Invalid procedure call or argument

Anonymous
2018-07-17T16:08:42+00:00

I generated this Macro using the Developer "Record Macro" function.  It worked importing the data during the Macro Creation process.  However, when I try to re-run the Macro to test for re-use when wanted, I get the "Run-time error 5 Invalid procedure call or argument" on the .CommandType=0 line.

Sub Macro2()

'

' Macro2 Macro

'

'

    With ActiveSheet.QueryTables.Add(Connection:= _

        "TEXT;C:\Scripts\Litigation Hold and eDiscovery\LitigationHoldInfo.csv", _

        Destination:=Range("$A$5"))

        .CommandType = 0

        .name = "LitigationHoldInfo_1"

        .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 = 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)

        .TextFileTrailingMinusNumbers = True

        .Refresh BackgroundQuery:=False

    End With

    ActiveWorkbook.Save

End Sub

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

3 answers

Sort by: Most helpful
  1. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2018-07-18T04:53:30+00:00

    Let the import page as is.

    Go into your other application and export a new CSV file

    In you import page select cell A5 (the cell where your query starts)

    In the Data tab click Refresh All

    Excel will import the new file with the same settings as the old file and update the data automatically. No need for a macro.

    Andreas.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments No comments
  2. Anonymous
    2018-07-17T18:36:38+00:00

    I'm importing Exchange O365 information to manipulate.  This will happen periodically.  The Macro is to import the csv file into a Summary WorkBook where I'll manipulate the data based on other information.

    Eventually, I'll process a "Wipe" of the import page and then "re-import" the new data.

    Was this answer helpful?

    0 comments No comments
  3. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2018-07-17T18:21:51+00:00

    Counter question: Why do you want to run it again?
    You can not have 2 queries in the same area, because they would overwrite each other.

    Andreas.

    Was this answer helpful?

    0 comments No comments