Invalid Variant Operation

[Tourless]

Hi Folks.

I have a macro that points and clicks it's way through a program, running a report and saving it as an xls file. Using XLOpen I was always able to open the xls and extract some values to variables. Suddenly, and without warning, my script now fails on the XLOpen line and throws an 'Invalid Varient Operation' error and I'm not sure why or what to do about it. Here's the section that is giving me problems, any help is greatly appreciated. -thank you.

Code: Select all

Goto>PrepData

Label>PrepData
XLOpen>C:\Users\Mentor\Documents\TunnelReport.xls,0,xlTR
Wait>.75
XLGetCell>xlTR,Sheet1,11,8,t2Transfers
XLGetCell>xlTR,Sheet1,12,8,t1Transfers
XLGetCell>xlTR,Sheet1,11,16,t2RunTime
XLGetCell>xlTR,Sheet1,12,16,t1RunTime
XLGetCell>xlTR,Sheet1,14,7,totalTransfers
XLGetCell>xlTR,Sheet1,14,16,totalRunTime
Wait>.5
XLQuit>xlTR
Wait>.5
Goto>t1Calculations

[Dorian (MJT support)]

99% of the time that's caused when the handle created with XLOpen does not match the handle used in the subsequent Xl commands (in this case XLGetCell).

But if you're sure this is caused by the XLOpen line and not the subsequent XL commands, and also that it used to work but now it doesn't, the solution may lay elsewhere...

... we had one report from one user three years ago who was experiencing the same issue all of a sudden. Her solution was to ensure Windows was up to date and to reinstall Office. Once Office was reinstalled the issue went away.

If you're not completely sure it's XLOpen causing the problem as opposed to XLGetCell, then maybe try slowing things down to be sure Excel is open and ready before moving on the the XLOpen commands.

Note : Prior to Version 14.4 04 this error would occur if the file specified in XLOpen did not exist.

[Tourless]

That's good info, thank you.

Looks like all that was needed in this instance was a quick repair of the office 365 installation.

[Dorian (MJT support)]

Tremendous! Happy to have helped, and I appreciate the feedback.