Results 1 to 5 of 5

Multiple Excel workbook to create 10 threads to split work load

This is a discussion on Multiple Excel workbook to create 10 threads to split work load within the Excel Questions forums, part of the Question Forums category; Hi, I have a 15k query to do in a IE object since every query takes over 5 seconds, I ...

  1. #1
    New Member
    Join Date
    May 2015
    Posts
    3

    Default Multiple Excel workbook to create 10 threads to split work load

    Hi,
    I have a 15k query to do in a IE object
    since every query takes over 5 seconds, I wanted to create 10 .xlsm files, (that I am successful) on each files I created I add into Thisworkbook lines as follow :

    Code:
    Public Sub Workbook_Open()    CoRegisterMessageFilter 0&, mlPreviousFilter
        FindIPDIssue
    End Sub
    Public Sub Workbook_Close()
        CoRegisterMessageFilter mlPreviousFilter, 0&
    End Sub
    I close those 10 Files then re-open them so the macro start querry the webpages, but the 2ns workbook will not open until the 1st one is finish, (as if I had still a 15k querry list.)

    how do I get Excel to open 10 of them without waiting for the previous one to complete macro ?

  2. #2
    Board Regular
    Join Date
    Oct 2007
    Posts
    4,489

    Default Re: Multiple Excel workbook to create 10 threads to split work load

    Welcome to MrExcel forums.

    You can do this with multiple VBScript agents. Each agent (.vbs file) would open one of the .xlsm files.

    Agent1.vbs
    Code:
    Option Explicit
    
    Dim Excel
    Set Excel = CreateObject("Excel.Application")
    With Excel
        .Visible = True
        .Workbooks.Open "C:\Folder\path\Workbook1.xlsm"
        .ActiveWorkbook.Close True
        .Quit
    End With
    Set Excel = Nothing
    Agent2.vbs
    Code:
    Option Explicit
    
    Dim Excel
    Set Excel = CreateObject("Excel.Application")
    With Excel
        .Visible = True
        .Workbooks.Open "C:\Folder\path\Workbook2.xlsm"
        .ActiveWorkbook.Close True
        .Quit
    End With
    Set Excel = Nothing
    And run all the agents (creating simultaneous multiple processes) using RunAllAgents.vbs
    Code:
    Option Explicit
    
    Dim Shell
    Set Shell = WScript.CreateObject("WScript.Shell")
    Shell.Run Chr(34) & "C:\Folder\path\Agent1.vbs" & Chr(34)
    Shell.Run Chr(34) & "C:\Folder\path\Agent2.vbs" & Chr(34)
    Shell.Run Chr(34) & "C:\Folder\path\Agent3.vbs" & Chr(34)
    'etc.
    Shell.Run Chr(34) & "C:\Folder\path\Agent10.vbs" & Chr(34)
    Set Shell = Nothing
    Note there is no such ThisWorkbook event as Workbook_Close. It should be Workbook_BeforeClose.

  3. #3
    New Member
    Join Date
    May 2015
    Posts
    3

    Default Re: Multiple Excel workbook to create 10 threads to split work load

    can we assign the excel application created with the script to an array in vba ? so we can know when it is ready to pull out the results ?

  4. #4
    Board Regular
    Join Date
    Oct 2007
    Posts
    4,489

    Default Re: Multiple Excel workbook to create 10 threads to split work load

    I don't think so, not with VBScript. The "Excel.Application" objects created by the VBScript files are separate to any VBA code in any workbook. I don't know how you would determine when each is ready to pull out the results. You could create the Excel objects in VBA like this:
    Code:
        Dim ExcelObjects(1 To 10) As Object
        Set ExcelObjects(1) = CreateObject("Excel.Application")
        With ExcelObjects(1)
            .Visible = True
            .Workbooks.Open "C:\Folder\Path\Workbook1.xlsm"
            .ActiveWorkbook.Close True
            .Quit
        End With
        Set ExcelObjects(1) = Nothing
    with a suitable loop for each object and workbook. But then you loose the multiple processes which the VBScript technique gives you.

    You might want to look at Multithreaded VBA An Approach To Processing Using VBScript | Excel & VBA Databison and
    Multi-threaded VBA | Excel Hero Blog for other ideas based on this multiple agent technique.

  5. #5
    New Member
    Join Date
    May 2015
    Posts
    3

    Default Re: Multiple Excel workbook to create 10 threads to split work load

    I will check file date stamp to find out if finished.

    Now I can't initiate

    Set Shell = WScript.CreateObject("WScript.Shell")

    WScript don't exist even if I put the reference into the project ..

    any ideals ?

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com