Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing data from text files
Hello,
I need to combine information from multiple files. All the files contain the exact same format. The difference is each file contains a different item. I am trying to figure out how to automatically open the data files from Excel and insert them into Excel to create a list. Is Excel capable of going out to my PC and opening files? If I can do it for the first file than I would think the rest of the files could be done through a repeatable loop. There is a total of 111 files. This would be to painful to do manually. Additionally the files are sent to my PC via FTP from and Informix database daily. I need to try and refresh this data on a daily basis. This appears to going to be a complex macro. Some direction might be all that I need. If anyone has some suggestions or can point me to some websites that might go into detail this would be great. Any help will be greatly appreciated. Thanks, Jeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing data from text files
Ron DeBruin offers some code on opening workbooks and gathering
information. http://www.rondebruin.nl/copy3.htm Chip Pearson has some examples of File I/O. http://www.cpearson.com/excel/imptext.htm Microsoft has examples of file I/O. http://support.microsoft.com/support...eio/fileio.asp HTH Paul -------------------------------------------------------------------------------------------------------------- Be advised to back up your WorkBook before attempting to make changes. -------------------------------------------------------------------------------------------------------------- Hello, I need to combine information from multiple files. All the files contain the exact same format. The difference is each file contains a different item. I am trying to figure out how to automatically open the data files from Excel and insert them into Excel to create a list. Is Excel capable of going out to my PC and opening files? If I can do it for the first file than I would think the rest of the files could be done through a repeatable loop. There is a total of 111 files. This would be to painful to do manually. Additionally the files are sent to my PC via FTP from and Informix database daily. I need to try and refresh this data on a daily basis. This appears to going to be a complex macro. Some direction might be all that I need. If anyone has some suggestions or can point me to some websites that might go into detail this would be great. Any help will be greatly appreciated. Thanks, Jeff |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing data from text files
Jeff,
If you are doing this manually, than you can record your steps to automate the task. There has been much code posted in this forum in opening files in a folder. The code will loop through all the files (of a given type) in a folder and open and work on the files. See if the below will get you started... Do a Google search and post back with further questions. =================================== Here's some code from Dave Peterson this might get you started (but I didn't rename .txt to .old. I just kept track of which ones were processed): Option Explicit Sub testme2() Dim myfiles() As String Dim i As Integer Dim myfile As String Dim myfolder As String Dim txtwb As Workbook Dim conswb As Workbook Dim destcell As Range Set conswb = Workbooks.Add(1) myfolder = "C:\my documents\excel" With Application.FileSearch .NewSearch .LookIn = myfolder .SearchSubFolders = False .Filename = "*.txt" If .Execute() 0 Then ReDim Preserve myfiles(1 To .FoundFiles.Count) Application.StatusBar = "Found Files: " & .FoundFiles.Count For i = 1 To .FoundFiles.Count myfiles(i) = .FoundFiles(i) Next i Else MsgBox "There were no files found." Exit Sub End If End With For i = LBound(myfiles) To UBound(myfiles) Application.StatusBar = "Processing #" & i & ": " & myfiles(i) Workbooks.OpenText Filename:=myfiles(i), _ DataType:=xlDelimited, Comma:=True, _ FieldInfo:=Array(Array(1, 1), Array(2, 1)) Set txtwb = ActiveWorkbook With conswb.Worksheets(1) If Application.CountA(.Columns("a:a")) = 0 Then Set destcell = .Range("a1") Else Set destcell = .Range("a1").End(xlDown).Offset(1, 0) =================================== And another from Rod de Bruin Sub test() Dim i As Long With Application.FileSearch .NewSearch .LookIn = "c:\Data" .SearchSubFolders = False .MatchTextExactly = False .FileType = msoFileTypeAllFiles If .Execute(msoSortOrderDescending) 0 Then MsgBox "There were " & .FoundFiles.Count & " file(s) found." For i = 1 To .FoundFiles.Count Cells(i, 1).Value = .FoundFiles(i) Cells(i, 2).Value = FileDateTime(.FoundFiles(i)) Cells(i, 3).Value = FileLen(.FoundFiles(i)) Next i Else MsgBox "There were no files found." End If End With End Sub =================================== -- sb "Jeff Armstrong" wrote in message ... Hello, I need to combine information from multiple files. All the files contain the exact same format. The difference is each file contains a different item. I am trying to figure out how to automatically open the data files from Excel and insert them into Excel to create a list. Is Excel capable of going out to my PC and opening files? If I can do it for the first file than I would think the rest of the files could be done through a repeatable loop. There is a total of 111 files. This would be to painful to do manually. Additionally the files are sent to my PC via FTP from and Informix database daily. I need to try and refresh this data on a daily basis. This appears to going to be a complex macro. Some direction might be all that I need. If anyone has some suggestions or can point me to some websites that might go into detail this would be great. Any help will be greatly appreciated. Thanks, Jeff |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing data from text files
One got truncated (and minor tweaks--long instead of integer, random
capitalizations, and ".txt" instead of "*.txt". (It seems to work better on some versions of windows.) And used the .foundfiles(i) directly. Option Explicit Sub testme2() Dim i As Long Dim myFolder As String Dim txtWb As Workbook Dim consWb As Workbook Dim DestCell As Range Set consWb = Workbooks.Add(1) myFolder = "C:\my documents\excel" With Application.FileSearch .NewSearch .LookIn = myFolder .SearchSubFolders = False .Filename = ".txt" If .Execute() 0 Then Application.StatusBar = "Found Files: " & .FoundFiles.Count Else MsgBox "There were no files found." Exit Sub End If For i = 1 To .FoundFiles.Count Application.StatusBar = "Processing #" & i & ": " & .FoundFiles(i) Workbooks.OpenText Filename:=.FoundFiles(i), _ DataType:=xlDelimited, Comma:=True, _ FieldInfo:=Array(Array(1, 1), Array(2, 1)) Set txtWb = ActiveWorkbook With consWb.Worksheets(1) If Application.CountA(.Columns("a:a")) = 0 Then Set DestCell = .Range("a1") Else Set DestCell = .Range("a1").End(xlDown).Offset(1, 0) End If End With With txtWb.Worksheets(1) .UsedRange.Copy _ Destination:=DestCell End With txtWb.Close savechanges:=False Next i End With Application.StatusBar = False End Sub steve wrote: Jeff, If you are doing this manually, than you can record your steps to automate the task. There has been much code posted in this forum in opening files in a folder. The code will loop through all the files (of a given type) in a folder and open and work on the files. See if the below will get you started... Do a Google search and post back with further questions. =================================== Here's some code from Dave Peterson this might get you started (but I didn't rename .txt to .old. I just kept track of which ones were processed): Option Explicit Sub testme2() Dim myfiles() As String Dim i As Integer Dim myfile As String Dim myfolder As String Dim txtwb As Workbook Dim conswb As Workbook Dim destcell As Range Set conswb = Workbooks.Add(1) myfolder = "C:\my documents\excel" With Application.FileSearch .NewSearch .LookIn = myfolder .SearchSubFolders = False .Filename = "*.txt" If .Execute() 0 Then ReDim Preserve myfiles(1 To .FoundFiles.Count) Application.StatusBar = "Found Files: " & .FoundFiles.Count For i = 1 To .FoundFiles.Count myfiles(i) = .FoundFiles(i) Next i Else MsgBox "There were no files found." Exit Sub End If End With For i = LBound(myfiles) To UBound(myfiles) Application.StatusBar = "Processing #" & i & ": " & myfiles(i) Workbooks.OpenText Filename:=myfiles(i), _ DataType:=xlDelimited, Comma:=True, _ FieldInfo:=Array(Array(1, 1), Array(2, 1)) Set txtwb = ActiveWorkbook With conswb.Worksheets(1) If Application.CountA(.Columns("a:a")) = 0 Then Set destcell = .Range("a1") Else Set destcell = .Range("a1").End(xlDown).Offset(1, 0) =================================== And another from Rod de Bruin Sub test() Dim i As Long With Application.FileSearch .NewSearch .LookIn = "c:\Data" .SearchSubFolders = False .MatchTextExactly = False .FileType = msoFileTypeAllFiles If .Execute(msoSortOrderDescending) 0 Then MsgBox "There were " & .FoundFiles.Count & " file(s) found." For i = 1 To .FoundFiles.Count Cells(i, 1).Value = .FoundFiles(i) Cells(i, 2).Value = FileDateTime(.FoundFiles(i)) Cells(i, 3).Value = FileLen(.FoundFiles(i)) Next i Else MsgBox "There were no files found." End If End With End Sub =================================== -- sb "Jeff Armstrong" wrote in message ... Hello, I need to combine information from multiple files. All the files contain the exact same format. The difference is each file contains a different item. I am trying to figure out how to automatically open the data files from Excel and insert them into Excel to create a list. Is Excel capable of going out to my PC and opening files? If I can do it for the first file than I would think the rest of the files could be done through a repeatable loop. There is a total of 111 files. This would be to painful to do manually. Additionally the files are sent to my PC via FTP from and Informix database daily. I need to try and refresh this data on a daily basis. This appears to going to be a complex macro. Some direction might be all that I need. If anyone has some suggestions or can point me to some websites that might go into detail this would be great. Any help will be greatly appreciated. Thanks, Jeff -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing Large Text Files | Excel Discussion (Misc queries) | |||
Importing text-files | Excel Discussion (Misc queries) | |||
automate importing text files? | Excel Discussion (Misc queries) | |||
Importing Text Files | Excel Discussion (Misc queries) | |||
importing text files | Excel Programming |