The Code modules I provided does all the dirty work for you.
Let me give a step by step on how to use them.
1) Start with a new excel workbook.
2) Press Alt+F11 to open the VB Editor
3) Press Alt+I and then select 'Module' from the window that pops up
4) In the big window that pops up paste the following code:
VBA Code:
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 Sub
5) Make any adjustments that you want to make to that code
6) Press Alt+F4 to return to Excel sheet
7) Right click the sheet tab at the bottom of the excel sheet that you elected to place the Button into.
8) select the 'View Code' from the window that pops up.
9) In the big window that pops up, paste the following code:
VBA Code:
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
10) Press Alt+F4 to return to Excel sheet
11) Press Alt+F8 and double click the 'CreateAClickableMacroButton' ' This will create the button on the sheet that you chose
12) Press F12 to 'Save As' & save the file as a 'xlsm' file
13) Close out the file and reopen it.
14) From then on you can click the button that was created and it will execute the code to ask for the CSV file location, etc.
