Trying to run Excel Macro from PERSONAL.XLSB

[Ronster]

I have an Excel file already open (visible) and am trying to run a macro from my PERSONAL.XLSB file (not visible). The following code seems to work but it pops up an Excel window, "File In Use","Personal.XLSB is locked for editing by Standard Configuration." It only shows three buttons, "Read Only", "Notify", and "Cancel."

#include <Excel.au3>
$oExcel = ObjCreate("Excel.Application")
$oExcel.Run("'C:USERSsmithaaappdataroamingmicrosoftexcelxlstartPERSONAL.XLSB'!Convert_XLS_To_XLSX")

I've also tried running this with this command but it always fails.
$oExcel.Run("Convert_XLS_To_XLSX")

How do I run this macro?

[JustSomeone]

Hello there,

What version of office you have, also did i understand correctly that you want to convert an xls file to xlsx file ?

[Ronster]

I have Office 2010. The macro "convert an xls file to xlsx" works great in Excel. I press a button in the Qiuck Access Toolbar, it unloads the XLS Excel file and reloads it as XLSX. Do you want the code?

[Bert]

I see it - you need to attach to the existing spreadsheet, not create a new instance. Use _ExcelBookAttach instead of $oExcel = ObjCreate("Excel.Application")

[JustSomeone]

You need to run the macro from whitin excel, or you need to convert an xls file to xlsx ?

[Ronster]

I'm just trying to run "any" macro from PERSONAL.XLSB, "Hello World", MsgBox "Hello World", would be OK. Currently no macros in PERSONAL.XLSB will run from the Autoit code above.

[Ronster]

I added the _ExcelBookAttach but I am getting a Syntax Error on the Run command. Not sure what I am doing wrong.

#include <Excel.au3>

_ExcelBookAttach ("MyExcelFile.XLSX", "FileName")

Local $oExcel.Run("'C:USERSsmithaaappdataroamingmicrosoftexcelxlstartPERSONAL.XLSB'!Convert_XLS_To_XLSX")

[water]

What is the value of @error after _ExcelBookAttach?

[Ronster]

I'm not that famiiar with Autoit code erros but here is the results:

>"Q:AUTOITV3.338AutoIt3SciTEAutoIt3WrapperAutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "C:UserssmithaaDocumentsAutoitCMSCMS_Query6.au3" /UserParams   
+>14:12:58 Starting AutoIt3Wrapper v.2.1.0.33    Environment(Language:0409  Keyboard:00000409  OS:WIN_7/Service Pack 1  CPU:X64 OS:X64)
>Running AU3Check (1.54.22.0)  from:Q:AUTOITV3.338AutoIt3
+>14:12:58 AU3Check ended.rc:0
>Running:(3.3.8.1):Q:AUTOITV3.338AutoIt3autoit3_x64.exe "C:UserssmithaaDocumentsAutoitCMSCMS_Query6.au3"   
--> Press Ctrl+Alt+F5 to Restart or Ctrl+Break to Stop
C:UserssmithaaDocumentsAutoitCMSCMS_Query6.au3 (3) : ==> The requested action with this object has failed.:
$oExcel.Run("'C:USERSsmithaaappdataroamingmicrosoftexcelxlstartPERSONAL.XLSB!'Convert_XLS_To_XLSX")
$oExcel.Run("'C:USERSsmithaaappdataroamingmicrosoftexcelxlstartPERSONAL.XLSB!'Convert_XLS_To_XLSX")^ ERROR
->14:12:59 AutoIT3.exe ended.rc:1
>Exit code: 1    Time: 1.565
 

[water]

I suggest to add a COM error handler to your script. This will return much better error information. Check ObjEvent in the help file to see how to implement a COM error handler.

[Bert]

you do not need to use run for you are already attached to the workbook. You simply need to send a keystroke to the workbook. Read here:

http://office.microsoft.com/en-us/excel-help/run-a-macro-HP010342865.aspx

[Ronster]

^{The spreadsheet is already open. When I manually press CTRL-q the macro runs but the following code will run without errors and writes to cell A1 but does not run the macro.}

 ^{#include <Excel.au3>
Local $oExcel = _ExcelBookAttach ("MyExcelFile.XLSM", "FileName")
_ExcelWriteCell($oExcel, "Write to Cell A1", 1, 1) ;Write to the Cell
Send("^q")}

 

[Ronster]

This code will run the macro. Thanks.

#include <Excel.au3>
winactivate("Microsoft Excel")
Send("^q")