How to get macro to prompt a .csv file for import

[Lordonez]

Hello,

I am working on making an excel that will get data from .csv files and delete un-needed columns. I've recorded a macro to do this for a specific .csv file, but I would like to make it so that excel will prompt me to select a file to perform the macro on. How can I edit the existing macro that will preserve everything I want done, but add in the feature to select what file? I have attached a screenshot of the macro I currently have. Thank you for your help!

 

[John_w]

Hard to help with a screenshot.

Add this to allow the user to browse and select a csv file:

    Dim csvFile As Variant
    
    csvFile = Application.GetOpenFilename(FileFilter:="csv files (*.csv), *.csv", MultiSelect:=False, Title:="Select CSV file to import")
    If csvFile = False Then Exit Sub 'Cancel clicked

and change this part of ActiveWorkbook.Queries.Add File.Contents(""C:\users\xxxx\xxxxx\file.csv"") to File.Contents(""" & csvFile & """)

Other changes will be needed if you want to run the macro again to import another csv file. For example, you'll get an error on the
ActiveWorkbooks.Queries.Add line because it will try to create a workbook query whose name already exists. The solution is to delete the workbook query, either manually or with code, or to use the existing workbook query, or give the workbook query a unique name.

 

[Lordonez]

Awesome, thank you! This definitely got be down the correct track. Here is what I ended up with. I had one line that kept giving me errors, but just deleting that line fixed the issues funnily enough.

Sub csvImportTest()
'
' csvImportTest Macro
'

'
 Dim csvFile As Variant
    
    csvFile = Application.GetOpenFilename(FileFilter:="csv files (*.csv), *.csv", MultiSelect:=False, Title:="Select CSV file to import")
    If csvFile = False Then Exit Sub 'Cancel clicked
    
    On Error Resume Next
ActiveWorkbook.Queries("Table 4").Delete
On Error GoTo 0
    
    ActiveWorkbook.Queries.Add Name:="Table 4", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""" & csvFile & """),[Delimiter="","", Columns=60, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Sample ID"", type text}, {""Samp" & _
        "le Rack ID"", type text}, {""Sample Rack Position"", Int64.Type}, {""Operator ID at time of Processing"", type text}, {""Assay Name"", type text}, {""Assay Number"", Int64.Type}, {""Assay Version"", Int64.Type}, {""Final Result Value"", type number}, {""Units"", type text}, {""Instrument Response Value (RLU)"", Int64.Type}, {""Dilution Name"", type text}, {""Result " & _
        "Flags"", type text}, {""Result Code"", type text}, {""Date of Completion"", type date}, {""Time of Completion"", type time}, {""Run Number"", Int64.Type}, {""Result Comment"", type text}, {""Module Serial Number"", type text}, {""Processing Lane ID"", type text}, {""Processing Track ID"", Int64.Type}, {""Site Name"", type text}, {""System Name"", type text}, {""Syst" & _
        "em Software Version"", type date}, {""Order Type"", Int64.Type}, {""Reagent Lot Number"", type text}, {""Reagent Lot Expiration Date"", type date}, {""Reagent Serial Number"", Int64.Type}, {""Trigger Lot Number"", type text}, {""Trigger Expiration Date"", type date}, {""Pre-Trigger Lot Number"", type text}, {""Pre-Trigger Expiration Date"", type date}, {""Buffer Lot" & _
        " Number"", type text}, {""Buffer Expiration Date"", type date}, {""RV Lot Number"", Int64.Type}, {""Calibration Date"", type date}, {""Calibration Time"", type time}, {""Calibrator Lot Number"", type text}, {""Calibrator Serial Number"", Int64.Type}, {""Control Type"", type text}, {""Control Set Name"", type text}, {""Control Name"", type text}, {""Control Lot Numbe" & _
        "r"", type text}, {""Control Lot Expiration"", type datetime}, {""Control Serial Number"", Int64.Type}, {""Assay Control Lot Number"", type text}, {""Assay Control Serial Number"", Int64.Type}, {""Control Min Range"", type number}, {""Control Max Range"", type number}, {""Control Range Units"", type text}, {""Secondary Reagent Lot Number"", type text}, {""Secondary R" & _
        "eagent Lot Expiration Date"", type datetime}, {""Secondary Reagent Serial Number"", Int64.Type}, {""Calibration Cutoff Value"", type number}, {""Positive Cal Mean RLU"", Int64.Type}, {""Constituent Assay 1 Name"", type text}, {""Constituent Assay 1 Result Value"", type text}, {""Constituent Assay 1 Instrument Response"", Int64.Type}, {""Constituent Assay 2 Name"", t" & _
        "ype text}, {""Constituent Assay 2 Result Value"", type text}, {""Constituent Assay 2 Instrument Response"", Int64.Type}})," & Chr(13) & "" & Chr(10) & "    #""Removed Columns"" = Table.RemoveColumns(#""Changed Type"",{""Sample Rack ID"", ""Sample Rack Position"", ""Assay Number"", ""Assay Version"", ""Instrument Response Value (RLU)"", ""Dilution Name"", ""Result Flags"", ""Result Code"", ""Ru" & _
        "n Number"", ""Result Comment"", ""Processing Lane ID"", ""Processing Track ID"", ""Site Name"", ""Module Serial Number"", ""System Software Version"", ""Order Type"", ""Reagent Lot Number"", ""Reagent Lot Expiration Date"", ""Reagent Serial Number"", ""Trigger Lot Number"", ""Trigger Expiration Date"", ""Pre-Trigger Lot Number"", ""Pre-Trigger Expiration Date"", ""B" & _
        "uffer Lot Number"", ""Buffer Expiration Date"", ""RV Lot Number"", ""Calibration Date"", ""Calibration Time"", ""Calibrator Lot Number"", ""Calibrator Serial Number"", ""Control Serial Number"", ""Assay Control Lot Number"", ""Assay Control Serial Number"", ""Secondary Reagent Lot Number"", ""Secondary Reagent Lot Expiration Date"", ""Secondary Reagent Serial Number" & _
        """, ""Calibration Cutoff Value"", ""Positive Cal Mean RLU"", ""Constituent Assay 1 Name"", ""Constituent Assay 1 Result Value"", ""Constituent Assay 1 Instrument Response"", ""Constituent Assay 2 Name"", ""Constituent Assay 2 Result Value"", ""Constituent Assay 2 Instrument Response""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Removed Columns"""
'    ActiveSheet.ListObjects("Table 4").Name = _
'       "Table 4"
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 4"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 4]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    
    
    End With
       
    
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    
    
End Sub