PDA

View Full Version : Macro to LIST ALL FILES IN FOLDER and then IMPORT ALL LISTED FILES



StlSmiln
06-23-2012, 04:33 PM
Hi All,

I can be lengthy in my effort to try to be clear so I've tried to emphasized the most important parts for faster reading by those that just want to get to it. I would really appreciate any help you can give me as I'm VERY FRUSTRATED at this point. [Disclaimer: Last several days have been my first trying to work with VBA (beyond recording/using recorded macros).]

Ultimately, I'm trying to create a 2-sheet workbook to accomplish the following (using Excel 2007 and 2010):
Sheet 1: create a list of all .txt files within a directory and it's subdirectories.
Sheet 2: Import the entirety of the first file listed on Sheet 1 into Sheet 2, and then import and append each of the remaining files on Sheet 1 to Sheet 2. All files after the first should start on row 2 of each file so not to repeat the header row, however I would like to change the formatting of the first line that starts a new file (or some other means of flagging the start of a new file). All text files have the same field settings and structure.I've been trying to splice various parts of different macros together but the main two that I've been using are:
21. VBA Tip on ExcelExperts dot com (Sorry, I can't give links in the forum yet)
The "Import_All_Text_Files_2007" subroutine given on Ozgrid dot com forum (thread = 155350) (the last code by Rushti on 6/24/2011)I love how the "list files" works except I can't seem figure out how to modify for just a specific file type--I believe I'm nesting conditionals incorrectly or something. This is where I'm at with that one:

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
Set MyObject = New Scripting.FileSystemObject
Set mySource = MyObject.GetFolder(mySourcePath)
On Error Resume Next
If InStr(MyFile.Path, ".txt") <> 0 Then
For Each MyFile In mySource.Files

iCol = 2
Cells(iRow, iCol).Value = MyFile.Path
iCol = iCol + 1
Cells(iRow, iCol).Value = MyFile.Name
iCol = iCol + 1
Cells(iRow, iCol).Value = MyFile.Size
iCol = iCol + 1
Cells(iRow, iCol).Value = MyFile.DateLastModified
iRow = iRow + 1
Next
End If
Columns("C:E").AutoFit
If IncludeSubfolders Then
For Each mySubFolder In mySource.SubFolders
Call ListMyFiles(mySubFolder.Path, True)
Next
End If
End Sub
And then with the second one I can't show you any of what I've modified...I'm frustrated as hell at this point, but for all of your amusement I'll summarize my day (it may remind you of when you were new at this)..."suddenly" the second macro stopped working this morning (neither the original or any of the versions I'd been modifying)...kept running it, but although it seemed to "run" it didn't do anything. :eek: I tried a bunch of things that my novice brain could come up with and searched the web for answers:reading:, but several hours went by and I still couldn't figure it out.:banghead:

My totally VBA-ignorant brain came up with some genius hypotheses :sleuth:, "Maybe I've used the same variable in multiple modules, or created too many variations of the modules, and have confused the system....:clevermanOr maybe I've screwed something up with the way that VBA modules relate/interact with each other while trying to splice them together." :dunnoWith that in mind, I decided to take the zen approach :wine:in lieu of pulling my hair out .

I did what anyone else in my position would do having exhausted all of my options...I took a deep breath and started over. That is, I ERASED ALL OF THE MODULES, INCLUDING MY OWN EFFORTS OVER THE PAST THREE DAYS!!! :boohoo

I pasted in fresh copies of the subroutines I'd found on the web, and held my breath as I ran the module that had suddenly stopped working after my modifications. It didn't work. :SHOCKED:New panic. Took me only a moment to realize that in my haste I hadn't entered the directory path as needed. :cool: Relief--dodged a bullet. Ran it again. Nothing. :104: What the hell? It didn't come up with any errors, but it also didn't seem to do anything. Spent another hour trying to figure out if I'd copied the wrong macro off the web...:stars:...finally defeated ,:writer: I spent about 30 minutes writing a different very long question in this forum about what the hell I could have done to stop these macros from working...I was about to post it when :115:I realized...I'd input the wrong directory path. :omg: And the worst part? I NOW also realize that was the problem this morning--BEFORE I'D ERASED ALL OF MY WORK!!! :rotflmao: HOW **** STUPID!!!

I'd used the path that I'd been testing the 1st module with, which will loop through folders and subfolders, but the second one will only import files from the main folder and all of my .txt files are inside subdirectories--there are no actual files in the main directory so it was returning nothing. HOLY S@*T! Did I really let my frustration make me that careless/stupid?!?

All of this considered, :ipray:I know that what I'm trying to accomplish would be an easy task for you experts so any help would be much appreciated!!! Even if it's just an encouraging pointer.

Kenneth Hobs
06-23-2012, 08:02 PM
Welcome to the forum!

Sounds like you learned some things from the school of hard knocks. As such, backup is the key to trial and error as you now know.

For step 1, we have several examples in this forum on how to do that. The lastest can be found at: http://www.vbaexpress.com/forum/showthread.php?t=42482

This is a modification of what I did in that thread just for you. Notice that I set it by File Type and not File Extension. If you want file extension, we can do that easily enough.

For step 2, you will need to be a bit more specific. Is the sheet named Sheet1 in each Excel spreadsheet or just the sheet indexed as 1? Are the workbooks password protected? Text files do not have fields per se. They can have some structure if saved as a CSV sort of file. Just how did you want the first line inserted from each file to be flagged, bold font? Of course if you have lots of data, you could well have too many rows inserted. With newer versions of Excel, that is less likely.

When troubleshooting, be sure to comment out On Error lines so that you will be notified of an error. You can use Debug to Step through code line by line with F8 if needed.

I must say, you are probably the king when it comes to use of Smilies. :)

Add or remove parts as needed. Make sure that you add the two library references as noted in my comments.

' Windows folder and file details for windows versions and a VBA macro:
' http://www.kixtart.org/forums/ubbthreads.php?ubb=showflat&Number=160880&page=1

' http://www.vbaexpress.com/forum/showthread.php?t=42482
Sub FileDetails()
' http://vbaexpress.com/kb/getarticle.php?kb_id=1035
'SpeedOn

ListMyFiles ThisWorkbook.Path, Range("A2"), True, "Text Document"

ActiveSheet.UsedRange.Columns.AutoFit
Range("C:C").HorizontalAlignment = xlCenter
Range("F:H").HorizontalAlignment = xlCenter
Range("A1").Activate
'SpeedOff
End Sub

' Tools > References > Microsoft Scripting Runtime
' Tools > References > Microsoft Shell Controls and Automation
Sub ListMyFiles(mySourcePath As String, sRow As Range, _
Optional IncludeSubfolders As Boolean = True, Optional FileType As String = "")

Dim myObject As Scripting.FileSystemObject
Dim mySource As Scripting.Folder
Dim myFile As Scripting.File
Dim mySubFolder As Scripting.Folder
Dim wShell As Shell

Set wShell = New Shell
Set myObject = New Scripting.FileSystemObject
Set mySource = myObject.GetFolder(mySourcePath)

'On Error Resume Next
For Each myFile In mySource.Files
'If LCase(myFile.Path) = LCase(ThisWorkbook.Path) Then GoTo NextFile
If myFile.Type = FileType Or FileType = "" Then
With sRow
.Value2 = myFile.Path
.Offset(, 1).Value2 = myFile.Name
.Offset(, 2).Value2 = myFile.Size
.Offset(, 3).Value2 = myFile.Type
.Offset(, 4).Value2 = myFile.DateLastModified
.Offset(, 4).NumberFormat = "mm/dd/yyyy"
End With

With wShell.Namespace(mySource.Path)
'sRow.Offset(, 5).Value2 = .GetDetailsOf(.ParseName(myFile.Name), 151) 'Frame width Vista=265, Width=151 Win7.
'sRow.Offset(, 6).Value2 = .GetDetailsOf(.ParseName(myFile.Name), 153) 'Frame height Vista=263, Height=153 Win7.
'sRow.Offset(, 7).Value2 = .GetDetailsOf(.ParseName(myFile.Name), 36) 'Duration value, XP=21. 36 in Vista and Win7.
'srow.offset(, 8).Value2 = .GetDetailsOf(.ParseName(myFile.Name), 36) 'Duration word, XP=21. 36 in Vista and Win7.
End With

' Hyperlink
'sRow.Offset(, 8).Hyperlinks.Add sRow.Offset(, 8), myFile.Path, , , myObject.GetBaseName(myFile.Name)

Set sRow = sRow.Offset(1)
End If
NextFile:
Next

If IncludeSubfolders Then
For Each mySubFolder In mySource.SubFolders
ListMyFiles mySubFolder.Path, sRow, True, FileType
Next
End If
End Sub

StlSmiln
06-23-2012, 09:04 PM
Kenneth,

Thank you, THANK YOU for your help! I'm going to start looking at the code you modified for me so that I can understand it, but in the mean time...
Notice that I set it by File Type and not File Extension. I don't understand the nuance--it's never occurred to me that they were not the same.

For step 2, you will need to be a bit more specific. Is the sheet named Sheet1 in each Excel spreadsheet or just the sheet indexed as 1? Are the workbooks password protected? Text files do not have fields per se. They can have some structure if saved as a CSV sort of file. Just how did you want the first line inserted from each file to be flagged, bold font? Of course if you have lots of data, you could well have too many rows inserted. With newer versions of Excel, that is less likely. The storyline behind my efforts: my company handles inventories, which means I have staff that go out and scan barcodes all day long. Lots of data is collected and aggregated at the end of each day in multiple tab-delimited text files, and it's then necessary to inspect, manipulate and, in some cases, correct the data--easier to do when it's in one excel file.

From project to project the "fields" may change so I don't want to put the "header row" in a variable array within the code. However, I only need to aggregate at the single-project level.

I've been working to create an excel template with the two sheets, which could be used when needed on a project (wouldn't that be the easiest way to share the macro with my colleagues...more no fuss than simply giving them the macro?). Sheet would be indexed as 1--not named Sheet 1. Can't see a reason why these workbooks would ever need to be password protected. I'm not sure how exactly I'd want them flagged, probably italicized font, or possibly a cell fill color. Our project history gives me no reason to think we'd ever exceed Excel's row limits per sheet.


