Non-blocking calls to Excel Macro using OLE Automation

[Guest]

Hello,

I've created a simple VB program, using Automation, to start excel to load a
template (Report_1.xlt) and then call a macro(Module1.ImportData) on that
template to import a data file (dump.txt):

 

[Tim Williams]

You could try using OnTime to schedule the macro in excel (eg. for 1
second from the call time)

Tim.

 

[DM Unseen]

BW

Excel is single threaded with VBA so this won't work. Seeing the macro
name it seems you want to import data. The only concurrency within
excel that works is on Getting External data. Database Queries in Excel
can be run in the background( the same goes for DDE and TDS, but that
won't help you)

DM Unseen

 

[Tim Williams]

I got the impression that the OP was using VB (not VBA) to automate Excel,
and AFAIK Excel is only single-threaded within any one instance of the Excel
application itself.
As long as the VB code following the "Run" call doesn't depend on the import
being complete then it would seem feasible to kick off the import and then
go on to do other tasks within the VB code...

Tim.

 

[DM Unseen]

Tim

VB(A) (COM)Automation is alwasy synchronous so there is no difference
between out of process Automation(VB or in process(VBA) Automation.
*Your* Ontime could work, although I would be interested to know when
excel would be idle enough so it could start the OnTime procedure. I
would suggest something tricky like this:

Dim XLApp Dim wb
Set XLApp = CreateObject("Excel.Applicatio­n")
XLApp.Visible = True 'why would you do this? Users would just get
confused, I would use False instead until you've got something to show
XLApp.Interactive = False 'prevent users from fooling around with excel
XLApp.Ignoreremoterequest = false 'prevents other apps from fooling
around with excel
XLApp.UserControl = True ' allows excel to run even when XLApp object
reference is cleared.
set wb = XLApp.workbooks.open("C:\temp\Report_1.xlt" )
XLApp.OnTime NOW() + 5seconds , "Module1.ImportData", "dump.txt"
set wb = nothing
set XLApp = nothing (excel should not quit!), but shoud now become
idle (and run the onTime Proc)

'Do other stuff.

set wb = GetObject(,"C:\temp\Report_1.xlt" )'connect back to the
running excel through the workbook. this works even if other excel
instances are active as well
set XLApp = wb.parent

' note that you need to be sure that Excel is done with the Ontime,
else your VB process will now hang!

' do more stuff with excel

XLApp .Quit 'Excel will quit

BTW maybe DDE would work also, I have never tried this.

Dm Unseen

 

[Guest]

Thanks all for responding.

Actually a call to Ontime is "asynchronous" and does not cause the caller
to block.

Thanks