Jump to content
Sign in to follow this  
Ronster

Trying to run Excel Macro from PERSONAL.XLSB

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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")

Share this post


Link to post
Share on other sites

What is the value of @error after _ExcelBookAttach?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-08-19 - Version 1.4.13.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Taks Scheduler (NEW 2019-10-09 - Version 0.9.0.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-08-19 - Version 1.4.13.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Taks Scheduler (NEW 2019-10-09 - Version 0.9.0.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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")

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×
×
  • Create New...