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?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Paste in a cell the file path name of any imported file selection through VBA
0
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: 1989) 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
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
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 SubI 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.