none
User selects import file csv error RRS feed

  • Question

  • Hello,

    I am trying to import a file with a name like "name20171108" "name20171109" and so on. I decided that I would just create an open prompt and select the file each day instead of asking the macro to find it itself.

    I am getting a runtime error 1004 Application defined or object defined error when I run this code:

    Sub hqadd()
    '
    ' hqadd Macro
    ' hqadd
    '
    ' Keyboard Shortcut: Ctrl+j
    '
        Dim fNameAndPath As Variant
     
        fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.CSV), *.CSV", Title:="Select File To Be Opened")
        If fNameAndPath = False Then Exit Sub

         ActiveWorkbook.Worksheets.Add
        With ActiveSheet.QueryTables.Add(Connection:= _
        "CSV; " & fNameAndPath, Destination:=Range("$A$4"))
            .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 = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileOtherDelimiter = "~"
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
            1, 1, 2, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
            , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 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
    End Sub

    The problem lines are in bold. Could anyone provide me some insight on why this is occurring? I am puzzling VBA out for the first time. 

    Wednesday, November 8, 2017 4:42 PM

All replies

  • Try it like this:

    Sub TestMacro()
        Dim fNameAndPath As Variant
        Dim wkbkA As Workbook
        Set wkbkA = ActiveWorkbook
        fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.CSV), *.CSV", Title:="Select File")
        If fNameAndPath = False Then Exit Sub
        Workbooks.Open Filename:=fNameAndPath
        ActiveSheet.Move before:=wkbkA.Worksheets(1)
    End Sub

    Wednesday, November 8, 2017 6:21 PM
  • Sub hqadd()
    '
    ' hqadd Macro
    ' hqadd
    '
    ' Keyboard Shortcut: Ctrl+j
    '
        Dim fNameAndPath As Variant
        Dim wkbkA As Workbook
        Set wkbkA = ActiveWorkbook
        fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.CSV), *.CSV", Title:="Select File")
        If fNameAndPath = False Then Exit Sub
        Workbooks.Open Filename:=fNameAndPath
        ActiveSheet.Move before:=wkbkA.Worksheets(1)

         ActiveWorkbook.Worksheets.Add
        With ActiveSheet.QueryTables.Add(Connection:= _
        "CSV; " & fNameAndPath, Destination:=Range("$A$4"))
            .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 = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileOtherDelimiter = "~"
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
            1, 1, 2, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
            , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 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
    End Sub

    Like this?


    • Edited by rachirl Wednesday, November 8, 2017 10:41 PM
    Wednesday, November 8, 2017 10:40 PM
  • No - just my macro, and none of yours.
    Wednesday, November 8, 2017 11:02 PM