When troubleshooting, be sure to comment out On Error lines so that you will be notified of an error. You can use Debug to Step through code line by line with F8 if needed. What do you mean by "comment out?" I've looked for a way to toggle comment lines as I can in NPP, but don't see that functionality. Am I missing it? Separately, I'm afraid the error boxes don't give me enough to go on at this time...may as well tell me nothing. Can you advise me of the best resource for proper troubleshooting? And, I've tried stepping in to debug, but it seems to either fly through it too fast for me to see what it's doing, or stop immediately.


I must say, you are probably the king when it comes to use of Smilies. :)

King of the Smilies? I like it--I am StlSmiln, ha. Actually, I can't say that I'll be using them so liberally in the future, but they did add a little levity to my otherwise very frustrating day. :jester:

So I'm off to take a look at your code. Again, thank you so much Kenneth! I may have more questions after I look at it--hope that's okay.

Much gratitude,
Courtney

Kenneth Hobs
06-23-2012, 09:35 PM
VBE is the Visual Basic Editor. If you right click the toolbar of icons, you can add another Command option from the Edit set if you select Customize after right clicking. You can drag and drop the Comment Block and Uncomment Block commands to your toolbar if you like. These simply add the single quote or remove it from the block of code lines selected.

OF course comments will be the single quote character typically as most don't use the old Rem method.

