VBA Importing text files from user specified folder

[steve6sgd]

Hello,

I am trying to import text files from a user specified folder onto different sheets of the current workbook. I am getting an error of
"Run-time error '1004': Application-defined or object-defined error"
Thanks in advance,


Sub LoopAllExcelFilesInFolder()


Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Dim i As Integer


i = 1 'seed the counter


'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


'Retrieve Target Folder Path From User
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)


With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With


'In Case of Cancel
NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings


'Target File Extension (must include wildcard "*")
myExtension = "*.txt"


'Target Path with Ending Extention
myFile = Dir(myPath & myExtension)


'Loop through each TXT file in folder
Do While myFile <> ""
'Set variable equal to opened workbook
i = i + 1
Set ws = ThisWorkbook.Sheets("Sheet" & i) 'the current sheet

With ws.QueryTables.Add(Connection:="TEXT;" & myFile, Destination:=ws.Range("A1"))
.Name = "Sheet" & i
.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 'we are using a tab-delimited file
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileOtherDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
fname = Dir
End With


'Get next file name
myFile = Dir
Loop


'Message Box when tasks are completed
MsgBox "Task Complete!"


ResetSettings:
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub

[snb]

Have a look at application.filedialog.

[GTO]

Hi there,

'myFile' is just the name of the file. You need the FullName (Path & FileName combined) to pass to QueryTables.Add

Hope that helps,

Mark