Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Resource problems

    Hi there all!

    Sorry for the long explanation, but it a slightly complext situation.

    I am running MS Office 2000 Premium on Windows 98 on Pentium III class machines with 128Mb RAM.

    Here is the scenario:
    I have a 100 to 105 page RTF document containing a statement for x number of people, which is generated by our financial system. I open the RTF documentin Word and then run the VBA code.

    My VBA code in Word starts off by opening another Word document which contains a handling table. The handling table contains an entry for each person in the financial report. These reports can be delivered to them by
    either be email or fax or snail mail (printed and then posted). It also creates a new document for reporting purposes.

    The VBA code then coping and pasting the first page to a new document and replaces some lines which where left out when the financial system exported the data to the RTF document. It then picks up a unique identifier for the
    page (the person's ID number) and the person's name.
    It then finds the corresponding ID number in the handling table and picks out the corresponding action (email/fax/print) out of the table and performs
    the appropriate action. and closes and saves the new document. The code then switch to the report document and an entry is written to it.
    (Basically action taken, time done, person's ID number and name).

    The rest of the pages in the master RTF document is then processed.

    My problem is that if the machine has anything less than 85% to 90% free system resources and 85% to 90% free user resources, the machine runs out of resources and totally freezes up.

    My question is: Is there a way to free up system and user resources while processing. Anyone got any good suggestions?

    Looking forward for some helpful pointers!

    James
    (EMail address: james.nesbitt@icon.co.za)

    [img]/w3timages/icons/wink.gif[/img]

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resource problems

    James,

    I'd be looking at what the VBA is doing to cut down on the resources rather than anything external.

    How are you doing the copying and pasting? If you are doing it onto the clipboard and into the second document, that could be consuming a lot of resources. Even using the "selection" object is probably worse than using the range object (it's certainly slower).

    Just a thought- it depends on how you're processing though.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Resource problems

    Hi,

    Just another thought, along the lines of Geoff's suggestion: try temporarily remming out selected parts of the code (starting I'd say with the e-mail/fax/print part) and see how the temporary changes affect resource use when the code is run.
    This might help narrow down which specific process(es) are causing the problem.

    Another permutation, if you can coordinate it, would be to try stepping through the code, while watching a resource meter (I've never tried that one though so not sure it's doable!).

    Gary

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resource problems

    A minor consideration: remove the Office Shortcut bar. It's less than 2MB, but it's something. Since you did not give any info re: software installed on the PC, it's hard to give any more advice. How many icons in the taskbar icon tray? There could be lots of stuff there you could pitch, maybe. Then again, your VBA app may have references to dlls, OCXs, etc. that are UNnecessary. As an experiment, you could install another 128MB in a PC and see if that really solves the problem.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resource problems

    Kevin, thanks for your comments. Here is a couple of finer points which I have tried and found.
    1) I have trimmed down and closed everything that may be open to free up resources to the point that it could be considered a freshly installed machine. This increases the resources and, therefore, the number of pages processed.

    2) Even though there is 128Mb RAM on the machine, I have had to limit it to only using 48Mb RAM. This not only gives more resources, but it also improves Windows 98's memory and resource management. This is a proven fact with Windows 98. (With Windows 95 as well)

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resource problems

    Initially when I started this project, I was just learning to program in VB iteself, even though I have used a similar package to VB.

    To select the page, I am using the Find and Select statement. I am basically starting at the top of the page and find and selecting 1 word to the right until a certain set of characters are found (currently set as ^~), which is currently at the end of the page. Once the end of the page is reached, I then do a Select.Copy, create a new document and paste it into the new document.

    I know that I am able to use another method to select the entire page, however, down the line - each report in the document (currently only 1 page each), may have so many transactions which forces it to spill onto a second page.

    Any further suggestions?

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington (Greater Manchester area)/Cheshire, UK
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resource problems

    I'll try something like that.

    Thanks

    James

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resource problems

    James,

    Here's a quick bit of code for copying all the text from one document to a section in another document, just to give the idea of bypassing cut and paste. This copies text only without formatting, but if you don't want the formatting, will be probably the most resource-efficient way:

    Dim rngFrom As Range
    Dim rngTo As Range

    Set rngFrom = Documents("Document1").Range
    Set rngTo = Documents("Document2").Bookmarks("Copyhere").Range

    rngTo.Text = rngFrom.Text


    It may be even easier (and will be a lot faster) if you work with strings instead of the document:

    dim rngDoc as range
    dim strText as string
    Set rngDoc = Documents("Document1").Range
    strText = rngDoc.Text
    (Manipulate the text- eg
    dim iStart as Integer
    dim iEnd as Integer
    iStart = 1
    iEnd = instr(strText, "^~")

    set rngDoc = Documents("Document2").Bookmarks("Copyhere").Range

    ' Copy in the selected portion of the text
    rngDoc.Text = mid$(strText,iStart, iEnd - 1)

    set strText to what's left
    strText = mid$(strText, iEnd +2)
    iStart = iEnd + 2


    Sorry, this is a bit rough, I'm supoosed to be out of here 20 minutes ago, MrsW will not be pleased. But I hope that helps
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  9. #9
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Resource problems

    In addition to Geoff's suggestions, which look really good, you can extend your selection much more simply with something like:

    <pre>With Selection
    .Extend
    .Extend Character:="^"
    .Extend Character:="~"
    .Copy
    .Collapse
    'etc.
    End With
    </pre>


  10. #10
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resource problems

    Gary,

    I didn't know you could do the "extend" with a character search with the selection object. I haven't tried it- I would guess you can do it with the range object as well.

    I do have a problem with using "Selection". In many (but not all as I understand) it's quicker to use Range rather than Selection- particularly as Selection does things to your screen while it's working, so it's slowing down with screen refreshes. Maybe "Application.screenupdating = false" will speed things up, but I suspect (don't know) that using "range" will be much faster.

    I was also trying to avoid the "copy" in this situation, as I suspect it also is responsible for consuming a big hunk of the system resources.

    Some time ago, I was constructing a large table, by adding one row at a time to a table, and pasting in data. It took a fair while.

    Instead, I set up an array, and set values in the array. It was only a one-dimensional array, with tabs separating what would later be columns in the table. It was only when the array was finished that I copied in the entire array, and then constructed the table around it. It was much quicker.

    In Excel (off-topic but relevant) I've had huge performance improvements by setting up a 2- dimensional array, then using "range" to copy in the whole array. For up to (I think) about 6 columns, there was not much of a performance difference between doing it that way and setting up individual cells; but adding more cells really degraded the performance.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  11. #11
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Resource problems

    Hi Geoff,

    Your point is taken that it's better to avoid the Selection object in favor of the Range object when possible. And I appreciated the neat methods in your previous post.

    Like many self-taught Word VBA'ers I started out very dependent on the Selection object. It's the easiest to get started with and the easiest to understand in terms of relating the processes in your program , with what you would be doing in the user interface (it is after all, what the macro recorder uses when it produces code, which tells you something about the centrality of the Selection object in the Word object model's design). It's also easier to debug and to follow program flow.

    It's only fairly recently that I've weaned myself more consistently away from good ol' Selection; I feel real good about myself when I can do a whole routine without using Selection. In the case of Selection.Extend, the thought that came to mind was something from Word's user interface, which is the Selection Extend mode (F8 key), and which has the neat feature of allowing you to extend a selection to a character you type on the keyboard. I actually recorded the code initially(!) to see what would happen. I don't think there is an equivalent for the Range object but not sure.

    Another angle on Selection vs. Range is that folks who learn VBA via Excel get very familiar with the Range object cause that's the main way to do things in Excel VBA; Excel-bred VBA'ers tend to use Range when doing Word VBA, more than do Word-bred VBA'ers. No question getting at things via Range is higher on the programming evolutionary scale, but it's worth reflecting on the close connection between Word and Selection.

    A really interesting gloss on this is to go back and see Woody's discussion of Selection vs. Range (which runs to 6 pages) in Word 97 Annoyances - it's very Word/Selection-centric. He surmised that the new emphasis (in things like MS help) on Range in Word 97 was the outcome of some occult struggle within MS between the forces of Excel and the forces of Word, with the Excelites winning the day. And he provides some detailed analysis before concluding with the advice that Word VBA'ers should forget about Range, and just use Selection - he really gets worked up about it! Not sure I agree with it all, but it makes good reading.

    Well, I better get off the line. Interesting topic though.

    Gary

Posting Permissions

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