Announcement

Collapse
No announcement yet.

Need a PB9 2003/2007 Excel working sample.

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Need a PB9 2003/2007 Excel working sample.

    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

    #2
    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?
    Rick Angell

    Comment


      #3
      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
      Attached Files

      Comment


        #4
        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.
        Sincerely,

        Steve Rossell
        PowerBASIC Staff

        Comment


          #5
          Thanks Steve

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

          Jim

          Comment


            #6
            In the Tools | Options dialog box select the "Generate Dispatch Interfaces only" option.
            Sincerely,

            Steve Rossell
            PowerBASIC Staff

            Comment


              #7
              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
              Attached Files

              Comment


                #8
                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.
                Sincerely,

                Steve Rossell
                PowerBASIC Staff

                Comment


                  #9
                  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
                  Attached Files

                  Comment


                    #10
                    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.
                    Sincerely,

                    Steve Rossell
                    PowerBASIC Staff

                    Comment


                      #11
                      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

                      Comment


                        #12
                        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.
                        Sincerely,

                        Steve Rossell
                        PowerBASIC Staff

                        Comment


                          #13
                          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?)
                          Engineer's Motto: If it aint broke take it apart and fix it

                          "If at 1st you don't succeed... call it version 1.0"

                          "Half of Programming is coding"....."The other 90% is DEBUGGING"

                          "Document my code????" .... "WHYYY??? do you think they call it CODE? "

                          Comment


                            #14
                            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,
                            Code:
                              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.
                            Dominic Mitchell
                            Phoenix Visual Designer
                            http://www.phnxthunder.com

                            Comment


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

                              MCM
                              Michael Mattias
                              Tal Systems (retired)
                              Port Washington WI USA
                              [email protected]
                              http://www.talsystems.com

                              Comment


                                #16
                                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.
                                Dominic Mitchell
                                Phoenix Visual Designer
                                http://www.phnxthunder.com

                                Comment


                                  #17
                                  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
                                  Engineer's Motto: If it aint broke take it apart and fix it

                                  "If at 1st you don't succeed... call it version 1.0"

                                  "Half of Programming is coding"....."The other 90% is DEBUGGING"

                                  "Document my code????" .... "WHYYY??? do you think they call it CODE? "

                                  Comment


                                    #18
                                    Originally posted by Dominic Mitchell View Post
                                    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
                                    ===============================
                                    It's a pretty day. I hope you enjoy it.

                                    Gösta

                                    JWAM: (Quit Smoking): http://www.SwedesDock.com/smoking
                                    LDN - A Miracle Drug: http://www.SwedesDock.com/LDN/

                                    Comment

                                    Working...
                                    X
                                    😀
                                    🥰
                                    🤢
                                    😎
                                    😡
                                    👍
                                    👎