Debug by F8 is one line at a time. I like to use MsgBox or Debug.Print for quick debugs. Debug.Print output goes to your Immediate Window. Tip: You can quickly test single lines of code by pressing Enter key after a line of code in the Immediate Window. If your prefix some code with a question mark, the result will be displayed on the next line after Enter key executes that line of code. e.g.
?21>20
Of course examples of your files attached will get you the most exact help. Sometimes it is worth it just to make those examples but be sure to make them formatted as your real data is. Too many times I was not able to read between lines of what the user wrote and what they meant. Sometimes, a solution is best done something other than what a less experienced one might think. e.g. Foreground techniques are most always slower than background methods. This can come into play for text files especially.

StlSmiln
06-23-2012, 10:18 PM
Interesting phrase, "foreground techniques." Wow, you just opened my debugging/troubleshooting world!

Okay, I've attached a sample text file--is that the extent of what you mean I should share?

StlSmiln
06-24-2012, 09:18 AM
Kenneth,

Okay, I've looked at your code, and I have a couple questions. But first I want to say that I LOVE the way it's commented with links to relevant post threads that can help certain parts be understood.

Sub FileDetails()

'SpeedOn

ListMyFiles ThisWorkbook.Path, Range("A2"), True, "Text Document"

ActiveSheet.UsedRange.Columns.AutoFit
Range("C:C").HorizontalAlignment = xlCenter
Range("F:H").HorizontalAlignment = xlCenter
Range("A1").Activate
'SpeedOff
End Sub
1. The comments to SpeedOn and SpeedOff (as well as that first link) are irrelevant in this code correct? I mean, there isn't any actual code related to it like the module to change speeds that is given in the link. It's been mistakenly left in during a modification, correct?

ListMyFiles ThisWorkbook.Path, Range("A2"), True, "Text Document" Sub ListMyFiles(mySourcePath As String, sRow As Range, _
Optional IncludeSubfolders As Boolean = True, Optional FileType As String = "")
2. So these two lines of code work together to declare the variables and set their values at once? Does this mean that for this code as is, it will list files in the same folder as the workbook is saved in? How can I change that? I tried pasting the correct path over the "ThisWorkbook.Path" in the first line but it gives me a syntax error.

3. Within VBE, at one point if I floated over some of the variables it would show a popup with the value assigned to it (kind of like when you hit quick info on a variable), but I can't seem to make that happen again. Hitting quick info just gives me a popup of the type of variable that it is.

Thank you for your help!!!

Kenneth Hobs
06-24-2012, 09:38 AM
1. When writing data one cell at a time, all the sheet is recalculated and the change event for that cell fires as. This can slow things down. To speed up the process, rather than including the code to do it, I wrote a kb, Knowledge Base, article with the Speedup module with the SpeedOn and SpeedOff routines to make coding easier. You can import the Module should you wish to use those routines.

2. Just paste in your path as needed though I did not check to see if the path was valid. Notice that it has no trailing backslash. That makes a difference for some code. e.g.
ListMyFiles "x:\Excel\Ken", Range("A2"), True, "Text Document"
Optional parameters can be set a default value. I tend to be lazy and make them default to what I typically want. It also gives you this flexibility.
ListMyFiles "x:\Excel\Ken", Range("A2"), FileType:="Text Document"


3. During a debug session, a mouseover event will show the value of some variables executed earlier.

snb
06-24-2012, 01:04 PM
or

sub snb()
sn=split(createobject("wscript.shell").exec("cmd /c Dir G:\OF /s /b").stdout.readall,vbcrlf)

with createobject("scripting.filesystemobject")
for each fl in sn
c01=c01 & vbcrlf & .opentextfile(fl).readall
next
end with

sp=split(mid(c01,3),vbcrlf)
sheets(1).cells(1).resize(ubound(sn)+1)=application.transpose(sn)
sheets(2).cells(1).resize(ubound(sp)+1)=application.transpose(sp)
sheets(2).columns(1).texttocolumns , 1, -4142, , True, True, True, False
end sub

Kenneth Hobs
06-24-2012, 01:12 PM
For the step 2 part:

Sub Test_ImportTxtData()
Dim s1 As Worksheet, s2 As Worksheet
Dim c As Range, s1Range As Range, r As Range

Set s1 = Worksheets("Sheet1")
Set s1Range = s1.Range("A3:A" & s1.Range("A" & Rows.Count).End(xlUp).Row)
Set s2 = Worksheets("Sheet2")
Set r = s2.Range("A1")
Set c = s1.Range("A2")

' Import first txt file with header row.
ImportTxtData c.Value2, c.Offset(0, 1).Value2, s2, r, True

' Import others if needed.
If s1Range.Address(False, False) <> "A3" Then Exit Sub
For Each c In s1Range
Set r = s2.Range("A" & Rows.Count).End(xlUp).Offset(1)
ImportTxtData c.Value2, c.Offset(0, 1).Value2, s2, r
'Delete header row and mark first row added.
r.EntireRow.Delete shift:=xlUp
Rows(ActiveCell.Row).EntireRow.Font.Italic = True
Next c
End Sub


Sub ImportTxtData(sPath As String, sName As String, dSheet As Worksheet, _
dRange As Range, Optional tbFieldNames As Boolean = False)
Dim q As String, s As String
s = q & "TEXT;" & sPath & q
With dSheet.QueryTables.Add(s, dRange)
.Name = sName
.FieldNames = tbFieldNames
.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
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 3, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

StlSmiln
06-24-2012, 01:15 PM
Hi all,

I haven't made much progress with the second part--to import text files from a directory and all subdirectories into one worksheet.

The main code I'm trying to work with is below, but I can't figure out for the life of me how to have it filter through subfolders. My original plan was to append a modified form of this Import module to a List Files module and have the ListFiles module do the subdirectory work, but I can't seem to make tons of progress there either.

