Paste in a cell the file path name of any imported file selection through VBA

0

I would like to know how to import any .csv or .txt file with an input box selection through vba on an existing worksheet named "Raw Data" into row 2, then the filename that was selected will be in cell A1?

Answer
Discuss

Discussion

I feel confidnt that you may have gotten some code already for this task, so go ahead, edit your question, and add the code that you are working with. I am happy to help you along, but don't feel like crafting an entire import scheme from scratch lol.
don (rep: 1969) Apr 14, '21 at 9:47 am
Yes I have. I have inputted the code I have below. The only thing I need is how do I get in A2, the filename of the CSV or TXT file that was imported in this workbook:

    Dim fileToOpen As Variant     Dim fileFilterPattern As String     Dim wsMaster As Worksheet     Dim wbTextImport As Workbook       Application.ScreenUpdating = False          fileFilterPattern = "Text Files (*.txt; *.csv), *.txt; *.csv"          fileToOpen = Application.GetOpenFilename(fileFilterPattern)          If fileToOpen = False Then              MsgBox "No file selected"     Else           Workbooks.OpenText _                     Filename:=fileToOpen, _                     StartRow:=2, _                     DataType:=xlDelimited, _                     Comma:=True           Set wbTextImport = ActiveWorkbook              Set wsMaster = ThisWorkbook.Worksheets("Raw")           wbTextImport.Worksheets(1).Range("A1").CurrentRegion.Copy wsMaster.Range("A2")           wbTextImport.Close False          End If End Sub
Jagra Apr 15, '21 at 8:16 am
    Dim fileToOpen As Variant     Dim fileFilterPattern As String     Dim wsMaster As Worksheet     Dim wbTextImport As Workbook       Application.ScreenUpdating = False          fileFilterPattern = "Text Files (*.txt; *.csv; *.prn), *.txt; *.csv; *.prn"          fileToOpen = Application.GetOpenFilename(fileFilterPattern)          If fileToOpen = False Then              MsgBox "No file selected"     Else           Workbooks.OpenText _                     Filename:=fileToOpen, _                     StartRow:=2, _                     DataType:=xlDelimited, _                     Comma:=True           Set wbTextImport = ActiveWorkbook              Set wsMaster = ThisWorkbook.Worksheets("Raw")           wbTextImport.Worksheets(1).Range("A1").CurrentRegion.Copy wsMaster.Range("A2")           wbTextImport.Close False          End If End Sub  
Jagra Apr 15, '21 at 8:17 am
Add to Discussion

Answers

0

Hello Jagra and welcome to the Forum

Your sub is not named above (and please use the "Add Link" button above in future when supplying code) so I have used the modified code below to add a file name to A1 of worksheet Raw and paste the file contents from A2 down (as per your question). 

There are just a few changes to your code (in bold- with alternatives for A1 contents):

Sub ImportCSVorTXT()
Dim fileToOpen As Variant
Dim fileFilterPattern As String
Dim wsMaster As Worksheet
Dim wbTextImport As Workbook

Application.ScreenUpdating = False
fileFilterPattern = "Text Files (*.txt; *.csv), *.txt; *.csv"
fileToOpen = Application.GetOpenFilename(fileFilterPattern)

If fileToOpen = False Then
        MsgBox "No file selected"
    Else
    Workbooks.OpenText _
        Filename:=fileToOpen, _
        StartRow:=2, _
        DataType:=xlDelimited, _
        Comma:=True
    Set wbTextImport = ActiveWorkbook
    Set wsMaster = ThisWorkbook.Worksheets("Raw")
    wsMaster.UsedRange.Clear ' clear previous contents
    wbTextImport.Worksheets(1).UsedRange.Copy wsMaster.Range("A2")
    wbTextImport.Close False

    wsMaster.Range("A1").Value = Right(fileToOpen, Len(fileToOpen) - InStrRev(fileToOpen, "\")) ' add filename
    'wsMaster.Range("A1").Value = fileToOpen 'OR use the full path and filename
    wsMaster.Range("A1").Font.Bold = True   
End If
End Sub
The application requestor GetOpenFilename actually delivers your string variable fileToOpen as the full path to the chosen file. I've used string manipulation to extract just the actual file name (but the adjacent commented-out line gives the full path, if that's what you want).

I notice that you use StartRow:=2 in the import (which will exclude the first row/line of a file) but I'd suggest StartRow:=1 so you get the whole file (stated in A1) unless all your files have common headers.

Hope this works for you.

Discuss

Discussion

Did that work, Jagra?
John_Ru (rep: 1072) Apr 20, '21 at 4:13 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login