Page 1 of 1 1
Topic Options
#212329 - 2017-03-13 03:05 PM Open Excel, run macro, save file and close Excel
Mart Moderator Offline
KiX Supporter
*****

Registered: 2002-03-27
Posts: 4672
Loc: The Netherlands
We have a small requirement to open an Excel file, run a macro, save the workbook and close Excel.

In VBS this works:
 Code:
Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Run "'D:\ExcelFile.xlsm'!Sheet1.OpenAnExistingWorkbookSet"
objExcel.DisplayAlerts = False
objExcel.Application.Save
objExcel.Application.Quit
Set objExcel = Nothing


but I seem to be unable to translate it to kix. Should not be that difficult I think but it does not execute the macro. The VBS code works but the KIX code does not work. I made some changes to the code but no success yet. The settings in Excel are unchanged between the VBS and kix code. Somehow I'm missing something. The fact that COM is not something I find easy and fully understand does not help also.

 Code:
$objExcel = CreateObject("Excel.Application")
$objExcel.Application.Run "'D:\ExcelFile.xlsm'!Sheet1.OpenAnExistingWorkbookSet"
$objExcel.DisplayAlerts = False
$objExcel.Application.Save
$objExcel.Application.Quit
$objExcel = ""
_________________________
Mart

- Chuck Norris once sold ebay to ebay on ebay.

Top
#212330 - 2017-03-14 03:39 AM Re: Open Excel, run macro, save file and close Excel [Re: Mart]
Allen Administrator Online   shocked
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4545
Loc: USA
These two lines are really the only ones that could be questionable.

$objExcel.Application.Run "'D:\ExcelFile.xlsm'!Sheet1.OpenAnExistingWorkbookSet"
$objExcel.DisplayAlerts = False

Maybe
$objExcel.Application.Run("'D:\ExcelFile.xlsm'!Sheet1.OpenAnExistingWorkbookSet")
$objExcel.DisplayAlerts = not 1

Top
#212331 - 2017-03-14 08:47 AM Re: Open Excel, run macro, save file and close Excel [Re: Allen]
Mart Moderator Offline
KiX Supporter
*****

Registered: 2002-03-27
Posts: 4672
Loc: The Netherlands
Yesterday evening I found a solution that works but I need to verify this with the owner of the Excel file to be sure that the macro runs. More info will follow later today.

Edited by Mart (2017-03-14 03:51 PM)
Edit Reason: typo
_________________________
Mart

- Chuck Norris once sold ebay to ebay on ebay.

Top
#212332 - 2017-03-14 03:50 PM Re: Open Excel, run macro, save file and close Excel [Re: Mart]
Mart Moderator Offline
KiX Supporter
*****

Registered: 2002-03-27
Posts: 4672
Loc: The Netherlands
Working code on Windows 7 and Excel (Office) 2016.

 Code:
;Set Break state to on
Break On

;Initiate Excel object
$objExcel = CreateObject("Excel.Application")

;Set Excel object to visible
$objExcel.Visible = True

;Open workbook
$objWorkbook = $objExcel.Workbooks.Open("D:\MyExcelFile.xlsm")

;Run macro
$objExcel.Run("MacroNameGoesHere")

;Save workbook
$objWorkbook.Save

$objWorkbook.Close

;Close Excel
$objExcel.Quit
_________________________
Mart

- Chuck Norris once sold ebay to ebay on ebay.

Top
#212340 - 2017-03-15 09:52 AM Re: Open Excel, run macro, save file and close Excel [Re: Mart]
Arend_ Moderator Offline
MM club member
*****

Registered: 2005-01-17
Posts: 1894
Loc: Hilversum, The Netherlands
The fact that this works:
 Code:
;Set Excel object to visible
$objExcel.Visible = True

Is just weird, "True" is not a boolean value in Kix.
Seemingly the object accepts anything other then a boolean False value as True.

Top
Page 1 of 1 1


Moderator:  Shawn, ShaneEP, Ruud van Velsen, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Mart 
Hop to:
Shout Box

Who's Online
2 registered (morganw, mole) and 414 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.085 seconds in which 0.043 seconds were spent on a total of 13 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org