ohmetw
asked on
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
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
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.applic ation")
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
' 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.applic
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
ASKER
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
so the first file goes into row1, second file in row2, etc
thank you for the help
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
' 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.applic
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