My Excel Functions are not working

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
Rick0825
Junior Coder
Posts: 28
Joined: Sun Mar 05, 2023 5:30 pm

My Excel Functions are not working

Post by Rick0825 » Sun Mar 05, 2023 5:42 pm

VERY new here. My first post after just a few macros. Im trying to use ANY of the Excel Functions but keep getting the "Access Violation error". I have a program running that spits out an excel file using the default Book1 name. I need Scheduler to just select and copy some cells and then close out without saving. Im using Excel 365. Here is my code:

Code: Select all

SetFocus>Book1 - Excel
XLOpen>tmp0,1,xlBook
Wait>3.0
//Maximize Window
WindowAction>1,Book1 - Excel
Wait>1.0

XLSelectRange>Book1,Sheet1,B3:B16
Wait>1.0

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1378
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: My Excel Functions are not working

Post by Dorian (MJT support) » Sun Mar 05, 2023 6:06 pm

It's because the handle you created in XLOpen (xlBook) and the one you're using in XLSelectRange (Book1) don't match. So you're using a handle that has not been created yet.

You also need to use a path and file name in XLOpen (unless tmp0 is already a variable containing that).

XLOpen : "Opens an existing Excel workbook with the given filename. Set Visible to 1 to make the workbook visible, or zero to hide it. Returns a handle in XLBookHandle which should be used to reference the workbook in other XL commands."

Example :

Code: Select all

XLOpen>%USERDOCUMENTS_DIR%\mybook.xls,1,xlBook
XLAddSheet>xlBook,Customers
XLSetCell>xlBook,Customers,1,1,CustID,xlRes
Thus your code should look more like this :

Code: Select all

XLOpen>c:\path\to\myfile.xlsx,1,xlBook
Wait>3.0
SetFocus>Book1 - Excel
//Maximize Window
WindowAction>1,Book1 - Excel
Wait>1.0

XLSelectRange>xlBook,Sheet1,B3:B16
Wait>1.0
Yes, we have a Custom Scripting Service. Message me or go here

Rick0825
Junior Coder
Posts: 28
Joined: Sun Mar 05, 2023 5:30 pm

Re: My Excel Functions are not working

Post by Rick0825 » Sun Mar 05, 2023 7:05 pm

Still getting the same error. I took out the "XLOpen" line because the file is already open. Using the debug tool the error happens on the XLSelectRange line. I also tried saving the Book1 file to my desktop and using the XLOpen with full file location but it made no difference. I know I have to be doing something wrong but just not sure what? Thank you so much for the quick response.

Code: Select all

SetFocus>Book1 - Excel
//Maximize Window
WindowAction>1,Book1 - Excel
Wait>1.0

XLSelectRange>xlBook,Sheet1,B3:B16
Wait>1.0

Rick0825
Junior Coder
Posts: 28
Joined: Sun Mar 05, 2023 5:30 pm

Re: My Excel Functions are not working

Post by Rick0825 » Sun Mar 05, 2023 7:36 pm

Ok I got it to work almost. I now understand what you mean when you say: "Open returns a handle in XLBookHandle which should be used to reference the workbook in other XL commands." But what if the excel file has been opened already by another program and it has NOT been saved to my computer. Does the handle have to be made or known before Scheduler can modify it?

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1378
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: My Excel Functions are not working

Post by Dorian (MJT support) » Sun Mar 05, 2023 8:29 pm

For Excel instances that were not opened by XLOpen, you can assign the handle with XLGet.

"Attaches to an existing running instance of Excel. If Filename is empty the first running instance of Excel will be used. Otherwise it will look for a running instance with the specified workbook open."

Example:

Code: Select all

XLGet>%USERDOCUMENTS_DIR%\mybook.xls,xlBook
XLAddSheet>xlBook,Customers
XLSetCell>xlBook,Customers,1,1,CustID,xlRes
Yes, we have a Custom Scripting Service. Message me or go here

Rick0825
Junior Coder
Posts: 28
Joined: Sun Mar 05, 2023 5:30 pm

Re: My Excel Functions are not working

Post by Rick0825 » Sun Mar 05, 2023 9:57 pm

XLGet worked. My final code ended up being:

Code: Select all

XLGet>Book1,xlBook
Wait>1.0
XLSelectRange>xlBook,Sheet1,B3:B16
Thanks for your patients. :D

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1378
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: My Excel Functions are not working

Post by Dorian (MJT support) » Sun Mar 05, 2023 10:20 pm

Always happy to help. :D
Yes, we have a Custom Scripting Service. Message me or go here

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts