Sub ProcessTextFiles()
Dim folderName As String, filePathName As String, FileName As String
Dim WBName As String, DSName As String
folderName = "D:\Documents\Settings\Desktop\temp\"
FileName = Dir(folderName, vbNormal)
While FileName <> ""
filePathName = folderName & FileName
Workbooks.OpenText FileName:=filePathName _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
False, Comma:=True, Space:=False, Other:=False
WBName = ActiveWorkbook.Name 'use WBName to refer to the name of the current workbook
DSName = ActiveSheet.Name 'use DSName to refer to the name of the worksheet
'that was created when you opened the text file
'do whatever you need to do to the file here.
'don't forget to close the file
FileName = Dir() 'this gets the name of the next file
Wend
End Sub
Sub GetFile()
Const conSpath As String = "F:\Profiles\Raghav_Pal\Desktop\Tools\"
Dim sFile As String
Dim iCnt As Integer
Dim ivnt As Integer
iCnt = 0
sFile = Dir(conSpath & "*.txt", vbNormal)
Do Until sFile = ""
MsgBox sFile
iCnt = iCnt + 1
MsgBox iCnt
If Workbooks(1).Sheets.Count < iCnt Then
Set shFirstQtr = Workbooks(1).Sheets.Add
Else
Set shFirstQtr = Workbooks(1).Worksheets(iCnt)
Worksheets(iCnt).Activate
End If
With ActiveSheet.QueryTables
.Add(Connection:="TEXT;F:\Profiles\Raghav_Pal\Desktop\Tools\" _
& sFile, Destination:=Range("$A$1")).Name = sFile
.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 = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(4, 7, 4, 7)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
sFile = Dir
Loop
End SubThank you for posting your code, even though it posted without any line breaks
To make your code legible when posting to the forum, please use Code tags. If you turn on the advanced editor in your user control panel, you get code tags by clicking the "#" button. Otherwise, just type "[ CODE ]" before your code, and "[/ CODE ]" after your code (without the quotation marks, and without the spaces around the word CODE). Also, please indent your code to make it readable. I took the liberty of doing that to your code so that I could decipher it:
Code:Sub GetFile() Const conSpath As String = "F:\Profiles\Raghav_Pal\Desktop\Tools\" Dim sFile As String Dim iCnt As Integer Dim ivnt As Integer iCnt = 0 sFile = Dir(conSpath & "*.txt", vbNormal) Do Until sFile = "" MsgBox sFile iCnt = iCnt + 1 MsgBox iCnt If Workbooks(1).Sheets.Count < iCnt Then Set shFirstQtr = Workbooks(1).Sheets.Add Else Set shFirstQtr = Workbooks(1).Worksheets(iCnt) Worksheets(iCnt).Activate End If With ActiveSheet.QueryTables .Add(Connection:="TEXT;F:\Profiles\Raghav_Pal\Desktop\Tools\" _ & sFile, Destination:=Range("$A$1")).Name = sFile .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 = xlFixedWidth .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1) .TextFileFixedColumnWidths = Array(4, 7, 4, 7) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With sFile = Dir Loop End Sub
Having done that, it seems that you are using a querytable to access the data in the the text files. That's something I've never done, and if you really need to do that, I won't be able to give you any useful advice (sorry).
I think a query table is used if the data in the worksheet needs to be updated whenever the content of the source file is updated. Is that what you need to do? (Will the data in the text files be changing regularly?)
As for the error, try commenting out just the line that's giving the error. Does the code do what you need?
.Refresh BackgroundQuery:=False