At this point, I'd just be happy to be able to import all the files from directory and subs into one sheet--with or without a list of files on a separate sheet. I would LOVE the macro to ask me
for the file path with the Application.FileDialog and then ask me with a MsgBox about including Sub Folders.

I've been trying to splice in a bit of code from jindon to facilitate that
I would sincerely appreciate any help that anyone can provide.

Option Explicit

Sub ImportListedFiles()

Dim nxt_row As Long

' 'Change Path
' Const strPath As String = "C:\"
Dim strExtension As String

Dim strPath As String
Dim SearchSubFolders As Boolean
Dim Rtn As Integer
' Dim Msg As String
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show Then
strPath = .SelectedItems(1)
Else
Exit Sub
End If
End With
' Msg = "Enter File name and Extension" & vbLf & "following wild" & _
' " cards can be used" & vbLf & "* # ?" & vbLf & "e.g .xls*"
' strExtension = Application.InputBox(Msg)
' If (strExtension = "False") + (strExtension = "") Then Exit Sub
Rtn = MsgBox("Include Sub Folders ?", vbYesNo)
SearchSubFolders = Rtn = 6

'Stop Screen Flickering
Application.ScreenUpdating = False

ChDir strPath

'Change extension
strExtension = Dir(strPath & "*.txt")

Do While strExtension <> ""

' 'Adds File Name as title on next row
' Range("A65536").End(xlUp).Offset(1, 0).Value = strExtension

'Sets Row Number for Data to Begin
nxt_row = Range("A65536").End(xlUp).Offset(1, 0).Row

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & strPath & strExtension, Destination:=Range("$A$" & nxt_row))
.Name = strExtension
.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
'Delimeter Settings:
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False

.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

strExtension = Dir
Loop

Application.ScreenUpdating = True

End Sub

StlSmiln
06-24-2012, 01:33 PM
Wow Kenneth!!!

Looks like we posted at the same time. I'm so thankful to you for your help!

Unfortunately, I can't seem to get the first part to work. At first I put in my folder path like you told me to do and when that didn't work I changed it back to the 'ThisWorkbook.Path.' But it still doesn't seem to do anything...and then the second part isn't going to work without the first.

Do you have any suggestions?

Kenneth Hobs
06-24-2012, 02:16 PM
Did you set the two references? Are the text files in the folder sent to the routine? Did you debug by F8 to see if all lines of code executed?

If you can't get the manual method of inputting the folder path to the text file's parent folder to work, inputting folder paths by a dialog method and prompts for subfolder iteration won't work either. Always do these things in steps. If one part does not work, adding bells and whistles will not work either.

Try running my example FileDetails() in Module1 after you change x:\fso\txt.

snb
06-24-2012, 02:18 PM
Did you overlook



Sub snb()

sn=split(createobject("wscript.shell").exec("cmd /c Dir G:\OF\*.txt /s /b").stdout.readall,vbcrlf)


With createobject("scripting.filesystemobject")
For Each fl In sn
c01=c01 & vbcrlf & .opentextfile(fl).readall
Next
End With


sp=split(mid(c01,3),vbcrlf)
sheets(1).cells(1).resize(UBound(sn)+1)=application.transpose(sn)
sheets(2).cells(1).resize(UBound(sp)+1)=application.transpose(sp)
sheets(2).columns(1).texttocolumns , 1, -4142, , True, True, True, False
End Sub

Aussiebear
06-24-2012, 03:10 PM
Did you overlook



Sub snb()

sn=split(createobject("wscript.shell").exec("cmd /c Dir G:\OF\*.txt /s /b").stdout.readall,vbcrlf)


With createobject("scripting.filesystemobject")
For Each fl In sn
c01=c01 & vbcrlf & .opentextfile(fl).readall
Next
End With


sp=split(mid(c01,3),vbcrlf)
sheets(1).cells(1).resize(UBound(sn)+1)=application.transpose(sn)
sheets(2).cells(1).resize(UBound(sp)+1)=application.transpose(sp)
sheets(2).columns(1).texttocolumns , 1, -4142, , True, True, True, False
End Sub






Can you blame people if they did? The code may or may not work, there is nothing included to assist people to follow what the code is doing, and there is no assistance being offered to assist others to learn what you are providing.

StlSmiln
06-24-2012, 03:49 PM
Did you overlook

I'm sorry, I don't understand. You're saying I'm missing something in your code?

StlSmiln
06-24-2012, 03:54 PM
Ohh...SNB, yes, I did overlook your original post. Still, I also agree with AussieBear because I'm not sure I can follow what has no explanation at this point--I'm VERY NEW to this. Sorry. Thanks for your help though!

StlSmiln
06-24-2012, 04:17 PM
Did you set the two references? Are the text files in the folder sent to the routine? Did you debug by F8 to see if all lines of code executed?

If you can't get the manual method of inputting the folder path to the text file's parent folder to work, inputting folder paths by a dialog method and prompts for subfolder iteration won't work either. Always do these things in steps. If one part does not work, adding bells and whistles will not work either.

Try running my example FileDetails() in Module1 after you change x:\fso\txt.

Totally true about the bells and whistles. Yes, I did set the two references. And actually, I can't get your example to work either--I tried it with and without a trailing slash.

Is there a way to set up an error log or something? I'm going to start doing the line by line now, but can you give me a suggestion as to the best way to figure out the vague errors when they give them to me? Many of them don't really even give enough to Google, because they're so broad.

Kenneth Hobs
06-24-2012, 04:20 PM
I like to use Option Explicit as the first line in my Modules so Dim is required. This sometimes makes variables a bit more clear. Dim keeps the case of the variable consistent too.

My modification of snb's code should be fairly obvious. Notice how I trimmed the last element of the array that he created since it will be empty. His code use the concept of the DOS command Dir. In the Start > Run, type cmd and the enter key. Then type Help Dir and Enter key to see the help for the command line switches of /b for bare and /s to iterate subfolders.

Obviously, more modification is needed to fit all of your requested needs but it gives you an idea on how to use his concept.

