Multiple Excel workbook to create 10 threads to split work load

voisin1972

New Member
Joined
May 5, 2015
Messages
3
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 ?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.
 
Upvote 0
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 ?
 
Upvote 0
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.
 
Upvote 0
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 ?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top