Find and Load a CSV

[WolfsInk]

Greetings Mr. Excel,

I'm working on a macro that will open a prompt window to select any csv file, then run it through a text to columns, but having issues after finding the file. Here's what I have so far. Also, I was wondering if there was a way for me to get the file name into a cell after the file was selected.

With ActiveSheet.QueryTables.Add(Filename = Application.GetOpenFilename("School Back up (*.csv), *.csv"), Destination:=Range("$A$1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = ","
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

 

[BrianExcel]

Where is the file path? I don't see anything in the code telling the system where to locate the file?

 

[WolfsInk]

I was using Filename = Application.GetOpenFilename("School Back up...) for that.

 

[tigeravatar]

WolfsInk,

When Excel opens a .csv file, it already separates it into different cells based on the comma delimiter. Because of this, you can simply copy its contents and paste it into a worksheet. The following does just that:

Sub tgr()
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    
    On Error Resume Next
    With Workbooks.Open(Application.GetOpenFilename("School Back up, *.csv"))
        .Sheets(1).UsedRange.Copy ws.Range("A1")
        .Close False
    End With
    
End Sub

 

[WolfsInk]

ooooh, that worked perfectly! Though onto the second part of the inquiry: Is there a way to get the name of the file into a cell?

 

[tigeravatar]

Sub tgr()
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    
    On Error Resume Next
    With Workbooks.Open(Application.GetOpenFilename("School Back up, *.csv"))
        ws.Range("A1").Value = .Name
        .Sheets(1).UsedRange.Copy ws.Range("A2")
        .Close False
    End With
    
End Sub

 

[WolfsInk]

Thanks again Tigeravatar