Need a PB9 2003/2007 Excel working sample.

[Jim Lyons]

Does anyone have a working Excel 2003 or 2007 sample app using an include file generated by PowerBASIC Com Browser 2.00.005 that comes with PB9 that they would like to share. I have read and tried every combination of options in an attempt to get the 2 Excel sample apps (ExcelAp1 and ExcelAp2) to compile and run. I can get different parts to work changing the COM Browser settings but continually get stumped, mostly with syntax I think. What are the best settings to use in the COM Browser? I believe that I have a fairly good understanding of COM but don’t seem to be able to get my old PB8 Excel code to work using PB9 syntax. A current working sample is like a picture “worth a thousand words”.

Thanks in advance for any help.
Jim

[Richard Angell]

Try setting the Tools\Options to Generate Dispatch Interfaces only. You still may need to edit some interface names where a prefix might need to be added. Also might be good to review the LET with objects help page.

I'm pretty sure the PB has posted on this several times in other threads over the last several weeks.

What error message are you getting when you try to compile?

[Jim Lyons]

Thanks Richard

Here are the details:

I have 10 different .inc files using different COM Browser settings. I have attached both the PB9 ExcelAp2.bas and the Office11Excel03i.inc files i am currently using. The Office11Excel03i.inc file was generated with the following settings:

Always use an Interface Prefix
Interface Prefix = Excel_
Prefix GUIDs/ProgIDs with Library Name
Use Property Get/Set statements

I then did a search and replace for __ and replaced with _ for name consistency. I don’t know why the COM Browser sometimes adds an extra _.

Initially I received the error “Dispatch Object variable expected” on the following lines:

OBJECT LET oExcelApp.Visible = vBool
OBJECT CALL oExcelApp.WorkBooks.Add TO oExcelWorkBook
OBJECT CALL oExcelWorkBook.WorkSheets.Add TO oExcelWorkSheet
OBJECT LET oExcelWorkSheet.Cells.Item(vY, vX) = vText1
OBJECT LET oExcelWorkSheet.Cells.Item(vY, vX) = nVnt
OBJECT LET oExcelWorkSheet.Cells.Item(vY, vX) = vText2
OBJECT CALL oExcelWorksheet.PrintPreview
OBJECT CALL oExcelWorkBook.SaveAs(vFile)
OBJECT CALL oExcelApp.ActiveWindow.Close
OBJECT CALL oExcelApp.Quit

I fixed these errors by editing and removing the OBJECT CALL/LET. I do not have any clue why removing these would be required.

I then received a syntax error then a data type error on: oExcelApp.Visible = vBool which I fixed by making the vBool a long and adding (0) as follows oExcelApp.Visible(0) = lBool.

I fixed the syntax errors on the folloing 2 lines:
oExcelApp.WorkBooks.Add TO oExcelWorkBook
oExcelWorkBook.WorkSheets.Add TO oExcelWorkSheet

By adding (0) as follows:
oExcelApp.WorkBooks.Add(0) TO oExcelWorkBook
oExcelWorkBook.WorkSheets.Add(0) TO oExcelWorkSheet

The next error messages I get are “METHOD or PROPERTY name expected” on the following lines:

oExcelWorkSheet.Cells.Item(vY, vX) = vText1
oExcelWorkSheet.Cells.Item(vY, vX) = nVnt
oExcelWorkSheet.Cells.Item(vY, vX, vText2)
oExcelApp.ActiveWindow.Close

I have tried everything I can think of to satisfy PB9 but have been totally unsuccessful. The reason I ask for a working example is that I can learn from someone who has a much better handle on PB9 and COM and see how they were able to solve the problems I am experiencing. My guess is that I very well may be going about this all wrong. This goes back to not understanding why I had to remove the OBJECT CALL/LET syntax from the source file.

I would also like to point out that most of my edits above came from searching this forum. In my searching I have not found any post that discusses this issue, if anybody knows of a posted solution I would be grateful for a link to it.

Thanks in advance for any help.
Jim

[Steve Rossell]

As Richard said Excel does not work properly with VTable interfaces you need to generate Dispatch interfaces with Excel. This would also mean that you use OBJECT statements. This is not a PowerBASIC limitation but a limitation of Excel.

[Jim Lyons]

Thanks Steve

Can you tell me how to do this using COM Browser 2.00.005.

Jim

[Steve Rossell]

In the Tools | Options dialog box select the "Generate Dispatch Interfaces only" option.

[Jim Lyons]

Thanks again Steve

That’s what I thought. If I use that option I get many syntax errors within the include file generated by the COM Browser 2.00.005.

$CLSID_Event_Excel_RefreshEvents = GUID$("{165206F1-907E-4C4D-BC7A-523AFB5F2C99}") = GUID$("{9A8F5699-3B42-49F6-8DFF-C2112A31E390}")

