VBScript that makes Excel the active window then runs a macro

CyberCorenez

New Member
Joined
Aug 25, 2020
Messages
2
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,097
Office Version
  1. 365
Platform
  1. Windows
Something like this.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,422
Messages
5,624,697
Members
416,042
Latest member
Oden

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
Top