none
Macro Run-time Error 5 RRS feed

  • Question

  • I'm unfortunately not as clued up on excel as I'd like to be, so forgive me if this has already come up or has an obvious fix. I have recorded a macro to import a csv file named "My Mobile Portfolio" (which is saved in the same location as the excel workbook) onto a new sheet, and to then copy a particular range of this data to another sheet in the workbook.

    When I run the macro, however, the following error is produced: "Run-time error '5': Invalid procedure call or argument".

    Altering the coding is unfortunately still beyond me, but as far as I have seen, this error has only occurred when a pivot table has been entered, which is not the objective of this macro. The following is the code shown in the 'debug' window:

    Sub ImportCSV()
    '
    ' ImportCSV Macro
    '

    '
        Sheets.Add After:=ActiveSheet
        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;C:\Users\Travis Taylor\Downloads\My Mobile Portfolio.csv", Destination _
            :=Range("$A$1"))
            .CommandType = 0
            .Name = "My Mobile Portfolio"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 65001
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
        ActiveCell.Columns("A:B").EntireColumn.Select
        Selection.Delete Shift:=xlToLeft
        ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
        Selection.Delete Shift:=xlToLeft
        ActiveCell.Offset(0, -1).Range("A1:A67").Select
        Selection.Copy
        Sheets("Sheet5").Select
        ActiveCell.Offset(0, 1).Range("A1").Select
        ActiveSheet.Paste
    End Sub

    Any help or advice would be appreciated. Thanks in advance.

    Wednesday, July 2, 2014 4:55 PM

All replies