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 ?
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,207
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.
 

voisin1972

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

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,207
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.
 

voisin1972

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

Watch MrExcel Video

Forum statistics

Threads
1,095,149
Messages
5,442,667
Members
405,191
Latest member
wedloski

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top