I don’t understand the double equal in the source line.

Has anybody been successful using the COM Browser 2.00.005 that comes with PB9 with Excel 2003/2007?

Jim

[Steve Rossell]

This is an issue in the COM Browser that will be fixed in an upcoming release. You can simply delete one of the GUIDs as they are generated randomly by the COM Browser for event interfaces.

Yes, the COM Browser has been used with Excel. You can look at the Excel samples that ship with the compiler or search the forums.

[Jim Lyons]

Thanks Steve, It Works.

It would have been nice if someone would have said something about a known bug in the COM Browser. I spent a lot of time on this. Also, the Excel examples that come with the compiler use the Excel9.olb file from Excel 2000 not the Excel.exe which comes with Excel 2002, 2003 or 2007. I hope you will consider updating your samples.

I made the following edits to the Com Browser generated include file:

Removed the second = and everything after it.
$CLSID_Event_Excel_RefreshEvents = GUID$("{165206F1-907E-4C4D-BC7A-523AFB5F2C99}")
$CLSID_Event_Excel_AppEvents = GUID$("{77A7B9A4-2E56-4BAF-A45C-C09739C2CB81}")
$CLSID_Event_Excel_ChartEvents = GUID$("{E5CCF03D-61C8-49C1-BEBA-CACAC2B50437}")
$CLSID_Event_Excel_DocEvents = GUID$("{A8D9D9D3-1896-490E-9678-949B2C1E4ADA}")
$CLSID_Event_Excel_WorkbookEvents = GUID$("{EF00871A-D553-44B6-8257- DF0383E2A8DD}")
$CLSID_Event_Excel_OLEObjectEvents = GUID$("{44DBD257-EE59-4581-B2F0-1D63F82D8DD1}")

CLASS Class_Excel_AppEvents $CLSID_Event_Excel_AppEvents = GUID$("{77A7B9A4-2E56-4BAF-A45C-C09739C2CB81}") AS EVENT

Edited to: CLASS Class_Excel_AppEvents $CLSID_Event_Excel_AppEvents AS EVENT

CLASS Class_Excel_ChartEvents $CLSID_Event_Excel_ChartEvents = GUID$("{E5CCF03D-61C8-49C1-BEBA-CACAC2B50437}") AS EVENT

Edited to: CLASS Class_Excel_ChartEvents $CLSID_Event_Excel_ChartEvents AS EVENT

CLASS Class_Excel_DocEvents $CLSID_Event_Excel_DocEvents = GUID$("{A8D9D9D3-1896-490E-9678-949B2C1E4ADA}") AS EVENT

Edited to: CLASS Class_Excel_DocEvents $CLSID_Event_Excel_DocEvents AS EVENT

CLASS Class_Excel_WorkbookEvents $CLSID_Event_Excel_WorkbookEvents = GUID$("{EF00871A-D553-44B6-8257-DF0383E2A8DD}") AS EVENT

Edited to: CLASS Class_Excel_WorkbookEvents $CLSID_Event_Excel_WorkbookEvents AS EVENT

CLASS Class_Excel_OLEObjectEvents $CLSID_Event_Excel_OLEObjectEvents = GUID$("{44DBD257-EE59-4581-B2F0-1D63F82D8DD1}") AS EVENT

Edited to: CLASS Class_Excel_OLEObjectEvents $CLSID_Event_Excel_OLEObjectEvents AS EVENT

CLASS Class_Excel_RefreshEvents $CLSID_Event_Excel_RefreshEvents = GUID$("{165206F1-907E-4C4D-BC7A-523AFB5F2C99}") AS EVENT

Edited to: CLASS Class_Excel_RefreshEvents $CLSID_Event_Excel_RefreshEvents AS EVENT

I also did a search and replace for __ and replaced with _ for name consistency. I don’t know why the COM Browser sometimes adds an extra _.

I have attached the working include file for others to use and would appreciate any further insights.

Jim

[Steve Rossell]

The interfaces in Excel are backwards compatible. If you have Excel version 12 then the interfaces and calls from Excel version 9 will still work. This is why MS has never updated/fixed the interfaces in Excel to work with direct VTable calls, because if they did this would break existing code.

[Jim Lyons]

Steve

That’s good info to know, are you saying that there is absolutely nothing new available in Excel 2002, 2003, 2007 and that using an include file from Excel 2000 gives exactly the features, functions and interfaces. As I expand my PB application to use printing, charting, templates etc. everything possible will be there. I don’t know, but I would find that hard to believe.

