Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run macro in background
I created some macros to work with Access database.
Apparently, those macros took quite some time to calculate, around 1-3 minutes. While the macro is running, the Excel application freezes and I can't work during that time, only waiting for the macro to finish the calculation. Is there any way to tell Excel to run the calculation in the background, so that I may still be able to work with my worksheet while the macro is running? Regards, Yohan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run macro in background
Yohan,
You could start another instance of Excel, and put that code in a workbook open event, and open that workbook in the other Excel instance. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Yohan" wrote in message ... I created some macros to work with Access database. Apparently, those macros took quite some time to calculate, around 1-3 minutes. While the macro is running, the Excel application freezes and I can't work during that time, only waiting for the macro to finish the calculation. Is there any way to tell Excel to run the calculation in the background, so that I may still be able to work with my worksheet while the macro is running? Regards, Yohan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run macro in background
Do you mean it takes a long time to recalculate the workbook once
you've got the data or it takes a long time to fetch the data? If it's the latter, you may be able to use an ADO recordset to fetch the data asynchronously. Here's some code which fetches millions of rows from a table my MS Access database. When the workbook opens, I can use it normally. After about a minute a message appears telling me the data has been fetched. Option Explicit Private m_con As ADODB.Connection Private WithEvents m_rs As ADODB.Recordset Private Sub Workbook_Open() Set m_con = New ADODB.Connection With m_con .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Tempo\New_Jet_DB.mdb" .Open End With Set m_rs = New ADODB.Recordset With m_rs .LockType = adLockReadOnly .CursorLocation = adUseClient .CursorType = adOpenForwardOnly .Source = "SELECT * FROM LongTime" .ActiveConnection = m_con .Open , , , , adAsyncFetch Or adCmdText End With End Sub Private Sub m_rs_FetchComplete(ByVal pError As ADODB.Error, _ adStatus As ADODB.EventStatusEnum, _ ByVal pRecordset As ADODB.Recordset) MsgBox "Done!" m_rs.Close m_con.Close Set m_rs = Nothing Set m_con = Nothing End Sub -- "Yohan" wrote in message ... I created some macros to work with Access database. Apparently, those macros took quite some time to calculate, around 1-3 minutes. While the macro is running, the Excel application freezes and I can't work during that time, only waiting for the macro to finish the calculation. Is there any way to tell Excel to run the calculation in the background, so that I may still be able to work with my worksheet while the macro is running? Regards, Yohan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro in Background | Excel Discussion (Misc queries) | |||
How to work with macro in background | Excel Discussion (Misc queries) | |||
How do I run a macro in the background | Excel Discussion (Misc queries) | |||
Macro that runs in the background | Excel Discussion (Misc queries) | |||
running macro at the background | Excel Programming |