In the link that I posted earlier, there is a link to snb's web site where he posted similar examples.

Obviously, you need to replace the string value for bPath. Test in a blank workbook as usual when testing code or make a backup of your file.
Option Explicit

Sub snb()
Dim sn() As String, fl As Variant, c01 As String, sp() As String
Dim bPath As String

bPath = "x:\fso\txt\"
sn() = Split(CreateObject("wscript.shell").exec("cmd /c Dir " & bPath & "*.txt /s /b").stdout.readall, vbCrLf)

ReDim Preserve sn(0 To UBound(sn) - 1)
With CreateObject("scripting.filesystemobject")
For Each fl In sn()
c01 = c01 & vbCrLf & .opentextfile(fl).readall
Next
End With

sp = Split(Mid(c01, 3), vbCrLf)
Sheets(1).Cells(1).Resize(UBound(sn) + 1) = Application.Transpose(sn)
Sheets(2).Cells(1).Resize(UBound(sp) + 1) = Application.Transpose(sp)
Sheets(2).Columns(1).TextToColumns , 1, -4142, , True, True, True, False
End Sub

Kenneth Hobs
06-24-2012, 04:32 PM
Before I ever run code, I Compile it. In the VBE, you can add that button. Right click the toolbar and Customize. In the category Debug, drag and drop the Compile Project to the toolbar.

Does not run means little to anyone. Error messages are informative. IF not to you then to others. Since we can't look over your shoulder, there is just so much that we can guess.

If you can not get my code to work after changing the folder path, I don't know that posting your workbook would help but you can if you like.

Try closing Excel and reopening. If an event was disabled, that will restore it. If an event was disabled with my SpeedOn routine and you did not have an On Error Goto the SpeedOff, you type run SpeedOff in the Immediate window. I guess you enabled macros or turned macro security to low.

When you looked at Tools > References in my example, did you see Missing for any library object?

Kenneth Hobs
06-24-2012, 06:05 PM
IF you want a more descriptive error message, use this method.

Sub DataTable()
CorrectTimepoint 1
MsgBox "CorrectTimepoint generated no error."
End Sub

Sub CorrectTimepoint(aNumber As Long)
Dim Msg As String
On Error GoTo ErrMsg
If aNumber <> 0 Then Err.Raise aNumber
MsgBox "aNumber=0 so no error was generated."

Exit Sub
ErrMsg:
Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
End
End Sub

StlSmiln
06-24-2012, 06:50 PM
When you say use this method do you mean just copy that code into a module? Is that all I have to do to make it record errors?

Okay, so I haven't been able to get your first part to work because it just doesn't want to see my path. I don't know. BUT, I've attached your second part to the code provided by Excel Experts (VBA Tip 21) and it is partially working!!!:clap:

It IS importing the first file, but none of the following. And, could you help me modify the ExcelExperts part to only list text files? Might it be the way I called it? Or the fact that I had to get rid of "Option Explicit" because the ExcelExperts code didn't use it and it got squirley when I tried to include it. I may have mislabeled the variable types when I tried to include all of the Dims.

Here's the file that I'm working with...

snb
06-25-2012, 12:51 AM
Cfr. the attachment

NB. Avoid merged cells if working with VBA
Adapt the path in Cell C3 before running the code.

Kenneth Hobs
06-25-2012, 05:23 AM
I guess you could make it store the error description in a cell but I use MsgBox as shown in the example. Maybe this will be more clear.

Sub t()
Dim Msg As String
On Error GoTo ErrMsg

' *********** Delete line below and add your code. *********************
MsgBox 5 / 0


Exit Sub
ErrMsg:
Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
End
End Sub

StlSmiln
06-25-2012, 05:25 AM
Cfr. the attachment

NB.

Snb, what does Cfr. and NB. mean?

StlSmiln
06-25-2012, 06:01 AM
Kenneth,

Good morning!

I keep getting a 1004 error (Application-defined or object-defined error) with the line ".Refresh BackgroundQuery:=False" near the bottom of the ImportTxt loop :banghead:

Sub ImportTxtData(sPath As String, sName As String, dSheet As Worksheet, _
dRange As Range, Optional tbFieldNames As Boolean = False)
Dim q As String, s As String
s = q & "TEXT;" & sPath & q
With dSheet.QueryTables.Add(s, dRange)
.Name = sName
.FieldNames = tbFieldNames
.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
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 3, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

