Shakeable_Drip
Board Regular
- Joined
- May 30, 2023
- Messages
- 52
- Office Version
- 365
- Platform
- Windows
My macro directs the users to select .txt files for import.
At the moment, a file explorer window pops up and users have to paste the folder directory and select the files, somewhat cumbersome.
A copy of the workbook is always in the directory with a folder named Temp where the .TXT files are located.
Can I do 1 one 2 things:
1. file explorer opens to this workbook's location.
2. (my preference) instead of using a pop up can excel grab all the .TXT files at *this workbook's location/temp
Thanks for the help!
At the moment, a file explorer window pops up and users have to paste the folder directory and select the files, somewhat cumbersome.
A copy of the workbook is always in the directory with a folder named Temp where the .TXT files are located.
Can I do 1 one 2 things:
1. file explorer opens to this workbook's location.
2. (my preference) instead of using a pop up can excel grab all the .TXT files at *this workbook's location/temp
Thanks for the help!
VBA Code:
Sub TXT_Import()
Dim DATA As Variant
Dim sourceWorkbook As Workbook
Dim sourceRange As Range
Dim destinationWorksheet As Worksheet
Dim nextColumn As Long, i As Long
DATA = Application.GetOpenFilename("TXT-DATA (*.txt), *.txt", MultiSelect:=True)
If Not IsArray(DATA) Then Exit Sub
Application.ScreenUpdating = False
Set destinationWorksheet = ThisWorkbook.Sheets("IMPORT")
nextColumn = 1
For i = LBound(DATA) To UBound(DATA)
Set sourceWorkbook = Workbooks.Open(DATA(i), local:=True)
With sourceWorkbook.ActiveSheet
Set sourceRange = .UsedRange.Resize(.UsedRange.Rows.Count - 1).Offset(1, 0)
End With
sourceRange.Copy destinationWorksheet.Cells(1, nextColumn)
nextColumn = nextColumn + sourceRange.Columns.Count
sourceWorkbook.Close False
Next i
Application.ScreenUpdating = True
'MsgBox "Completed . . .", vbInformation 'optional
End Sub