vba - How to select all data from notepad and copy the data into Excel using VBA

Question

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

标签： vbacsv