Combining VBA macros across files

Nyanko

Active Member
Joined
Sep 1, 2005
Messages
437
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
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 ?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Could someone advise if this is even possible ?

I hope I've described my problem clear enough - if further clarification is needed, please let me know. I would appriciate ANY comments regarding my query ... even if it's WTF ;)
 
Upvote 0
48 views but no suggestions ? :(

Can someone even advise if this is possible then I can continue my research ?
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top