VBA to import CSV and put it into a Table

[Carkcark]

Hey to all in this amazing community,

I have the following code which I have put together so that the user can click a button in Excel and the user is taken to the File Picker to choose a CSV file to import into the spreadsheet as a table. The code works when it is static. What I mean by this is when the Source argument is given a specific location e.g = Csv.Document(File.Contents(""C:\Users\Desktop\Temp\Removals.csv""). How do I get it to work so that it uses the filepath for FileToOpen instead so that it is dynamic to the user's selection? I have tried using Source = Csv.Document(File.Contents(""FileToOpen"") but it doesn't work.

Any ideas?

Sub Macro1()

    Dim FileToOpen As String
    Dim OpenBook As Workbook
    Application.ScreenUpdating = False

'this section allows the user to pick a file name and identify it as FileToOpen

    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="CSV Files (*.csv*),*csv*")
    If FileToOpen <> "" Then

    Range("A1").Select
    
'this section imports the file identified as Source = Csv.Document....... into the workbook in the same way Date->Get Data->From File-> Text/CSV works.
    
    ActiveWorkbook.Queries.Add Name:="Removals", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""C:\Users\Desktop\Temp\Removals.csv""),[Delimiter="","", Columns=20, 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"",{{""Rem.-Date"", type date}," & _
        " {""Item"", type text}, {""SFE"", type text}, {""TUE"", Int64.Type}, {""UD"", type text}, {""TO"", Int64.Type}, {""Order"", type text}, {""Lbl"", Int64.Type}, {""Pns"", type text}, {""Ty"", type text}, {""Rip"", type text}, {""Po"", type text}, {""Ser"", type text}, {""TAY"", type text}, {""NST"", type text}, {""NSC"", type text}, {""TIW"", " & _
        "type text}, {""INP"", Int64.Type}, {""Ipt"", type text}, {""RFR"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Removals"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Removals]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Removals"
        .Refresh BackgroundQuery:=False
    End With
     
    End If
    
    Application.ScreenUpdating = True
    
End Sub

 

[LlebKcir]

here is some code for saving to a user directed path, maybe you can modify it to open from a path instead.

Function DirSelect() As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    DirSelect = sItem
    Set fldr = Nothing
End Function

 

[Carkcark]

My code already identifies the filepath and calls it FileToOpen, the issue I am having is assigning that filepath into the query where the source is.