Jump to content
Sign in to follow this  
Ronster

Trying to run Excel Macro from PERSONAL.XLSB

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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 ?

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
JustSomeone

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
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.

Share this post


Link to post
Share on other sites
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")

Share this post


Link to post
Share on other sites
water

What is the value of @error after _ExcelBookAttach?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

 

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - 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
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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")

 

Share this post


Link to post
Share on other sites
Ronster

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  

×