Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Location
    Florida, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem with Cells.Replace via OLE call (Excel 2000)

    Here's a good problem -- maybe someone has seen this before...

    I have attempted to build a "mail merge" like facility in Excel, since there are a number of spreadsheets that are generated by the system that I work on. So what I tell the user community is that when they want a new spreadsheet, they should set it up EXACTLY as they want it (fonts, colors, etc.) and wherever they want to have a piece of data put in, they should put some string in that cell prefixed with the characters "^^". Works great.

    I added capabilities to the current system to generate flat data files containing a tag (the ^^ ones) and value pair. Works great.

    I wrote some VBA code in a separate Excel workbook that has a function BuildExcelDocument that takes three arguments: the data file name, the template file name, and a system username -- all strings. It makes a copy of the template file, reads the data file tag and value pairs, and loops thru the tags doing replaces using the following code:

    Sub ReplaceWorkbookTag(pairKey As Variant, pairValue As Variant)
    Dim wSheet As Worksheet

    For Each wSheet In goOutputWorkbook.Worksheets
    wSheet.Cells.Replace _
    What:=ksTagMarker & CStr(pairKey), _
    Replacement:=CStr(pairValue), _
    LookAt:=xlWhole, _
    SearchOrder:=xlByColumns, _
    MatchCase:=True
    Next wSheet
    End Sub


    Fairly straightforward. Now, I tested this by putting a test function in the code workbook and ran it -- takes about 3-4 seconds to run start to finish for about 50 tags. Works great.

    Now, this needs to be called via my application and uses OLE -- I hook it all up (using a framework that I built in the app for external Excel and Word calls) and it works....Not so great. Takes about 8 minutes (!) to run. I start throwing breakpoints in to try and find the problem, and, it boils down to that .Replace line in the subroutine above. That one line is responsible.

    My question...Is there something about using the Replace function via a call in OLE that makes it different? Some bug/hidden feature/quirk that I need to know or do?

    I am stumped.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Cells.Replace via OLE call (Excel 2000)

    No thoughts about why replace would be slow, but have you set screenupdating to false? It wil help.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    Oct 2002
    Location
    Florida, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Cells.Replace via OLE call (Excel 2000)

    I do this regularly for these types of macros. No difference.

    I have a small routine that I can set a global debugging flag to so I can turn it on and off as I need to see what's going on. I set ScreenUpdating, Interactive, and DisplayAlerts all to false once I get a macro working correctly.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Cells.Replace via OLE call (Excel 2000)

    Maybe changing this to:

    wSheet.UsedRange.Cells.Replace .......

    helps?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    New Lounger
    Join Date
    Oct 2002
    Location
    Florida, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Cells.Replace via OLE call (Excel 2000)

    That's a possibility. I can give that a shot. I have always wondered how to select the cells being used, simply because I see it mentioned often enough (LastCell, etc.) but the help doesn't link you to that property...

  6. #6
    New Lounger
    Join Date
    Oct 2002
    Location
    Florida, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Cells.Replace via OLE call (Excel 2000)

    UsedRange doesn't affect it at all.

    I sincerely believe that the problem is not with the Excel code, since it runs quite well inside Excel. I believe that the problem stems from it doing a replace while Excel is being invoked via OLE. Should I crosspost this to the VBA forum?

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Cells.Replace via OLE call (Excel 2000)

    I guess that's worth a try. Please include a link to this thread in your message.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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