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

tommill52

New Member
Joined
May 12, 2015
Messages
27
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.

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
 
Last edited:

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Watch MrExcel Video

Forum statistics

Threads
1,122,481
Messages
5,596,393
Members
414,063
Latest member
N_Bates

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top