I am currently creating a macro that needs to be able to open a .csv file from a specified path, copy all of the data, and paste it into an excel document. These documents have IDs that contain leading zeroes and so I need to be able to retain them. My current manual process includes:
- opening a new excel workbook
- setting all cells to Text data type
- opening a .csv file in notepad++
- c&p all into the workbook
- use Text to Columns for formatting
- delete the entire workbook contents
- re-c&p the data from np++ into excel and QA for the green triangle that denotes a number is stored as text
I have tried other methods that utilize importing the data, however those methods do not seem to be retaining the leading zeroes. Thus, I am seeking a possible way to just select all from a .csv in notepad++ and c&p the contents into a workbook.
Sub cdx()
Dim wb As Workbook
Set wb = ThisWorkbook
Workbooks.Add
Dim data_range As Range
'Selects all cells in the workbook
Cells.Select
'Sets all cells in workbook to a variable
Set data_range = Selection
data_range.NumberFormat = "@"
'Declare variables
'file_name: partial file name to be opened
Dim file_name As Variant
'file_path: directory file path to open the files
Dim file_path As String
'csv: object that opens up based on application type
Dim csv As Object
'creates an object to open up .csv files in NotePad++
Set csv = CreateObject("shell.application")
file_path = "C:\Users\User Name\Desktop\"
file_name = Dir(file_path & "Inquiries*")
If file_name <> "" Then
csv.Open (file_path & file_name)
End If
Dim csv_data
csv_data = file_name.readall
wb.Sheets(1).Range("A1").Value = csv_data
End Sub
