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 SubSub CreateAClickableMacroButton()
'
Dim ButtonHeighth As Long, ButtonLength As Long
Dim ButtonTitle As String
Dim CellToPutButtonInto As String
Dim CodeNameOfSheetToPutClickableButton As String
Dim LeftAddress As String, TopAddress As String
Dim MacroToRunWhenButtonIsClicked As String
Dim NameOfSheetToPutClickableButton As String, NameOfMacroToRunWhenButtonIsClicked As String
'
CellToPutButtonInto = "B2" ' <--- Set this to the cell that you want the clickable button to be put into
ButtonTitle = "Import CSV File" ' <--- Set this to what you want displayed on the button
NameOfSheetToPutClickableButton = "Sheet1" ' <--- Set this sheet name to the desired sheet to put the clickable button into
NameOfMacroToRunWhenButtonIsClicked = "LoadCSV_FileToSheet" ' <--- Set this to the name of the subroutine that you want to run when button is clicked
'
CodeNameOfSheetToPutClickableButton = Sheets(NameOfSheetToPutClickableButton).CodeName ' Find the CodeName of the SheetName
MacroToRunWhenButtonIsClicked = CodeNameOfSheetToPutClickableButton & "." & NameOfMacroToRunWhenButtonIsClicked
'
ButtonHeighth = 15 ' <--- Set this to the Default Height of button
ButtonLength = Round(Len(ButtonTitle) * 5 * 0.98) ' Calculated value to set the ButtonLength to
'
With Sheets(NameOfSheetToPutClickableButton)
With .Buttons.Add(1, 1, ButtonLength, ButtonHeighth)
.Top = .Parent.Range(CellToPutButtonInto).Top
.Left = .Parent.Range(CellToPutButtonInto).Left
.Caption = ButtonTitle ' Title of clickable button
.OnAction = MacroToRunWhenButtonIsClicked ' Subroutine to run when button is clicked
End With
End With
End Sub
Sub CreateAClickableMacroButton()
'
Dim ButtonHeighth As Long, ButtonLength As Long
Dim ButtonTitle As String
Dim CellToPutButtonInto As String
Dim CodeNameOfSheetToPutClickableButton As String
Dim LeftAddress As String, TopAddress As String
Dim MacroToRunWhenButtonIsClicked As String
Dim NameOfSheetToPutClickableButton As String, NameOfMacroToRunWhenButtonIsClicked As String
'
CellToPutButtonInto = "B2" ' <--- Set this to the cell that you want the clickable button to be put into
ButtonTitle = "Import CSV File" ' <--- Set this to what you want displayed on the button
NameOfSheetToPutClickableButton = "Sheet1" ' <--- Set this sheet name to the desired sheet to put the clickable button into
NameOfMacroToRunWhenButtonIsClicked = "LoadCSV_FileToSheet" ' <--- Set this to the name of the subroutine that you want to run when button is clicked
'
CodeNameOfSheetToPutClickableButton = Sheets(NameOfSheetToPutClickableButton).CodeName ' Find the CodeName of the SheetName
MacroToRunWhenButtonIsClicked = CodeNameOfSheetToPutClickableButton & "." & NameOfMacroToRunWhenButtonIsClicked
'
ButtonHeighth = 15 ' <--- Set this to the Default Height of button
ButtonLength = Round(Len(ButtonTitle) * 5 * 0.98) ' Calculated value to set the ButtonLength to
'
With Sheets(NameOfSheetToPutClickableButton)
With .Buttons.Add(1, 1, ButtonLength, ButtonHeighth)
.Top = .Parent.Range(CellToPutButtonInto).Top
.Left = .Parent.Range(CellToPutButtonInto).Left
.Caption = ButtonTitle ' Title of clickable button
.OnAction = MacroToRunWhenButtonIsClicked ' Subroutine to run when button is clicked
End With
End With
End SubSub 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