(http://%20http://office.microsoft.com/client/helppreview.aspx?AssetId=HV805533949990&lcid=3081&NS=EXCEL.DEV&Version=12&pid=CH080553391)http://office.microsoft.com/client/helppreview.aspx?AssetId=HV805533949990&lcid=3081&NS=EXCEL.DEV&Version=12&pid=CH080553391
says that this is used only with query tables based on the results of an SQL query. Is there another type of query?

http://www.officekb.com/Uwe/Forum.aspx/excel-prog/142846/how-delete-old-querytables and this talks about needing to delete old query tables because every time you run them the file gets bigger and bigger.

Am I even looking in the right direction?

StlSmiln
06-25-2012, 06:12 AM
And actually, this morning what worked last night suddenly isn't working!!!:cry:

I even looked at the one I uploaded and it's def not working. How could this be? I did NOTHING to it between when it was working last night and when I uploaded it.

So it LISTS the first text file (but no others) and then gives me that 1004 runtime error related to the query table refresh. It does not import anything.

IF I simply hide all of the import subs and run it then it lists everything correctly, but having the Test_ImportTxtData () sub there makes it go whonky.

Kenneth Hobs
06-25-2012, 06:18 AM
I had problems with both of the last two attachments. Here is snb's attachment. Be sure to include the trailing backslash. After running, you get some of what you wanted. As I explained before, more code would be needed to add the other file details and clean up sheet2's extra blank and header rows. That is not difficult though.

The method that you reference Courtney is similar to what I did. I just added to it as you can see from the thread that I referenced. Before I get into fixing your last example, you probably need to understand the fso method. It uses a concept called recursion. You added a last line of code that repeats at every recursive iteration for each subfolder. What you should have done was to write a 3rd routine that runs the step 1 code and then the step 2 code. Try to think modular when you code. If you make a routine that can be used over and over, it makes future projects much easier.

When using fso, File System Object methods, you may need to see if your computer can do it. Some employers disable it as I explained in my first referenced thread.

What version of Excel are you using?

Kenneth Hobs
06-25-2012, 06:51 AM
Fixing your code Courtney, I had to modify as I did mine to get just the text files. Rather ALL Text Documents, I made it look for the file extension TXT. This is mostly what I posted earlier so it if does not work for you then I don't why other than what I explained before. It does violate my coding rule to always use Option Explicit. It uses a global variable which is another thing that I try to avoid.


Dim iRow

Sub ListFiles()
Sheets(1).Range("B10:B" & Sheets(1).Range("B" & Rows.Count).End(xlUp).Row).Delete

iRow = 10

ListMyFiles Range("C3"), Range("C4")
Test_ImportTxtData

Columns("C:E").AutoFit
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
Dim myFile As File, MyObject As Scripting.FileSystemObject

Set MyObject = New Scripting.FileSystemObject
Set mySource = MyObject.GetFolder(mySourcePath)
On Error Resume Next

For Each myFile In mySource.Files
With myFile
If LCase(MyObject.GetExtensionName(.Path)) = "txt" Then
iCol = 2
Cells(iRow, iCol).Value = .Path
iCol = iCol + 1
Cells(iRow, iCol).Value = .Name
iCol = iCol + 1
Cells(iRow, iCol).Value = .Size
iCol = iCol + 1
Cells(iRow, iCol).Value = .DateLastModified
iRow = iRow + 1
End If
End With
Next
If IncludeSubfolders Then
For Each mySubFolder In mySource.SubFolders
Call ListMyFiles(mySubFolder.Path, True)
Next
End If
End Sub


Sub Test_ImportTxtData()
Dim s1 As Worksheet, s2 As Worksheet
Dim c As Range, s1Range As Range, r As Range, r2 As Range

Set s1 = Sheets(1)
Set s1Range = s1.Range("B11:B" & s1.Range("B" & Rows.Count).End(xlUp).Row)
Set s2 = Sheets(2)
Set r = s2.Range("A1")
Set c = s1.Range("B10")

' Import first txt file with header row.
ImportTxtData c.Value2, c.Offset(0, 1).Value2, s2, r, True

' Import others if needed.
If s1Range.Address(False, False) <> "B11" Then Exit Sub
For Each c In s1Range
Set r = s2.Range("A" & Rows.Count).End(xlUp).Offset(1)
Set r2 = r.Offset(1)
ImportTxtData c.Value2, c.Offset(0, 1).Value2, s2, r
'Delete header row and mark first row added.
r.EntireRow.Delete shift:=xlUp
r2.Rows.EntireRow.Font.Italic = True
Next c
End Sub


Sub ImportTxtData(sPath As String, sName As String, dSheet As Worksheet, _
dRange As Range, Optional tbFieldNames As Boolean = False)
Dim q As String, s As String
s = q & "TEXT;" & sPath & q
With dSheet.QueryTables.Add(s, dRange)
.Name = sName
.FieldNames = tbFieldNames
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.Refresh AStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 3, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

StlSmiln
06-25-2012, 07:13 AM
WOW!!! There is so much less code in Snb's--at first glance, I thought "how did he get all of that done it that?"

I see the command prompt and can work through some of it...BUT, would ANYone mind helping me with the original code shared and modified to this point (shown below)?

I know there is a lot of value seeing different means of coming up with the same end, but as I'm so new I'd like to understand one functional method first. Kenneth, would that be okay with you? I'm trying to work through what you and I have been working on, although I'm grateful for Snb's alternative it's much different than all of the other methods I've been familiarizing myself with.

I checked the scripting on this computer and it's not a problem.

Dim iRow
Sub ListFiles()
Sheets(1).Range("B10:E65536").Select
Selection.Delete

iRow = 10
Call ListMyFiles(Range("C3"), Range("C4"))
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)

Set MyObject = New Scripting.FileSystemObject
Set mySource = MyObject.GetFolder(mySourcePath)
On Error Resume Next

For Each myFile In mySource.Files

iCol = 2
Cells(iRow, iCol).Value = myFile.Path
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.Name
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.Size
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.DateLastModified
iRow = iRow + 1
Next
Columns("C:E").AutoFit
If IncludeSubfolders Then
For Each mySubFolder In mySource.SubFolders
Call ListMyFiles(mySubFolder.Path, True)
Next
End If
Call Test_ImportTxtData
End Sub


Sub Test_ImportTxtData()
Dim s1 As Worksheet, s2 As Worksheet
Dim c As Range, s1Range As Range, r As Range

Set s1 = Sheets(1)
Set s1Range = s1.Range("B11:B" & s1.Range("B" & Rows.Count).End(xlUp).Row)
Set s2 = Sheets(2)
Set r = s2.Range("A1")
Set c = s1.Range("B10")

' Import first txt file with header row.
ImportTxtData c.Value2, c.Offset(0, 1).Value2, s2, r, True

' Import others if needed.
If s1Range.Address(False, False) <> "B11" Then Exit Sub
For Each c In s1Range
Set r = s2.Range("A" & Rows.Count).End(xlUp).Offset(1)
ImportTxtData c.Value2, c.Offset(0, 1).Value2, s2, r
'Delete header row and mark first row added.
r.EntireRow.Delete shift:=xlUp
Rows(ActiveCell.Row).EntireRow.Font.Italic = True
Next c
End Sub