I’m a new PB9 user, I have some experience with PB8 and in fact have several PB8/Excel programs working. I upgrade to PB9 and they won’t compile so I start searching for answers. I use the COM browser to generate a new include file but can’t get it to work and down the slippery slope I go trying COM Browser options and editing code. You on the inside, working with PB all the time have info and experience you take for granted. We on the outside (especially us relative newbies) struggle to do what you can do in your sleep. I strongly believe that both a PB known issues and a user contributed programming samples area on your web site is needed. I know you already have code area but a working samples area would give us newbies a place to go and maybe get something to begin new project or answer a question. I learn a lot by reviewing what other have done and how they did it.

Thanks again for your help. I will now take what I have learned here and apply it to my PB8 projects and attempt to get them working again in PB9 using my new and edited include file.

Jim

[Steve Rossell]

There maybe new interfaces in newer versions of Excel, but the old interfaces and method calls will still work. We used version 9 because we knew most users would have this version or greater.

[Cliff Nichols]

Thank you Jim for bringing up a viable point.
As one that came from VB and trying to get PB to work with what I know (later with other languages), to me all the examples use a PB program, with a PB COM, and something like word or Excel (which created the COM)

I am working on creating the COM in PB, and use it in whatever language, but I am blind as to how the COM was created, and only demos I see are PB in PB, so that makes things worse.

$CLSID_Event_Excel_RefreshEvents = GUID$("{165206F1-907E-4C4D-BC7A-523AFB5F2C99}") = GUID$("{9A8F5699-3B42-49F6-8DFF-C2112A31E390}")

I don’t understand the double equal in the source line.

I did not get this myself till Steve answered it. But it brings up a question.

Are not these values only valid if you set a static GUID within the PB GUID? or they would need to be changed via COMBROWSER every time you recompile?
(or at least that is my understanding of it, that a GUID is unique, and each compile creates a new GUID unless you specify a specific?)

The double GUID always got me too, and delete one vs the other did not change the results much, but made me wonder why one unique value equaled another "Unique" value

Thank you for the posts for those of us that know nothing about COM (aka I just used an OCX, or sample code and assumed that was how it worked, and FUBAR'ed when it would not work)

Now that I am the one making the COM/OCX, it is MUCH worse because I just do not get what is happening behind the scenes, and examples are somewhat language specific.

On the other hand, I know PB has been Onslaughted to give COM capability, and they have tried to give it to us, and have been there myself and to the users..."Give it time"...."When its viable and reliable, then its released"

Thats what I love about PB, they test it out, and when truly tested release it, if there is something not quite right, they endeavor to fix it. (and lets face it who among us got it RIGHT, the 1st or 2nd time?)

[Dominic Mitchell]

Now that I am the one making the COM/OCX, it is MUCH worse because I just do not get what
is happening behind the scenes, and examples are somewhat language specific.

But aren't you going about this backwards. It's like trying to understand Windows GUI programming
using DDT. If you want to understand what is going on behind the scenes, I think you should try
to write an OCX or OLE container using low-level COM.

For example,

  EVENTS FROM oBrowser CALL oEvents

If you know low-level COM, it is pretty obvious what is going on here and the code needed to implement it.

[Michael Mattias]

Oh my goodness, Mr. Mitchell: you are not suggesting fundamentals are important, are you? Heresy!

MCM

[Dominic Mitchell]

You bet I am. With the advent of DDT, there is an increasing sense on these forums of the blind leading the blind.
Alas, this is a measure of how successful DDT has been.

[Cliff Nichols]

Oh my goodness, Mr. Mitchell: you are not suggesting fundamentals are important, are you? Heresy!

MCM

Dominic

But aren't you going about this backwards. It's like trying to understand Windows GUI programming
using DDT.

Is that a statement? or a question??? or just something to make me realize "Ummmm....Whatttt IFfff???")

It got me thinking, what if I am going about it all wrong and currently the PB Com is kinda like DDT is to SDK????
(it works, but only because the compiler saves me from the intricacies of "Whats under the hood")

If this is the case, then at least I have a direction to look and research instead of..."Well it works in PB, why does it not work in another language???" or "How do I port to let another language use it???"

Thank you Dominic for reminding me, that I may be staring to hard at the forest, that I can not find the tree in front of me

[Gösta H. Lovgren-2]

You bet I am. With the advent of DDT, there is an increasing sense on these forums of the blind leading the blind.
Alas, this is a measure of how successful DDT has been.

I'll bet the mechanical switchers (guys who manually manipulated actual real switches) said the same thing about card punchers who said the same thing when ASM first came out who then said the same thing about the COBOL/Pascal/Basic/SDK users who said the same thing about Excel/Word script writers who said ....

And the world ever goes round ...

===============================
"Death alone reveals how small
are men's poor bodies."
Juvenal
===============================