Sub LoadCSV_FileToSheet()
'
Application.ScreenUpdating = False ' Turn ScreenUpdating off
'
Dim startTime As Single
Dim CSV_FileToOpen As Variant
'
CSV_FileToOpen = Application.GetOpenFilename("Text files,*.csv", , "Select file", , False) ' Save full path of CSV file to CSV_FileToOpen
If CSV_FileToOpen = False Then ' Exit Sub if user cancelled
MsgBox "No file selected - exiting"
Exit Sub
End If
'
startTime = Timer ' Start the stopwatch
'
Application.ScreenUpdating = False ' Turn ScreenUpdating off
'
Dim CSV_ColumnMinus1 As Long, CSV_FileRow As Long
Dim FreeFileNumber As Long
Dim RowNumber As Long
Dim All_CSV_RowsFromCSV_FileArray As Variant, CSV_FileRowColumnsArray As Variant
Dim Partitioned_CSV_FileArray As Variant
Dim wsDestination As Worksheet
'
Set wsDestination = Sheets("Sheet2") ' <--- Set this to the sheet name to dump Partitioned_CSV_FileArray
'
FreeFileNumber = FreeFile ' Get an unused file number
Open CSV_FileToOpen For Input As #FreeFileNumber
'
If Err.Number <> 0 Then ' If error occurred then ...
MsgBox "File open error #" & Err.Number & "!", vbCritical, "Error!" ' Display error #
Exit Sub ' Exit sub
End If
'
All_CSV_RowsFromCSV_FileArray = Split(Input(LOF(FreeFileNumber), #FreeFileNumber), vbCrLf) ' Load all Rows in file to All_CSV_RowsFromCSV_FileArray
Close #FreeFileNumber
'
RowNumber = 0 ' Initialize RowNumber
'
ReDim Partitioned_CSV_FileArray(1 To UBound(All_CSV_RowsFromCSV_FileArray), 1 To 100) ' Set rows/columns for Partitioned_CSV_FileArray
'
For CSV_FileRow = LBound(All_CSV_RowsFromCSV_FileArray) To UBound(All_CSV_RowsFromCSV_FileArray) ' Loop through all rows of CSV file
If All_CSV_RowsFromCSV_FileArray(CSV_FileRow) <> vbNullString Then ' If CSV row is not blank then ...
CSV_FileRowColumnsArray = Split(All_CSV_RowsFromCSV_FileArray(CSV_FileRow), ";") ' Load contents of row to CSV_FileRowColumnsArray
'
RowNumber = RowNumber + 1 ' Increment RowNumber
'
For CSV_ColumnMinus1 = LBound(CSV_FileRowColumnsArray) To UBound(CSV_FileRowColumnsArray) ' Loop through columns
Partitioned_CSV_FileArray(RowNumber, CSV_ColumnMinus1 + 1) = _
CSV_FileRowColumnsArray(CSV_ColumnMinus1) ' Add values to Partitioned_CSV_FileArray
Next ' Loop back
End If
Next ' Loop back
'
wsDestination.UsedRange.Clear ' Clear the destination sheet
wsDestination.Range("B2").Resize(UBound(Partitioned_CSV_FileArray, 1), UBound(Partitioned_CSV_FileArray, 2)).Value = Partitioned_CSV_FileArray ' Display Partitioned_CSV_FileArray to sheet
wsDestination.UsedRange.EntireColumn.AutoFit ' Autofit used columns
'
Application.ScreenUpdating = True ' Turn ScreenUpdating back on
'
Debug.Print RowNumber & " Rows of data processed from the CSV file." ' Display the # of data lines that were processed to the 'Immediate Window'(CTRL-G)
Debug.Print "Time to complete = " & Timer - startTime & " seconds." ' about .1 seconds
End Sub