Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Limitation on # of Workbooks (Excel 2002)

    Hi,
    I've written a macro that loops through a customer list of about 300 and makes a customer statement for each customer. This macro makes a "customer statement" for one customer at a time... first it copies the customer's invoices and related data into a blank customer statement, then it copies the customer statement worksheet into a new workbook, and then closing that new workbook. The macro loops through the list, and each time it gets to the 32nd workbook, it slows way down. The macro still works, however it takes 5 times as long to make each new workbook. Is there a limit in a folder as to how many new workbooks can be generated by moving a worksheet and then saving it as a new file? No matter where I start in the customer list, it always dramatically changes in speed at the 32nd workbook. Unfortunately, there will always be about 300+ in my list. Any ideas?
    Thanks!
    Lana

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Limitation on # of Workbooks (Excel 2002)

    Are you creating the workbooks on a local hard disk or on a network disk? If the latter, try saving them to a local hard disk first - you can always copy them to the network later on.
    You could also try turning off your antivirus program temporarily - don't forget to turn it on again later!

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Limitation on # of Workbooks (Excel 2002)

    I tried the two different options you mentioned Hans... and with each option (turned off virus, saved files to local drive), it still slowed dramatically down at the 32nd workbook. Any other ideas?
    Thanks!
    Lana

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Limitation on # of Workbooks (Excel 2002)

    The only thing I can think of is to process batches of 30, and to close the workbook, perhaps even quit Excel, after each batch.

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Limitation on # of Workbooks (Excel 2002)

    <hr>first it copies the customer's invoices and related data into a blank customer statement, <hr>
    For statement #2; are you cleaning out the data from statement #1 to create your blank customer statement?
    Regards
    Don

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

    Re: Limitation on # of Workbooks (Excel 2002)

    This is something I've heard reported before and I seem to recall the "solution" being to close and reopen Excel.
    One thing you could do is use automation to open a separate instance of Excel to create the files. Then you can easily kill that instance every 30 workbooks:

    <pre>Option Explicit

    Function GetMeAnExcelInstance() As Object
    Static XL As Object
    Static lCount As Long
    If XL Is Nothing Then
    Set XL = CreateObject("Excel.Application")
    Else
    If lCount > 30 Then
    XL.Quit
    Set XL = Nothing
    Set XL = CreateObject("Excel.Application")
    lCount = 0
    Else
    lCount = lCount + 1
    End If
    End If
    Set GetMeAnExcelInstance = XL
    End Function

    Sub Demo()
    Dim oXL As Object
    Set oXL = GetMeAnExcelInstance
    ' Now do your processing using oXL
    MsgBox oXL.Caption
    oXL.Quit
    Set oXL = Nothing
    End Sub</pre>

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

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Limitation on # of Workbooks (Excel 2002)

    Thanks to all for responding! I'm unsure as to how to incorporate the code Jan wrote into my code. Do I put the function in a module, or attached to a particular worksheet, and how do I get it to work?
    Thanks!
    Lana

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Limitation on # of Workbooks (Excel 2002)

    You should copy the code into a standard module - the kind that you create by selecting Insert | Module in the Visual Basic Editor.

    Where Jan Karel wrote

    ' Now do your processing using oXL
    MsgBox oXL.Caption

    you should insert your code, but it should refer to oXL instead of the instance of Excel running the code. A bit technical, I'm afraid. If you post your workbook (or your code), Loungers may be able to help you.

  9. #9
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Limitation on # of Workbooks (Excel 2002)

    Attached is my file & macros (without the real data)... I've added Jan's code as suggested, and tried it out, however I'm sure I don't have it right (as it slowed down after 31 workbooks), and I'm not sure how it would know to stop or shut down after 30 workbooks saved, and how it would know where to start back up again on the list of customers??? Any ideas would be awesome!! The main macro is named CreateStmts in module 3.
    Thanks so much!
    Lana
    Attached Files Attached Files

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

    Re: Limitation on # of Workbooks (Excel 2002)

    Ouch, this complicates things a bit.
    You'd have to:
    - Put the VBA in a separate file, along with just the information to be stuffed in the files you want built.
    - Put the sheets to be filled in a separate file and open that file and fill those sheets using the second excel instance.
    - rewrite quite a bit of the code to cater for that situation

    I lack time to do this for you though, sorry.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Limitation on # of Workbooks (Excel 2002)

    I'll give this a try... thanks so much for your help Jan!
    Lana

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

    Re: Limitation on # of Workbooks (Excel 2002)

    Let me explain the code I posted above line-by-line. Explanation shows ABOVE each line of code.

    'Top of your module:
    Public oXL As Object


    Function GetMeAnExcelInstance() As Object
    'Declare an object variable that will hold the 2nd Excel instance. Declare static so this variable does not get cleared when the sub ends.
    Static XL As Object
    'Declare a static counting variable
    Static lCount As Long
    'Check if we already have a 2nd Excel running
    If XL Is Nothing Then
    'No 2nd instance, create one
    Set XL = CreateObject("Excel.Application")
    'You could make the 2nd instance visible by removing the single quote in front of the next line:
    'XL.Visible=True
    Else
    'If our counter reaches over 30, time to kill the Excel instance and start afresh
    If lCount > 30 Then
    'Quit 2nd instance WARNING: this will not happen when any workbook is opened in that instance that has not been saved
    XL.Quit
    'Remove object variable from memory
    Set XL = Nothing
    'Now create a new 2nd instance
    Set XL = CreateObject("Excel.Application")
    'You could make that one visible again here, like shown above
    'Reset counter
    lCount = 0

    Else
    'Add to the count
    lCount = lCount + 1
    End If
    End If
    'Return the current "copy" of the 2nd Excel instance
    Set GetMeAnExcelInstance = XL
    End Function

    Sub Demo()
    'This line ensures we get the 2nd instance
    Set oXL = GetMeAnExcelInstance
    ' Now do your processing using oXL
    ' For example, load your workbook with data:
    oXL.Workbooks.Open "c:my documentsDataWorkbook.xls"
    ' and load the template
    oXL.Workbooks.Open "c:my documentsTemplateFile.xls"

    End Sub

    Furthermore: Everywhere in your code where you are doing something to either of the workbooks opened above, you must prepend the statements with "oXL." (without the quotes)
    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
  •