Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

reading text all text files in a folder into excel.

Posted on 2004-11-18
4
Medium Priority
?
155 Views
Last Modified: 2010-05-19
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
0
Comment
Question by:ohmetw
  • 3
4 Comments
 
LVL 28

Expert Comment

by:vinnyd79
ID: 12618850
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
 
LVL 28

Expert Comment

by:vinnyd79
ID: 12618876
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
 

Author Comment

by:ohmetw
ID: 12619055
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
 
LVL 28

Accepted Solution

by:
vinnyd79 earned 2000 total points
ID: 12621107
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month21 days, 2 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question