Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lesson 12 example in Excel 97 Step by Step VB book (97, 2000, 2002)

    This question is directed at those having the MSFT Press Step By Step book
    for Excel 97 VBA.
    Since the code referenced is part of the book, I cannot post the code.

    I tried sending the following email to the author Reed Jacobson, the email
    address in the book appears to obsolete and the message bounced.

    Anybody know the solution?
    -------------------------------
    The code for Lesson 12 works in Excel 97, but does the following:

    1. In Excel 2000, the map is never displayed, just a blank black screen,
    and there are no error messages.

    2. In Excel 2002, a message is issued asking whether I want to change
    "Data". This message does not occur in either Excel 97 or Excel 2000.
    Whether I respond Yes or No, I do get the map. However, when I click on
    California, I get an error 1004.

    What needs to be changed in the Lesson12 example code to get this to work
    in Excel 2000 and Excel 2002? Do you have an errata for such issues?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lesson 12 example in Excel 97 Step by Step VB book (97, 2000, 2002)

    I use XL97 and do not have 2000 or 2002 so can NOT test. Some comments, (I have the file here, but can't look at the book as it is at work so I can't look into the thoughts of the authors as well)

    1) The lines that make the map and logo visible is:
    myLogo.Visible = msoTrue
    myMap.Visible = msoTrue

    Is msoTrue = -1 in XL2000? does it work if you change msoTrue to TRUE?

    Is myLogo and myMap defined correctly?

    Can you work directly with the shapes: in the immediate window
    worksheets("main").shapes(1).visible = true
    worksheets("main").shapes(2).visible = true

    2) the error comes when the pivottable cache is NOT setup correctly. The program reads data from a file named orders.dbf and uses a pivottable cache connection. Is the ODBC driver syntax the same in XL2002. It sounds like you have a problem with the code to get external data!

    Steve

  3. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lesson 12 example in Excel 97 Step by Step VB book (97, 2000, 2002)

    The book chapter, I've only gotten as far as page 299 so far, uses two workbooks:

    1. A supplied Lesson12.xls that is referenced on pages 281-285. Lesson12.xls is used to demonstrate what the rest of the chapter asks the reader to recreate.
    Two observations about this workbook:

    a. Workbook executes correctly in Excel 97, but not in Excel 2000 or Excel 2002. Note that this is a multiple -boot system so the same workbook files/directories are used with all 3 vesions of Excel.

    b. The workbook includes a ResfreshPivot sub that is used by Workbook_Open event. No problem, it works, more below.

    2. Pages 286 to at least page 299, instruct the reader on how to recreate the workbook, let's call this HKLesson12.xls.
    No problems doing so until I get to page 299, at which point I am asked to enter the code for the RefreshPivot sub.

    At this point the HKLesson12.xls does not yet have a Workbook_Open event, I believe that will be added later in the chapter.

    Note that I am now doing this in Excel 97, in case, there are indeed problems using the code in 2000 or 2002.
    However, after entering the code for RefreshPivot, the reader is instructed to run RefreshPivot.

    Boom!!!

    After doing so, the pivot table cache seems to have gotten blown up and I have not been able to get it back.

    So, I expect that I will need to reconstruct the workbook thru page 299, but this time I'll save the workbook prior to trying RefreshPivot.

    I will place a copy of the orders.dbf and map.wmf files in each directory that might be used.

    The books I have do not seem to discuss how to restore a Pivot Table in such a circumstance.
    It's rather tedious to rebuild the critter, so I MUST remember to save the workbook at an appropriate point.

    I agree that the problem is getting the external data.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lesson 12 example in Excel 97 Step by Step VB book (97, 2000, 2002)

    One comment:
    The code expects the orders.dbf to be UP one folder from where lesson12.xls is located, though this could be changed in the RefreshPivot routine

    Steve

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lesson 12 example in Excel 97 Step by Step VB book (97, 2000, 2002)

    Yes, I had noticed that. I have put a copy of the files in several places, so I won't have to worry about that.

    In a few minutes, I'm going to reboot to the system that has Office 97 and do the following:

    1. Copy the code from the Lesson12.xls to a clean .xls.
    2. Comment out ALL the code in the clean .xls.
    3. Then, as the chapter instructs adding code, I'll uncomment the relevant code in the clean .xls, to eliminate th echance of my entering code incorrectly.
    4. I'll save the clean .xls just before the chapter asks me to add RefreshPivot to avoid the problem I had yesterday when running RefreshPivot seems to have broken the PivotTable.

    If I get this working in Excel 97, then I'll try running the newly constructed .xls in Excel 2002.

    Of course, this still does not explain why the original Lesson12.xls won't run in Excel 2000 or Excel 2002.

  6. #6
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lesson 12 example in Excel 97 Step by Step VB book (97, 2000, 2002)

    Here's a summary of what I've found so far:

    1. The supplied lesson12.xls works in Excel 97, but not in Excel 2000 or
    Excel 2002.
    2. The chapter describes how to reconstruct the workbook.

    I am able to reconstruct the workbook in Excel 97, but I note the
    following:. These comments are relevant only to those having the book.

    a. Page 303 instructs using the Drawing toolbar to insert a rectangle around
    the map. However, doing so results in a white rectangle covering the map.
    So, I need to use Send To Back to change the Z-Order.

    My first comment would be why the book does not mention changing the Z-order
    for the rectangle. Of course, I could have missed something as I am sick and
    tired of reading this particular chapter.

    My second comment is that I do not know why the rectangle is needed. My 3rd
    comment is that the rectangle gets displayed, but it is not displayed in the
    lesson12.xls supplied with the book.

    Note that it is difficult to examine things in the supplied lesson12.xls
    because of the code used to hide menu/toolbars and protect the worksheets.

    b. The workbook I constructed, using Excel 97, works in Excel 2002, but only
    for the data for California. It does not work for the other 5 states.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lesson 12 example in Excel 97 Step by Step VB book (97, 2000, 2002)

    You read the instructions wrong. You are supposed to use the "select Objects button" from the drawing toolbar NOT the Draw rectangle button.

    He is NOT telling to draw a rectangle. The directions are calling for you to SELECT the rectangular area around the map the text. It is the SELECTION RECTANGLE you are "drawing". Then you group everything.

    Steve

  8. #8
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lesson 12 example in Excel 97 Step by Step VB book (97, 2000, 2002)

    Thanx, my eyes have wearied reading that chapter.

    Uncle! Actually, this has so frustrated me that a few minutes ago, I ordered a copy of Microsoft Excel 2002 Visual Basic for Applications Step By Step by the same author. Seems like a waste of money to buy the book, but it was a bit under $25(US), so if it answer sthe issue, it's well worth it.

  9. #9
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lesson 12 example in Excel 97 Step by Step VB book (97, 2000, 2002)

    Latest findings:

    Following the instructions in chapter 12, using Excel 97, I am able to create a workbook with macros that run correctly in both Excel 97 and Excel 2000, but not in Excel 2002.

    I have not tried to create the workbook in Excel 2002.

    However, I have one idea why the workbook supplied with the book may not work in Excel 2000 or Excel 2002.
    Using Excel 97, I created the workbook using Excel 97 SR2 (i).
    It's likely that the book's examples were created with the original release of Excel 97, perhaps even a Beta version of Excel 97.

    I ordered the Excel 2002 version of the book today.
    If the author's email address is in the book, I'll query him on this thread.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •