Problem with using VBA to import comma separated text file to worksheet - Excel 2013

[tommill52]

Hi

I am using VB code created by recording a macro. I need to import as text, the content of a CSV file into a worksheet. The code I used is below.

When I first ran the code, I got an error (Run-time error 5, Invalid procedure call or argument).

I searched the internet and found a few people saying that the code generated includes the line '.commandtype = 0', but this is allegedly a Microsoft bug as there is no command 0 and I should just omit this line. I commented it out and it worked - for a while.

Now, a few hours later, it is coming up with the same error. I have excluded the 'commandtype =0' line and included it, but get the same error. I have single-stepped the code so I know this is where it is stopping.

Can anyone help with this, or even provide better code please? I am developing this in Excel 2013/365, but it will be used on mix of Excel 2007, 2010 and 2013 PCs.

By the way, I have included all of the code which also includes a prompt to the user asking if they want to import the data, and puts a time stamp on a worksheet with the date/time the source data file was created. I have included this just in case there is an issue within that code.

Sub Read_por800()
'
' Read_por800 Macro
' Read CSV file 'por800.csv' from named folder to worksheet 'data'
'


'PART 1: Prompt to ask user if they want to update worksheet 'data'
    
Dim LResult As Date




    'Text to display is shown here.
    MyNote = "Do you want to update worksheet 'data'?"
    
    'Display MessageBox - note the text in capitals. This is the label for the message box.
    Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "QUESTION")


    ' Now there is an 'If' statement to see if the user hit the 'No' button
    If Answer = vbNo Then
        
        'Code for No button Press - just quit this routine
        Exit Sub
     End If


' Answer is Yes, so continue


    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\temp\por800.csv", Destination:=Range("$A$1"))
'        .CommandType = 0
        .Name = "por800_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .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
    
' Update date and time from 'por800' filename and store it in G2 on worksheet T2 to show time and date data was generated.


LResult = FileDateTime("C:\temp\por800.csv")
Worksheets("T2").Range("G1").Value = LResult


End Sub