1

I was wondering if there is a way to take the .csv file that is selected and place the name into a cell on the 'Summary' sheet. Here is the code to upload the .csv file:

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Input Raw Data") 'set to current worksheet name

strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, 
Destination:=ws.Range("A1"))
     .TextFileParseType = xlDelimited
     .TextFileCommaDelimiter = True
     .Refresh
End With

I am not sure if there is some way to do that, thanks in advance.

A Cohen
  • 458
  • 7
  • 26
  • Use `Worksheets("Summary").Range('Your Range here').Value = strFile` for file path, If you just want file name use FileSystemObject and `GetFileName(strFile)`, see here: https://stackoverflow.com/questions/1743328/how-to-extract-file-name-from-path – Victor K Sep 28 '17 at 16:27
  • Is there anyway to incorporate the hyperlinked solution into my existent code, without having to create a separate function? – A Cohen Sep 28 '17 at 16:33
  • See my answer - it incorporates FileSystemObject solution. It will require you to add a reference to `Microsoft Scripting Runtime` – Victor K Sep 28 '17 at 16:38

1 Answers1

1

Applying the solution from the link I provided in the comment to the question (you would need to add a reference to Microsoft Scripting Runtime (Tools > References in the IDE): 

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Input Raw Data") 'set to current worksheet name

strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
    .TextFileParseType = xlDelimited
    .TextFileCommaDelimiter = True
    .Refresh
End With


Dim fso as new FileSystemObject
Dim fileName As String
fileName = fso.GetFileName(strFile)
Worksheets("Summary").Range(Your Range Here).Value = fileName
Victor K
  • 1,049
  • 2
  • 10
  • 21