Help importing multiple text files to individual sheets in an excel workbook

I am trying to import multiple text files form a specific directory (C:\Documents and settings\user\desktop\test) into a new excel workbook, pasting each text file onto its own sheet in order. The text files are numerically named 25, 500, 1000, 2000, 3000, etc.. up to 40000.

The naming of the sheets is not important, but the text files must be placed into the sheets in order.

Ultimately, I would like to run a script that checks my test folder automatically every 10 minutes for text files, and if found, puts them all in the excel workbook and saves it, then runs a set of macros which I have already written to process the workbook into reports.

For now, I just need to know how to write a script to call up check the folder for text files, if found then call up excel and paste in order to sheets in a workbook, then save the workbook. If no files are found, I need it to wait 10 minutes and then run the check again.

I am using Excel 2007.

Here is the code I have so far:

Sub LoadSpaceDelimitedFiles()


Dim idx As Integer
Dim fpath As String
Dim fname As String

    idx = 0
    fpath = "c:\documents and settings\user\desktop\test"
    fname = Dir(fpath & "*.txt")
    While (Len(fname) > 0)
        idx = idx + 1
        Sheets("Sheet" & idx).Select
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
          & fpath & fname, Destination:=Range("A1"))
            .Name = "a" & idx
            .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 = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = True
            .TextFileOtherDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
            fname = Dir
        End With
    Wend
    
End Sub

When I run the macro, nothing happens. I have text files loaded into the test folder, but there appears to be no activity when the code is run and no errors.

Thanks in advance.

Hello cham.miller,

Welcome to the Forum!

To make your posts easier to read, copy, and edit please wrap your code. I did it for you this time. Here is how you can do it next time.

How to wrap your Code using the # icon

1. Select all your code using the mouse.
2. Click on the # icon on the toolbar in the Message window. This will automatically wrap the text you selected with the proper Code tags to create a Code Window in your post.

To manually wrap your code, use these Bulletin Board Code Tags

[code] at the start of the first line,

[/code] at the end of the last line.

Your code looks sound so far.

Provide a couple of sample text files and the workbook you have this macro installed into, let's look at your stuff directly.

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.