Excel GetCell "Type mismatch"

[TheVig]

Error message:
"Error (RT) in Balance_Check: 0x80020005, Type mismatch."

The data in the cell I am trying to retrieve a value from is derived from the following formula:
=IF(A4="","",SUM(Data!$C$2:C4)-SUM($D$24))

Function Balance_Check

Code:

Copy      Help

str sfile="$desktop$\ExcelFile.xls"
str asheet="Sheet1"
Excel.Application app._create
Excel.Workbook book=app.Workbooks.Open(_s.expandpath(sfile))
ExcelSheet es.ws=book.Worksheets.Item(asheet)

str Value
es.GetCell(Value 6 4)

It's driving me nuts! I can grab values that are static values, and most that have values derived from formulas. It seems like the "IF" statement really screws things up.

Thanks,
Jimmy Vig

[TheVig]

Not true about the "IF" it works fine when I tested it...

Duh...its the reference to the other worksheet that is messing it up....geeze...now what to do?!?!?
Drats...that's not it either!! I took out all the references to other sheets...still errors.

Great, it's not consistent either....

Grr.

[Gintaras]

In ExcelSheet.GetCell

replace

s=r.Value

to

VARIANT v=r.value
out v.vt
s=v

What number is in QM output?

To edit System functions, uncheck readonly in System folder properties.

[TheVig]

10

[Gintaras]

It is VT_ERROR. The value is an error number. It cannot be converted to str using operator =.

[TheVig]

But the number shows up in Excel as the correct value?

[Gintaras]

Try to open the workbook in Excel and let the macro attach to it using ExcelSheet es.Init instead of _create etc.

[TheVig]

Well...that works.

So is there anyway to do it without Excel being open?

[Gintaras]

Don't know, try to change some properties of Excel objects.

For example, after

Excel.Application app._create

insert

app.Visible=TRUE

[TheVig]

You are right about that being an error code. When I choose TRUE for visibility, the cells are showing their error message, but why?

I'll work on it a bit more. Thank you for all you help!

[TheVig]

It's "NETWORKDAYS"

When I open Excel normally the function is there when I search for it.
When Quickmacros runs it visible....it is not there.

Weird!

[Gintaras]

I tested with simple formula, =SUM(A1;B1), and it works in both cases. Excel 2003.

[TheVig]

Yes, it works with simple formulas just fine.

What I am finding is that for some reason when QM initializes the app it does not load the Add-ins.

NETWORKDAYS is part of the Analysis ToolPak which doesn't seem to be loading through the QM Excel.Application app._create.
When I set visibility to TRUE, the program stays open after the error.

If I choose Tools->Add-ins then disable Analysis ToolPak, then re-enable Analysis ToolPak, and then go to edit one of the error'd cells, everything goes back to normal. If I do not disable and re-enable Analysis ToolPak...nothing works to fix it.

Is it possibly something in the way QM calls for the Excel application to run?

[Gintaras]

I am not familiar with Excel addins. Look in Excel object model, there is an AddIns interface. Look in Excel help.

Faund this in Excel help:

app.AddIns.Item("analysis toolpak").Installed=TRUE

It says "installs the Analysis Toolpak add-in".

[TheVig]

Thanks! That did the trick I think. I only tested it once real quick. I put up more later...

Thanks.
Jimmy Vig

[TheVig]

Nope...same problem. I was mistaken. Very strange, must just be one of those things I'll have to learn with in life. I'll test on other computers just to see if there is something strange with the way Excel is installed on this computer.

Thanks again for all your help.
Jimmy Vig

[Gintaras]

In next QM ExcelSheet will have function LoadAddins. Excel does not load addins when started by macro.

Now you can test it.

Member function ExcelSheet.LoadAddins_Test

Code:

Copy      Help

function [flags] ;;flags: 1 run auto open macros

;Loads installed Excel addins (.xla, .xll).

;REMARKS
;When Excel started by Init, it does not load addins. If need, call this function after Init.

;Added in: QM 2.3.3.


Excel.Application a=ws.Application
Excel.AddIn x
foreach x a.AddIns
,_s=""
,if(!x.Installed) continue
,_s=x.FullName
,int failed=0
,if _s.endi(".xll")
,,failed=!a.RegisterXLL(_s)
,else
,,Excel.Workbook b=a.Workbooks.Open(_s) ;;xla
,,if(flags&1) b.RunAutoMacros(Excel.xlAutoOpen); err
,
,err+ failed=1
,if(failed) out "Failed to load %s" _s

;Excel.COMAddIns=a.COMAddIns ;;should load too?

[lordvellos]

Dear all,

I am getting the LoadAddin issue, but when I try to use function it gives me the following error

Failed to load H:\Applications\CM\Add-ins\CM.xlam
Failed to load P:\Applications\Excel\Add-ins\br.xlam

All th others addins seem to load just fine

Do you think it has anything to do with the fact that the addins are installed in a network? Is there a workaround to it?

Thank you

[lordvellos]

Just to add, these are the addins installed

[Gintaras]

Yes, it can be because the network folders probably are not mapped to H: and P: in QM process, because QM process runs as admin. There are separate mappings for standard user and admin.

Macro Macro272

Code:

Copy      Help

if !FileExists("H:" 2)
,Wsh.WshNetwork n._create
,VARIANT v=1
,n.MapNetworkDrive("H:" "\\xxx\yyy\zzz" v)
,n.MapNetworkDrive("P:" "\\xxx\yyy\zzz2" v)

Or run your macro in separate process as User. Look in Properties.