Hi,
I hope I can describe my problem clear enough, so please bear with me !!
I have a parent file that I would like to use to produce a combined report that is made up of idential queries run on 7 child files. Ideally I would like to hit a button on the parent and it to open and run the right macro in all 7 children and amalgamate the reports in one sheet.
I've been able to create a macro to open the first child file and run the macro contained within it, however each macro is dependant on start/end dates and as the parent opens each file in turn it asks for the start/end dates for each of the 7 files.
Code for the Parent file
Relavent section of code for the Child file
I wondered if I could set the AbsenceStart/End in the parent file and have it picked up within the macro for Child File One ... however it still pops up.
Is there anyway I can do this ?
I hope I can describe my problem clear enough, so please bear with me !!
I have a parent file that I would like to use to produce a combined report that is made up of idential queries run on 7 child files. Ideally I would like to hit a button on the parent and it to open and run the right macro in all 7 children and amalgamate the reports in one sheet.
I've been able to create a macro to open the first child file and run the macro contained within it, however each macro is dependant on start/end dates and as the parent opens each file in turn it asks for the start/end dates for each of the 7 files.
Code for the Parent file
Code:
Sub RunAllLoaders()
'
Dim msg As Variant, ans As Variant
Dim AbsenceStart As Date, AbsenceEnd As Date
Dim LastRow As Integer
CheckQuestion:
msg = "Have you run the All Absence Checker?" & vbCrLf
ans = MsgBox(msg, vbInformation + vbYesNo, "Save Uploader")
Select Case ans
Case vbYes
GoTo RunLoaders
Case vbNo
MsgBox "Absence Checker must be run before uploader created to prevent errors"
End
End Select
RunLoaders:
On Error Resume Next
Excel.Application.EnableEvents = False
'start of data validation script. continued in new sub at end of sheet (not included for this example)
GetValidDates FromDate:=AbsenceStart, _
ToDate:=AbsenceEnd, _
MinDate:=DateSerial(2007, 1, 1), _
MaxDate:=DateSerial(2007, 12, 31)
Workbooks.Open Filename:= _
"P:\Absence Project\Child File One.xls"
Application.Run "'Child File One'!AbsenceLoader"
LastRow = Range("A65536").End(xlUp).row
Range("2:" & LastRow).Select
Selection.Copy
ActiveWindow.ActivateNext
Sheets("KEM Uploader").Select
LastRow = Range("A65536").End(xlUp).row + 1
Range("A" & LastRow).Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Application.CutCopyMode = False
ActiveWorkbook.Close
MsgBox ("All updated")
Excel.Application.EnableEvents = True
End Sub
Relavent section of code for the Child file
Code:
If AbsenceStart <> 0 And AbsenceEnd <> 0 Then GoTo CreateFile
'start of data validation script. continued in new sub at end of sheet
GetValidDates FromDate:=AbsenceStart, _
ToDate:=AbsenceEnd, _
MinDate:=DateSerial(2007, 1, 1), _
MaxDate:=DateSerial(2007, 12, 31)
CreateFile:
'Searches the roster file for start and end absence month dates
Sheets("Roster").Columns("E").Find(AbsenceStart).Select 'finds the start date directly
emptyrow = 4 'Value for finding empty row in sicktemp file
I wondered if I could set the AbsenceStart/End in the parent file and have it picked up within the macro for Child File One ... however it still pops up.
Is there anyway I can do this ?