Sign in to follow this  
Followers 0
Ronster

Trying to run Excel Macro from PERSONAL.XLSB

13 posts in this topic

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



Hello there,

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

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

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

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - 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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

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

This code will run the macro. Thanks.

#include <Excel.au3>
winactivate("Microsoft Excel")
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  
Followers 0