0

I have a few macros in excel that work properly when I run them in excel manually, but when I set up a script in VBScript, Excel gives me an error message and the macros go unexecuted. Does anyone know why this is happening and what can be done to fix it? The command below "Windows("icecleared_oiloptions.xlsx").Activate" is where excel pointed out that needs debugging.

Sub Save_Close_TN()
'
' Save_Close_TN Macro
'
    Windows("icecleared_oiloptions.xlsx").Activate
    ActiveWorkbook.Save
    ActiveWindow.Close
End Sub

The below code is the call function for the VBS:

Dim objExcel, xlBook

Set objExcel = CreateObject("Excel.Application")
Set xlBook = objExcel.Workbooks.Open("Q:\GAS\Hub Pricing\ICE Oil Options\ICE 
Dat to Excel.xlsm", 0, False)
ObjExcel.Application.Visible = True

objExcel.Application.Run "Module3.Save_Close_TN"

objExcel.Application.Run "Module2.SaveAs1"

xlBook.Save
xlBook.Close True
set xlBook = Nothing
Community
  • 1
  • 1
TNieland
  • 27
  • 6
  • FWIW - Why do you even `Activate` the window if you are about to `Save` and `Close` the workbook anyway? Why not just `Workbooks("icecleared_oiloptions.xlsx").Save` and `Workbooks("icecleared_oiloptions.xlsx").Close`? – YowE3K Nov 30 '17 at 19:37
  • That code doesn't seem to open `icecleared_oiloptions.xlsx`. Or am I missing something? You can't activate a workbook that hasn't been opened. – YowE3K Nov 30 '17 at 19:41
  • That workbook is already open. Part of the first macro that DOES work was opening that workbook. All I'm wanting to do now is make it the active workbook and then save and close it. – TNieland Nov 30 '17 at 19:45
  • Is it open in the Excel instance you are using, or is it open in some other instance of Excel? – YowE3K Nov 30 '17 at 19:45
  • It could potentially be in another instance of excel. How do I check that? – TNieland Nov 30 '17 at 19:48
  • Umm - I would have to search (which you may as well do) but I think I have seen code where people use `GetObject` (instead of `CreateObject`) to connect to an existing Excel Application. You say "It could potentially be in another instance" - does that mean that it could also potentially be open in the new instance you are creating? (It was beginning to sound as if you weren't opening it until you said that.) – YowE3K Nov 30 '17 at 19:53
  • Does [this answer](https://stackoverflow.com/a/7773401/6535336) help? (I'm not familiar enough with using this sort of stuff to be sure - I'm used to being in Excel and using VBA there.) Or maybe [this question](https://stackoverflow.com/q/30970828/6535336)? – YowE3K Nov 30 '17 at 19:58
  • Workbooks running on a same instance will share their Ctrl+F dialog box. – MrDogme Nov 30 '17 at 21:22

0 Answers0