CODEWITHSUNDEEP
excelvbacsv
GSP
GSP

Reputation: 1

Excel crashing when importing CSV data into workbook

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

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

Upvotes: 0

Views: 1180

Answers (1)

W. García
W. García

Reputation: 1

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

Upvotes: 0

Related Questions