Excel crashing when importing CSV data into workbook

Question

I have a few workbooks that I have developed for sizing of equipment. I export the details of the design via VBA to a CSV file to make it easier to load back in. Attached below is the code. The script works most of the time but if I try try load a CSV file shortly after saving one, excel sometimes crashes and then restarts etc. I have 4 different sizing workbooks for different equipment, each one based on the same VBA code and they all have the same issue.

One item of note, when I save the CSV file, I add text to the end of the file names to identify the type of equipment that is sized as well as keep it from being identified as an excel file. This is done by an export script. for example "Filename.csv" gets saved as "Filename.csv.WHRU"

'''''''''''''''''''''''''''''''''''''''''''

Worksheets(12).Activate

Dim fStr As String With Application.FileDialog(msoFileDialogFilePicker) .Show

    If .SelectedItems.Count = 0 Then
        MsgBox "Cancel Selected"
        Exit Sub

    End If

    'fStr is the file path and name of the file
    fStr = .SelectedItems(1)
End With
With ThisWorkbook.Sheets(12).QueryTables.Add(Connection:= _
"TEXT;" & fStr, Destination:=ThisWorkbook.Sheets(12).Cells(3, 7))
  .Name = "CAPTURE"
    .FieldNames = True
    .RefreshStyle = xlOverwriteCells
    .TextFileCommaDelimiter = True
    .Refresh BackgroundQuery:=False
 End With
For Each Cn In ThisWorkbook.Connections
    Cn.Delete
Next Cn

For Each Cn In Sheets(12).QueryTables
    Cn.Delete
Next Cn

'''''''''''''''''''''''''''''''''''''''''''

Lets start by adding Option Explicit at the top of each Form/Module/Class. You can default this setting by selecting Tools->Options->Require Variable Declaration. Now, keep doing Debug->Compile VBAProject until all errors are gone. I'm fairly certain that you're reusing variables, like Cn, without declaring/setting them properly. Where is the code where you save the file? — Profex
Also, Worksheets(12) is not always the same as Sheets(12). Sheets(3) could be a chart and mess everything up afterwards. — Profex
I went through and corrected the Worksheets versus Sheets. It was in numerous places. I also declared all the variables. At the same time I removed the " _ " that was in the querytable.connection definition All combined these three items cleared up the issue. — GSP

W. García W. García · Accepted Answer · 2020-11-06T02:07:31

For copy the information from a CSV file and put it in an Excel Worksheet, directly from VBA, you can take advantage of this project. The project also allows you to write CSV files in practicaly no time.

Try this:

Sub ImportCSVRecords(filePathAndName As String, OutputSheet As String, OutputRange As String)
    Dim CSVix As CSVinterface
    
    Set CSVix = New CSVinterface 'Create new instance
    Call CSVix.OpenConnection(filePathAndName) 'Open a physical connection to the CSV file
    Call CSVix.ImportFromCSV 'Import data
    Call CSVix.DumpToSheet(WBookName:=ThisWorkbook.Name, SheetName:=OutputSheet, rngName:=OutputRange) 'Dumps the data to the current Workbook's OutputSheet starting at named OutputRange.
    Set CSVix = Nothing 'Terminate the current instance
End Sub

Excel crashing when importing CSV data into workbook

1 Answers