Sub ImportTxtData(sPath As String, sName As String, dSheet As Worksheet, _
dRange As Range, Optional tbFieldNames As Boolean = False)
Dim q As String, s As String
s = q & "TEXT;" & sPath & q
With dSheet.QueryTables.Add(s, dRange)
.Name = sName
.FieldNames = tbFieldNames
.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
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 3, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub


you probably need to understand the fso method. It uses a concept called recursion. You added a last line of code that repeats at every recursive iteration for each subfolder.
Are you refering to my adding Call Test_ImportTxtData I did think that I was keeping it outside the recursion by being after the 'End If.' Is that incorrect?

Can you explain what this line is saying in human (I can't follow it):

Set s1Range = s1.Range("B11:B" & s1.Range("B" & Rows.Count).End(xlUp).Row)

What you should have done was to write a 3rd routine that runs the step 1 code and then the step 2 code. Try to think modular when you code. If you make a routine that can be used over and over, it makes future projects much easier.
You are absolutely right on me needing to think more modular. How do you keep these routines for the future? Do you house them in the Personal.xlsb file? With modules, I can put different sub routines in separate modules and just 'call' them? I assume they do have to be in the same workbook?

StlSmiln
06-25-2012, 07:14 AM
Kenneth,

THANK YOU! Please don't get frustrated with me...I am trying. I'm going to check out Lynda.com right now to see if I can find some videos that can help me quickly bone up on this.

Cheers!
Courtney

StlSmiln
06-25-2012, 07:18 AM
It does violate my coding rule to always use Option Explicit. It uses a global variable which is another thing that I try to avoid.


Oops, we posted at the same time again. I'll be adopting the Option Explicit rule--it's MUCH EASIER for me to see the variables with Dims. Actually, when I start looking at new routines I wind up separating them all out and adding Dims. I don't even love when variables are declared in long lists that simply don't repeat the 'Dim' but I get why good coders do it that way.

Why do you dislike the global variable?

Kenneth Hobs
06-25-2012, 07:31 AM
Are you refering to my adding Yes.


Can you explain what this line is saying in human (I can't follow it):
Yes. Start the block for the range at cell B11. Go to the last row in column B. If you press End key and then the Up arrow, the activecell would then be the last cell with data in column B. This is more efficient than trying to guess at the users number of rows with data in column B. It is standard practice.

Yes, I use the Personal workbooks to store Modules. e.g. mStrings is the name of the module that I store string routines into. mFSO is the Module that I store FSO methods into. mDicitonary is my Module for dictionary code examples. SpeedUp is the module with my SpeedOn and SpeedOff routines. While you can export and import Modules, I just drag and drop from my personal workbook to my current workbook to quickly copy a Module or other object. Don't forget to backup your Personal workbook(s) occasionally. Always comment where you got code so that you can give credit and you know where to point others to rather than reposting code. IF you do this, be sure to only put well documented and well code into them where you use Option Explicit. This helps you avoid problems later on. Always Compile after each addition and then Save the workbook.

You can run code from another workbook or the current workbook by Application.Run. e.g.
Application.Run "'" & ThisWorkbook.Path & "\RunExample.xlsm'!Module1.Button807_Click", 3
Global variables make it difficult to create a standalone module for one thing. Another thing is that it adds to the stack memory and is less efficient.

You can add the Option in the VBE Tools menu.

StlSmiln
06-25-2012, 08:40 AM
So I went through the ListFiles routines and watched every expression change through an F8 line by line debug scenario, and I think I understand the recursion better now.

So the only error I keep getting on this new code is on the .RefreshBackgroundQuery:=False

I'm using Excel 2010 and sometimes 2007.

StlSmiln
06-25-2012, 08:44 AM
OH, but I = was getting an error on the line

.Refresh AStyle = xlInsertDeleteCells

So I changed it back to...

queryTable.RefreshStyle = xlInsertDeleteCells

Why I had you made that change? I couldn't find anything on it with Google.

Kenneth Hobs
06-25-2012, 09:01 AM
Obviously "A" typo, not:
.Refresh AStyle = xlInsertDeleteCells But:
.RefreshStyle = xlInsertDeleteCells
That is why Intellisense is handy.

StlSmiln
06-25-2012, 09:17 AM
Strange thing happening...

I opened a new workbook, because while exploring the 1004 error w/

.Refresh BackgroundQuery: = False

I read about macros working the first time, and then getting this error all subsequent runs. So in the new workbook I input my path in 'C3' and "True" in 'C4' and ran the same macro.

It almost immediately gets an Error 5 (invalid procedure call or argument) on
Set mySource = MyObject.GetFolder(mySourcePath) in the ListMyFiles subroutine. Watching the actual spreadsheet while running it, I see that the path and boolean that I'd put in 'C3 and C4' are immediately moved to 'B3 and B4' which is what I think is causing the problem. What do you think? And what would cause that shift?

StlSmiln
06-25-2012, 09:19 AM
Think it's time to give up?

Kenneth Hobs
06-25-2012, 09:52 AM
I guess that you added Microsoft Scripting Runtime reference in the new workbook?

Obviously, if you pass an invalid or non-exisistant path error 76 would tell you.

It is good practice to Dim your parameters to the type expected rather than nothing which means that it is Variant. I don't like to rely on an object's default property either. In my routine, I addressed all of these issues.

Tweaking your code for some of this:

Sub ListFiles()
Sheets(1).Range("B10:B" & Sheets(1).Range("B" & Rows.Count).End(xlUp).Row).Delete

iRow = 10

ListMyFiles Range("C3").Value2, Range("C4").Value2
Test_ImportTxtData

Columns("C:E").AutoFit
End Sub

Sub ListMyFiles(mySourcePath As String, IncludeSubfolders As String)
Dim myFile As File, MyObject As Scripting.FileSystemObject

Set MyObject = New Scripting.FileSystemObject
Set mySource = MyObject.GetFolder(mySourcePath)
On Error Resume Next

For Each myFile In mySource.Files
With myFile
If LCase(MyObject.GetExtensionName(.Path)) = "txt" Then
iCol = 2
Cells(iRow, iCol).Value = .Path
iCol = iCol + 1
Cells(iRow, iCol).Value = .Name
iCol = iCol + 1
Cells(iRow, iCol).Value = .Size
iCol = iCol + 1
Cells(iRow, iCol).Value = .DateLastModified
iRow = iRow + 1
End If
End With
Next
If IncludeSubfolders Then
For Each mySubFolder In mySource.SubFolders
Call ListMyFiles(mySubFolder.Path, True)
Next
End If
End Sub

StlSmiln
06-25-2012, 10:08 AM
This last code gives me compile errors that the sub or function are not defined in the Sub ListFiles().

Is this working on your end?

Either way, I can't get past this .Refresh BackgroundQuery error

Kenneth Hobs
06-25-2012, 10:12 AM
Almost every single code that I post was tested. If something is missing like the other two Subs, just add them in. There is no use repeating code that has been posted earlier.

snb
06-26-2012, 01:22 AM
I don't want to interfere; but only for illustration purposes:

Sub snb_filesearch()
sn = Filter(Split(Application.Trim(Replace(CreateObject("wscript.shell").exec("cmd /c dir " & range("C3").value & "*.txt /s").StdOut.readall, vbLf & " Map", vbCrLf & ".txtMap")), vbCrLf), ".txt")

ReDim sp(UBound(sn), 3)
For j = 0 To UBound(sn)
If Left(sn(j), 8) = ".txtMap " Then
c00 = Right(Split(sn(j), ":\")(0), 1) & ":\" & Split(sn(j), ":\")(1)
Else
sp(j, 0) = c00
sq = Split(sn(j))
sp(j, 1) = Left(sn(j), 16)
sp(j, 2) = sq(2)
sp(j, 3) = Trim(Split(Mid(sn(j), 17), sq(2))(1))
End If
Next

Cells(1).Resize(UBound(sp) + 1, 4) = sp
Columns(1).SpecialCells(4).EntireRow.Delete
End Sub

StlSmiln
06-27-2012, 11:15 AM
The point is, SNB, the code ALONE doesn't illustrate anything to me...or (I'm sure) anyone else that doesn't already know what they're doing. But thank you for the tip to avoid merged cells when using VBA.

snb
06-27-2012, 01:18 PM
No harm done.
Maybe someone else interested in VBA will be triggered to dive into the code.

Sayre
06-30-2014, 11:06 PM
I'm using the code originally posted by Kenneth Hobbs on this topic in post #2 and #9. It works great!

I'm new to this board but not new to vba although still pretty intermediate at best. My limited abilities have been very helpful throughout my career though and I can usually get by just searching on code, pastng and modify. When it comes to importing data from other files/directories/subdirectories I'm trying to learn more.

I have a few questions if Kenneth Hobbs or anyone else could help, much appreciated!

1) The code only works for me on my local machine. I first tried on a version of workbook stored on a network drive through my citrix xen app connection and part 2 (from post #9) would only inmport the 1st file on the list and then exit even though there were many more file/file paths list in successive rows. I tried it on a copy on my local machine and it imported all text files just fine. Any dea if it was the network drive, the citrix, or a combo of both that was stopping it from working? I think it gets to this line in the Sub Test_ImportTxtData() after importing the first file...

If s1Range.Address(False, False) <> "A3" Then Exit Sub
... and does not detect anymore rows (even though they are clearly there) and just exits.

2) I'd really love a couple mods to this that would make it even more beneficial I anyone is game:

- Have the first part (from post #2) work on more than just TEXT files but not all files, I'd love to keep a list (in 'mySheet' Col A) of the file types I want to be able to import (partials values should be ok!), and have it list out files on 'Sheet1' with *like* partial value strings from the file type meta data. I have folder directories with .OUT, .CSV, etc... and not sure which I will need data from depending on each directory.

- Then of course have part 2 (from post #9) import these files into 'Sheet 2' regardless of their file type. And have inserted in column A, which file the data came from for every row of data imported.

- Another option that would be great to have: keep another list (on 'mySheet' Col B) of key words (partial values again need to be ok). Macro looks for each key word inside each file listed on 'Sheet1' and imports only those files where it found a *like* match.

Anyone care to take a run at it? Would be extremely helpful to make progress on ay of these fronts.

I know I'm asking a lot, if anyone wants to help with just a part of it or point me at other threads solving similar, I'll definitely follow up on anything suggested ;) I've been searching for stuff like this all over and posted a question on another forum about this (wich I closed the loop on) so anything new I've not seen yet will be interesting. Thank you!

Sayre
07-01-2014, 10:24 AM
I said a lot above, apologies if I overloaded. Starting again smaller.

With Kenneth Hobbs' original code below, can anyone show me quick syntax to modify so it looks for more than just text files?

Sub FileDetails()

SpeedOn

ListMyFiles ThisWorkbook.PATH, Range("A2"), True, "Text Document"

ActiveSheet.UsedRange.Columns.AutoFit
Range("C:C").HorizontalAlignment = xlCenter
Range("F:H").HorizontalAlignment = xlCenter
Range("A1").Activate
SpeedOff
End Sub


I need text, csv, log, .out files, etc.... I can get rid of the ", Text Document" at the end of this line:
ListMyFiles ThisWorkbook.PATH, Range("A2"), True, "Text Document"
and that will give me all files, but I don't want all files, I just want to be able to designate more than one file type on this particular bit of code. Its probably simple but I can't seem to get it. Can anyone help?

Kenneth Hobs
07-01-2014, 11:18 AM
Please ask your questions in your own new thread. Since you are new and can not post a link yet, just show or copy some of what you posted here and say see link 42676.

Sayre
07-01-2014, 02:28 PM
OK I posted in a new thread. Thanks for any help you can provide good sir!