Macro to LIST ALL FILES IN FOLDER and then IMPORT ALL LISTED FILES

[StlSmiln]

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:

1. 21. VBA Tip on ExcelExperts dot com (Sorry, I can't give links in the forum yet)
2. 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:

[vba]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[/vba]
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. I tried a bunch of things that my novice brain could come up with and searched the web for answers, but several hours went by and I still couldn't figure it out.

My totally VBA-ignorant brain came up with some genius hypotheses , "Maybe I've used the same variable in multiple modules, or created too many variations of the modules, and have confused the system....Or maybe I've screwed something up with the way that VBA modules relate/interact with each other while trying to splice them together." With that in mind, I decided to take the zen approach 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!!!

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. New panic. Took me only a moment to realize that in my haste I hadn't entered the directory path as needed. Relief--dodged a bullet. Ran it again. Nothing. 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......finally defeated , 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 I realized...I'd input the wrong directory path. And the worst part? I NOW also realize that was the problem this morning--BEFORE I'D ERASED ALL OF MY WORK!!! 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, 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]

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.

[VBA]' Windows folder and file details for windows versions and a VBA macro:
' http://www.kixtart.org/forums/ubbthr...=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

[/VBA]

[StlSmiln]

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.

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]

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.
[vba]?21>20[/vba]
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]

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]

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.

[vba]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[/vba]
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?

[vba] ListMyFiles ThisWorkbook.Path, Range("A2"), True, "Text Document"[/vba] [vba]Sub ListMyFiles(mySourcePath As String, sRow As Range, _
Optional IncludeSubfolders As Boolean = True, Optional FileType As String = "")[/vba]
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]

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.
[vba]ListMyFiles "x:\Excel\Ken", Range("A2"), True, "Text Document" [/vba]
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.
[vba]ListMyFiles "x:\Excel\Ken", Range("A2"), FileType:="Text Document" [/vba]


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

[snb]

or

[vba]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
[/vba]

[Kenneth Hobs]

For the step 2 part:

[VBA]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

[/VBA]

[StlSmiln]

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.

[vba]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



[/vba]

[StlSmiln]

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]

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]

Did you overlook


[vba]

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

[/vba]

[Aussiebear]

Did you overlook


[vba]

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

[/vba]

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]

Did you overlook

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

[StlSmiln]

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]

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]

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.
[VBA]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[/VBA]

[Kenneth Hobs]

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]

IF you want a more descriptive error message, use this method.

[VBA]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[/VBA]