reading text all text files in a folder into excel.

I have an automated process that writes a text file to a folder on our network drive everytime someone accesses a spreadsheet.  I need write code to automatically check this folder for files every 5 minutes and if there is a file, to open it and import the data.

How do I check for files in this folder.   I want this code to loop until there aren't anymore files in the folder.  (Once the file is imported, my code deletes the text file)  Things are getting out of control and I'm approaching 2000 text files!!!   please help.


So I am asking for the part of the code below in caps:

1.CHECK FOLDER FOR FILES
2.if file present, open file and import data into sheet
3.close and delete file.  
4. LOOP BACK TO STEP 1


thank you!!


Todd
ohmetwAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vinnyd79Commented:
Have you tried something like this?


' add reference to Microsoft Excel Object Library

Private Sub Command1_Click()
Dim FileName As String, DirName As String, Ln As String, eDat As String
Dim ff As Integer, i As Integer
Dim oExcel As Excel.Application
Dim oWorkbook As Excel.Workbook
Dim oSheet As Excel.Worksheet

' set up excel
Set oExcel = CreateObject("excel.application")
Set oWorkbook = oExcel.Workbooks.Add
Set oSheet = oWorkbook.Sheets.Add

i = 1

DirName = "C:\TestDir\"
FileName = Dir(DirName & "*.*")

Do Until FileName = ""
    eDat = ""
    ff = FreeFile
    Open DirName & FileName For Input As #ff
    While Not EOF(ff)
        Line Input #ff, Ln
            If eDat = "" Then
                eDat = Ln
            Else
                eDat = eDat & vbCrLf & Ln
            End If
    Wend
    Close #ff
   
    ' uncomment to delete file
   ' Kill DirName & FileName

    oSheet.Cells(i, 1).Value = Ln
    i = i + 1
   
   
    FileName = Dir
DoEvents
Loop

oExcel.Visible = True
oSheet.Activate
               
Set oSheet = Nothing
Set oWorkbook = Nothing
Set oExcel = Nothing

End Sub
0
vinnyd79Commented:
Disregard the above and try this:

' add reference to Microsoft Excel Object Library

Private Sub Command1_Click()
Dim FileName As String, DirName As String, Ln As String, eDat As String
Dim ff As Integer, i As Integer
Dim oExcel As Excel.Application
Dim oWorkbook As Excel.Workbook
Dim oSheet As Excel.Worksheet

' set up excel
Set oExcel = CreateObject("excel.application")
Set oWorkbook = oExcel.Workbooks.Add
Set oSheet = oWorkbook.Sheets.Add

i = 1

DirName = "C:\TestDir\"
FileName = Dir(DirName & "*.*")

' loop through files
Do Until FileName = ""
    eDat = ""
    ff = FreeFile
   
    ' open file and read into eDat string
    Open DirName & FileName For Input As #ff
    While Not EOF(ff)
        Line Input #ff, Ln
            If eDat = "" Then
                eDat = Ln
            Else
                eDat = eDat & vbCrLf & Ln
            End If
    Wend
    Close #ff
   
    ' uncomment to delete file
   ' Kill DirName & FileName

    ' add to excel
    oSheet.Cells(i, 1).Value = eDat
    i = i + 1
   
    ' get next file
    FileName = Dir
DoEvents
Loop

oExcel.Visible = True
oSheet.Activate
               
Set oSheet = Nothing
Set oWorkbook = Nothing
Set oExcel = Nothing

End Sub
0
ohmetwAuthor Commented:
ok, this gets my data into one cell....cell a1.   How can I get my data distributed across a row.   cells a1,a2,a3,a4,a5,etc.  Then I should be all set


so the first file goes into row1, second file in row2, etc


thank you for the help
0
vinnyd79Commented:
I posted in your other question where you had the sample file.

When adding the second param is for the column

oSheet.Cells(<Row>, <COL>).Value   = "Data"

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.