My job for the last 15 years has been data retrieval & analysis in support of the Finance Department of a large company. As a principle I strongly suggest that you put all your data into a single table wherever possible - using MS Access if the number of records is likely to exceed the 65,536 Excel limit. The more powerful data handling capabilities of Excel such as Pivot Tales and Filters can then be used for analysis.
I also suggest that you do *not* put all your macros into a single routine, especially when importing text files. Files are likely to be corrupted at any time, and it is sometimes difficult to see where the problem lies. A more modular approach helps with this - as well as making it unnecessary to re-run the full routine when problem occur.
An alternative is to devide the code with message boxes of the sort :-
Code:
rsp = MsgBox("Do you wish to import the text file ?", vbYesNoCancel)
If rsp = vbCancel Then End
If rsp = vbYes Then ................
The code below consolidates data from all files in a folder :-
Code:
'===============================================
'- Generic code for processing all
'- workbooks contained in a folder.
'- workbooks must be the only ones in the folder.
'- Change "Sub Transfer_data()" etc. as required.
'------------------------------------------------
'- in this example to summarise tables in all
'- worksheets in all workbooks :-
'- worksheets must be contain tables which are
'- identical to the master, with headings in row 1.
'- master sheet is remade each time.
'- run this code from the master book
'- by Brian Baulsom (BrianB) January 1st.2004
'----------------------------------------------
Dim ToBook As String
Dim ToSheet As Worksheet
Dim NumColumns As Integer
Dim ToRow As Long
Dim FromBook As String
Dim FromSheet As Worksheet
Dim FromRow As Long
Dim LastRow As Long
'-
'--------------------------
Sub FILES_FROM_FOLDER()
'--------------------------
Application.Calculation = xlCalculationManual
ChDrive ActiveWorkbook.Path
ChDir ActiveWorkbook.Path
ToBook = ActiveWorkbook.Name
'---------------------------
'- MASTER SHEET
'---------------------------
Set ToSheet = ActiveWorkbook.Worksheets(1)
NumColumns = ToSheet.Range("A1").End(xlToRight).Column
ToRow = ToSheet.Range("A65536").End(xlUp).Row
'- clear master
If ToRow <> 1 Then
ToSheet.Range(Cells(2, 1), Cells(ToRow, NumColumns)).ClearContents
End If
ToRow = 2
'------------------------------
'- main loop to open each file
'------------------------------
FromBook = Dir("*.xls")
While FromBook <> ""
If FromBook <> ToBook Then
Application.StatusBar = FromBook
Transfer_data
End If
FromBook = Dir
Wend
'-- close
MsgBox ("Done.")
Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic
End Sub
'---------------------------------------------------------------
'- CHANGE THIS CODE TO DO WHAT YOU WANT TO THE OPENED WORKBOOK
'----------------------------------------------------------------
Sub Transfer_data()
Workbooks.Open FileName:=FromBook
For Each FromSheet In Workbooks(FromBook).Worksheets
LastRow = FromSheet.Range("A65536").End(xlUp).Row
'------------------------------
'- copy paste to master sheet
FromSheet.Range(Cells(2, 1), Cells(LastRow, NumColumns)).Copy _
Destination:=ToSheet.Range("A" & ToRow)
'------------------------------
'- set next ToRow
ToRow = ToSheet.Range("A65536").End(xlUp).Row + 1
Next
Workbooks(FromBook).Close savechanges:=False
End Sub
'==== EOP ======================================
-----------------------
Regards BrianB
Most problems occur from starting at the wrong place.
Use a cup of coffee to make Windows run faster.
It is easy until you know how.
Processing text file from Oracle in Excel
