VBA to import CSV and put it into a Table

Carkcark

New Member
Joined
Dec 13, 2016
Messages
9
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?

VBA Code:
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
here is some code for saving to a user directed path, maybe you can modify it to open from a path instead.

Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top