VBScript that makes Excel the active window then runs a macro

[CyberCorenez]

So I have been trying to make a VBScript that opens excel. then there is a macro that selects a specific cell(s) to update the date for a monthly report. This is where the macro breaks because the VBScript does not make Excel the active window (this can be told because the excel icon on the task bar will blink). Does anyone have any way around this?

Here is my code for the VBScript followed by my VBA code for the Macro. (My Excel file has a sheet for the date selection and then 3 sheets for the data tables it fills from an add in).


VBScript:

Dim args, objExcel

Set args = wScript.Arguments
Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Open args(0)
objExcel.Visible = True

objExcel.Run "MonthlyReport"
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close(0)
objExcel.Quit

VBA Code:
Sub MonthlyReport()
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False

'This code calculates all of the data from the previous month and PDF's all of it for printing and viewing.

'This changes the Date from the Date Selection Sheet
Sheets("Data Selection").Select

Range("G5").Select
MsgBox ("test")
ActiveCell.FormulaR1C1 = Format(DateAdd("m", -1, Date), "mm/01/yyyy")
Range("G6").Select
ActiveCell.FormulaR1C1 = Format(DateAdd("m", -1, Date), "mm/31/yyyy")

'Forces the Calculations
Application.Calculation = xlCalculationAutomatic

'Selects the Flow sheet to look at the data and PDF

Sheets("Flow").Select

'Coding to PDF the Flow sheet
Dim ID1 As String
ID1 = Format(DateAdd("m", -1, Date), "yyyy_mm") + ("Flow")

ChDir "C:\Users\example\PDF\Flow_Data"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
"C:\Users\example\PDF\ " + ID1 + ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

'Selects the Equipment Runtime sheet to look at the data and PDF
Sheets("Equipment Runtime").Select
Dim ID2 As String
ID2 = Format(DateAdd("m", -1, Date), "yyyy_mm") + ("Equipment Runtime")

ChDir "C:\Users\example\PDF\Equipment_Runtime_Data"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
"C:\Users\example\PDF\ " + ID2 + ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

'Selects the Digester sheet to look at the data and PDF
Sheets("Digester").Select
Dim ID3 As String
ID3 = Format(DateAdd("m", -1, Date), "yyyy_mm") + ("Digester")

ChDir "C:\Users\example\PDF\Digester_Data "
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
"C:\Users\example\PDF\ " + ID3 + ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

Application.ScreenUpdating = True

End Sub

 

[lrobbo314]

Something like this.

Option Explicit

Private Declare Function FindWindow _
    Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, _
    ByVal lpWindowName As String) _
    As Long
   
Private Declare Function APISetFocus Lib "user32" _
Alias "SetFocus" _
(ByVal hWnd As Long) _
As Long

Sub ActivateWindow()
AppActivate "Book1 - Excel"
' Call your code here
End Sub