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:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Forum statistics

Threads
1,216,485
Messages
6,130,938
Members
449,608
Latest member
jacobmudombe

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
Back
Top