Non-blocking calls to Excel Macro using OLE Automation
 

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):

Non-blocking calls to Excel Macro using OLE Automation
 

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

Tim.


"BW" wrote in message
...
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):

.
.
Dim XLApp
Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = True
XLApp.workbooks.open "C:\temp\Report_1.xlt"

XLApp.Run "Module1.ImportData", "dump.txt"
.
. more VB code here.

The problem is that the VB code immediately below the 'XLApp.Run...'
statement blocks UNTIL the excel macro finishes loading the data.

Anyone know of a way to make calls to an excel macro from VB
non-blocking?
ie. i would like the code below 'XLApp.Run.. " to continue executing
while
the Excel macro is importing the data.

Thanks!

Non-blocking calls to Excel Macro using OLE Automation
 

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

Non-blocking calls to Excel Macro using OLE Automation
 

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.

--
Tim Williams
Palo Alto, CA


"DM Unseen" wrote in message
ups.com...
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

Non-blocking calls to Excel Macro using OLE Automation
 

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

Non-blocking calls to Excel Macro using OLE Automation
 

Thanks all for responding.

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

Thanks

"DM Unseen" wrote:

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