1

I'm trying to do 'Refresh All' (as shown in the image below) using a Macro:

enter image description here

Following is the macro (which is written in Module1):

Sub Workbook_RefreshAll()
  ActiveWorkbook.RefreshAll
End Sub

And then I'm writing a VBScript as follows:

'Code should be placed in a .vbs file
Set objExcel = CreateObject("Excel.Application")
Set book = objExcel.Workbooks.Open("excel.xlsm", , True)

WScript.Echo "Executing RefreshAll"
objExcel.Application.Run "'excel.xlsm'!Module1.Workbook_RefreshAll"

WScript.Echo "Executing Mailing"
objExcel.Application.Run "'excel.xlsm'!ThisWorkbook.Mail"

objExcel.DisplayAlerts = False
objExcel.Application.Quit
Set objExcel = Nothing

But it is not refreshing the workbook.

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Arman
  • 827
  • 3
  • 14
  • 28

1 Answers1

0

You have some mismatching of your objects and parameters of the run.

Instead of objExcel.Application.Run "'excel.xlsm'!Module1.Workbook_RefreshAll" you should be using your book object that you created and in the run parameter you do not need to call the workbook name again. I.E. book.Application.Run "Module1.Workbook_RefreshAll"

Also you may want to look in to how you are creating that book object. Presumable if the vbs file and xlsm file are in the folder that should work. But it doesn't always.

You may want to consider adding this to the beginning of your script

 Set fso = CreateObject("Scripting.FileSystemObject")
 curDir = fso.GetAbsolutePathName(".")

Then modify your script to

 Set book = objExcel.Workbooks.Open( curDir & "\excel.xlsm" )
Sean W.
  • 863
  • 5
  • 14
  • But even when I run the macro for refreshing (i.e., in Module 1), it still does not refresh. – Arman Apr 10 '17 at 23:44
  • It may have to do with background refresh setting on the connections. Try either disabling it manually or modify your refresh code like this http://stackoverflow.com/a/18836346/3658485. See if that helps – Sean W. Apr 11 '17 at 12:38