Guys and Gals,
I have written(hacked) some VB code to open some files, copy the data from a particular sheet, and paste this data on a new worksheet in a new workbook.
This part works well, however the files that I specify to be opened don't open up in the order that I need them to. Here is an example of the filenames that I open for data analysis:
Data - 20030512-20030512 - 1
Data - 20030511-20030511 - 1
Data - 20030510-20030510 - 1
When I select these files, in the file window, the order in which these files open tends to vary. I need for my macro to open these files in order and process the data from the earliest date to the latest (current date).
What would be the easiest method to this? Here is an example of the code I currently use:
Sub CombineMultipleFiles2One()
'
Dim varFilenames As Variant
Dim strActiveBook As String
Dim strSourceDataFile As String
Dim strTest As String
Dim wSht As Worksheet
Dim allwShts As Sheets
'
intResponse = MsgBox("This script will combine all of the sector total data from all selected files to a single worksheet in a new workbook. Continue?", vbOKCancel, "Combine Worksheets to One Sheet")
If intResponse = vbOK Then
Workbooks.Add
strActiveBook = ActiveWorkbook.Name
' Create array of filenames; the True is for multi-select
On Error GoTo exitsub
varFilenames = Application.GetOpenFilename(, , , , True)
counter = 1
' ubound determines how many items in the array
On Error GoTo quit
' Workbooks.Add
Application.ScreenUpdating = False
While counter <= UBound(varFilenames)
'Opens the selected files
Workbooks.Open varFilenames(counter)
strSourceDataFile = ActiveWorkbook.Name
Sheets("sector totals").Select
Range("A2:U307").Select
Selection.Copy
'Find end of usedrange in destination file
Workbooks(strActiveBook).Activate
Range("A1").Select
ActiveSheet.UsedRange.Select
lRows = Selection.Rows.Count
ActiveCell.Offset(lRows, 0).Select
' Copy & Paste All including Formatting
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Workbooks(strSourceDataFile).Activate
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
' displays file name in a message box
MsgBox varFilenames(counter) & " Has Been Processed", vbOKOnly + vbInformation, "File Processed"
'increment counter
counter = counter + 1
Wend
quit:
If Err <> 0 Then
MsgBox "An Error Occurred Trying to open the File. Please close any open Excel files and try again", vbOKOnly + vbExclamation, "File Open Error"
On Local Error GoTo 0
End If
End If
exitsub:
On Local Error GoTo 0
Application.ScreenUpdating = True
End Sub
I have written(hacked) some VB code to open some files, copy the data from a particular sheet, and paste this data on a new worksheet in a new workbook.
This part works well, however the files that I specify to be opened don't open up in the order that I need them to. Here is an example of the filenames that I open for data analysis:
Data - 20030512-20030512 - 1
Data - 20030511-20030511 - 1
Data - 20030510-20030510 - 1
When I select these files, in the file window, the order in which these files open tends to vary. I need for my macro to open these files in order and process the data from the earliest date to the latest (current date).
What would be the easiest method to this? Here is an example of the code I currently use:
Sub CombineMultipleFiles2One()
'
Dim varFilenames As Variant
Dim strActiveBook As String
Dim strSourceDataFile As String
Dim strTest As String
Dim wSht As Worksheet
Dim allwShts As Sheets
'
intResponse = MsgBox("This script will combine all of the sector total data from all selected files to a single worksheet in a new workbook. Continue?", vbOKCancel, "Combine Worksheets to One Sheet")
If intResponse = vbOK Then
Workbooks.Add
strActiveBook = ActiveWorkbook.Name
' Create array of filenames; the True is for multi-select
On Error GoTo exitsub
varFilenames = Application.GetOpenFilename(, , , , True)
counter = 1
' ubound determines how many items in the array
On Error GoTo quit
' Workbooks.Add
Application.ScreenUpdating = False
While counter <= UBound(varFilenames)
'Opens the selected files
Workbooks.Open varFilenames(counter)
strSourceDataFile = ActiveWorkbook.Name
Sheets("sector totals").Select
Range("A2:U307").Select
Selection.Copy
'Find end of usedrange in destination file
Workbooks(strActiveBook).Activate
Range("A1").Select
ActiveSheet.UsedRange.Select
lRows = Selection.Rows.Count
ActiveCell.Offset(lRows, 0).Select
' Copy & Paste All including Formatting
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Workbooks(strSourceDataFile).Activate
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
' displays file name in a message box
MsgBox varFilenames(counter) & " Has Been Processed", vbOKOnly + vbInformation, "File Processed"
'increment counter
counter = counter + 1
Wend
quit:
If Err <> 0 Then
MsgBox "An Error Occurred Trying to open the File. Please close any open Excel files and try again", vbOKOnly + vbExclamation, "File Open Error"
On Local Error GoTo 0
End If
End If
exitsub:
On Local Error GoTo 0
Application.ScreenUpdating = True
End Sub
