Excel Trouble

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
fightcancer
Macro Veteran
Posts: 239
Joined: Fri Apr 15, 2005 8:32 am

Excel Trouble

Post by fightcancer » Fri Apr 26, 2019 2:32 pm

This code:

Code: Select all

XLGet>E:\test.xlsx,xlBook
XLGetCell>xlBook,MSsheet,2,1,FirstName
Yields this error.

"Access violation at address 004DCA32 in module 'msched.exe'. Read of address 00000000."

How can I get XLGetCell to work? Thanks!

fightcancer
Macro Veteran
Posts: 239
Joined: Fri Apr 15, 2005 8:32 am

Re: Excel Trouble

Post by fightcancer » Fri Apr 26, 2019 2:44 pm

Weird. This code works as intended.

Code: Select all

XLOpen>E:\test.xlsx,1,xlBook
//XLGet>E:\test.xlsx,xlBook
XLGetCell>xlBook,MSsheet,2,1,FirstName
Maybe I'm using XLGet wrong.

User avatar
Grovkillen
Automation Wizard
Posts: 1009
Joined: Fri Aug 10, 2012 2:38 pm
Location: Bräcke, Sweden
Contact:

Re: Excel Trouble

Post by Grovkillen » Fri Apr 26, 2019 4:54 pm

XLGet> isn't xlsx compatible?
Let>ME=%Script%

Running: 15.0.24
version history

fightcancer
Macro Veteran
Posts: 239
Joined: Fri Apr 15, 2005 8:32 am

Re: Excel Trouble

Post by fightcancer » Fri Apr 26, 2019 6:53 pm

Not sure if that's a statement or a question, but if it's true, then I would hope for an update to the help file.

User avatar
Grovkillen
Automation Wizard
Posts: 1009
Joined: Fri Aug 10, 2012 2:38 pm
Location: Bräcke, Sweden
Contact:

Re: Excel Trouble

Post by Grovkillen » Sat Apr 27, 2019 3:42 am

Yeah it was a question. :P
Let>ME=%Script%

Running: 15.0.24
version history

hagchr
Automation Wizard
Posts: 327
Joined: Mon Jul 05, 2010 7:53 am
Location: Stockholm, Sweden

Re: Excel Trouble

Post by hagchr » Sat Apr 27, 2019 8:12 am

It seems to work if the file was openend using XLOpen>, so that may be the requirement. This works for me:

Code: Select all

XLOpen>C:\Users\Christer\Desktop\CHTestGET.xlsx,1,xlBook0
XLGet>C:\Users\Christer\Desktop\CHTestGET.xlsx,xlBook
XLGetCell>xlBook,Sheet1,1,1,strCell
MDL>strCell

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

Re: Excel Trouble

Post by Dorian (MJT support) » Mon Apr 29, 2019 2:58 pm

XLGet attaches to an existing running instance of Excel. In other words, if that Excel file has been opened by anything other that XLOpen.

I was able to replicate this error, so have a good idea of what may be causing it. This is my test code :

Code: Select all

//Working code
let>filename=D:\mjt\files\excel\compare.xlsx
XLGet>filename,xlBook
XLAddSheet>xlBook,Customers
XLSetCell>xlBook,Customers,1,1,CustID,xlRes
I was able to force the following errors.

Access Violation : caused when the file specified at D:\mjt\files\excel\compare.xlsx was not open.

Invalid Variant Operation : caused when the file had been opened manually, but the file path or name was incorrect. (e.g. let>filename=D:\mjt\files\excel\ZZZZZZZcompare.xlsx)

So my guess is that this code is being run without the Excel file being already open. It could either be that nothing is opening it, or that if something else IS opening it, we're not waiting for enough time before running XLGet?
Yes, we have a Custom Scripting Service. Message me or go here

fightcancer
Macro Veteran
Posts: 239
Joined: Fri Apr 15, 2005 8:32 am

Re: Excel Trouble

Post by fightcancer » Tue Apr 30, 2019 6:02 pm

I modified the code a bit to demonstrate that the window is in fact open and has been open for at least 5 seconds.

Code: Select all

IfWindowOpen>test.xlsx - Excel
  Let>FileName=E:\test.xlsx
  Wait>5
  XLGet>FileName,xlBookk
  XLGetCell>xlBookk,MSsheet,2,1,FirstNameP
EndIf
The error: "Invalid Variant Operation"

I'm unable to get XLGetCell to work with XLGet.

XLOpen works just fine though....

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

Re: Excel Trouble

Post by Dorian (MJT support) » Thu May 02, 2019 10:36 am

The only ways I can force this error (and the only causes I have ever seen over at the Support Desk) are :

1) If the path and filename in XLGet is incorrect. But if XLOpen is working using that same path, it can't be that.

or,

2)If the XL Book Handles in XLGet and XLGetCell don't match - of course I can see on your code they do match, so it's not that.

I think you *may* be using version 14.4.04. Can you try downloading the trial and see if the issue exists in the latest version?
Yes, we have a Custom Scripting Service. Message me or go here

User avatar
Marcus Tettmar
Site Admin
Posts: 7378
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Re: Excel Trouble

Post by Marcus Tettmar » Sun May 05, 2019 10:11 am

Not sure what is causing your error, but if you want to send your excel file and code to reproduce the issue to support (support AT mjtnet.com) we can try it here and see if we can get any debug info to work out what's happening.
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

ibatey
Newbie
Posts: 10
Joined: Thu Dec 17, 2015 9:24 pm

Re: Excel Trouble

Post by ibatey » Fri Feb 07, 2020 6:55 pm

Did you ever get to the bottom of this.

I have a script that ussed to work fine, but is now giving an
'Invalid Variant Operation Error'
on anXLOpen line

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

Re: Excel Trouble

Post by Dorian (MJT support) » Sat Feb 08, 2020 7:57 pm

ibatey wrote:
Fri Feb 07, 2020 6:55 pm
Did you ever get to the bottom of this.

I have a script that ussed to work fine, but is now giving an
'Invalid Variant Operation Error'
on anXLOpen line
Are you by any chance using an old version of Macro Scheduler, and have the filepath wrong?

This error was caused in versions prior to Version 14.4 04 when an incorrect filepath was used.

Any occurrences of this happening in versions after that fix seem to have been an Office issue and were resolved by reinstalling Office.
Yes, we have a Custom Scripting Service. Message me or go here

Ranger762
Newbie
Posts: 7
Joined: Thu Mar 26, 2020 9:55 am

Re: Excel Trouble

Post by Ranger762 » Thu Mar 26, 2020 4:54 pm

I can confirm that the only time I had this issue was when I was working with Macro Scheduler on an old computer at work that had with an older version of Office. We updated it and then it worked fine!

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