Calling vba Sub. from a script
Hi,
I have a vba procedure which i needs to call automatically by using some script.
this is required because excel workbook is dealing with more than 80000 rows and 50 columns data which is too voluminious ( huge in size) .
one instance of this code take roughly 15 mins to proceed and i need to call it repeatdly for a 20 min interval so that every time new data arrives it process new report.
Adding extra code to this file will make this file more heavy to process (system ram is 2 GB)
so i need a script that can open my work book by opening an instance of excel and run the vba proceure inside my workbook. Also i need script in the end to close and save file and excel once vba procedure ran sucessfully.
I have a vba procedure which i needs to call automatically by using some script.
this is required because excel workbook is dealing with more than 80000 rows and 50 columns data which is too voluminious ( huge in size) .
one instance of this code take roughly 15 mins to proceed and i need to call it repeatdly for a 20 min interval so that every time new data arrives it process new report.
Adding extra code to this file will make this file more heavy to process (system ram is 2 GB)
so i need a script that can open my work book by opening an instance of excel and run the vba proceure inside my workbook. Also i need script in the end to close and save file and excel once vba procedure ran sucessfully.
If you're concerned about running this while you're doing other work, perhaps also Excel, may I suggest that you remote desktop in to your machine (long on with another instance) - that way you can have an isolated VM doing this operation for you - and you can monitor it from the window into that instance...
This assumes you have an "Ultimate" edition - e.g., Vista Ultimate, and you configure so you can log into your own machine with multiple instances of your username. I can send you links for this if you want to go down this path.
Dave
This assumes you have an "Ultimate" edition - e.g., Vista Ultimate, and you configure so you can log into your own machine with multiple instances of your username. I can send you links for this if you want to go down this path.
Dave
May I see your VBA Procedure?
Sid
Sid
Ok - let's give him what he's asking for as if he knows better and really wants to do this....
from the primary excel application, add this procedure:
Sub NewInstance()
Dim objExcel As Object
Set objExcel = CreateObject("Excel.Applic
objExcel.Visible = True ' makes the instance visible
objExcel.Workbooks.Open Filename:="YOUR SPREADSHEET.xlsm" '- have the spreadsheet setup to run your macro on open, save and close itself
End Sub
in YOUR_SPREADSHEET.xlsm, put this in ThisWorkbook module of the VBA Project:
Private Sub Workbook_Open()
Call MY_PROCEDURE
ActiveWorkbook.Save
Application.Quit ' terminates this instance.
End Sub
You could also call the procedure from the primary workbook, but try this to see if this is where you're headed.
Then, when you want to open your spreadsheet that has this workbook_open() procedure, above, just hold the ALT key while you open the file to keep it from running it. So you can edit or whatever...
Dave
from the primary excel application, add this procedure:
Sub NewInstance()
Dim objExcel As Object
Set objExcel = CreateObject("Excel.Applic
objExcel.Visible = True ' makes the instance visible
objExcel.Workbooks.Open Filename:="YOUR SPREADSHEET.xlsm" '- have the spreadsheet setup to run your macro on open, save and close itself
End Sub
in YOUR_SPREADSHEET.xlsm, put this in ThisWorkbook module of the VBA Project:
Private Sub Workbook_Open()
Call MY_PROCEDURE
ActiveWorkbook.Save
Application.Quit ' terminates this instance.
End Sub
You could also call the procedure from the primary workbook, but try this to see if this is where you're headed.
Then, when you want to open your spreadsheet that has this workbook_open() procedure, above, just hold the ALT key while you open the file to keep it from running it. So you can edit or whatever...
Dave
Do the 80k x 50 cells in the voluminous primary worksheet contain only data or do they also contain formulae ?
Especially when formulas are involved, you might find great benefit in using the statements
On my desktop, when I fill 80.000 x 50 cells with raw data (without formulas), using these statements improves the amount of time needed from 294 to 200 seconds. When formulas are used which relate cells to one another, the improvement can be even more significant.
The drawback of these statements is that when the screen is not updated, there is no visual notification that the update process is busy. You could use
application.statusbar = "message"
for this. When intermediate formulae calculation is required, use
application.Calculate
where appropriate.
Especially when formulas are involved, you might find great benefit in using the statements
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
[...]
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = TrueOn my desktop, when I fill 80.000 x 50 cells with raw data (without formulas), using these statements improves the amount of time needed from 294 to 200 seconds. When formulas are used which relate cells to one another, the improvement can be even more significant.
The drawback of these statements is that when the screen is not updated, there is no visual notification that the update process is busy. You could use
application.statusbar = "message"
for this. When intermediate formulae calculation is required, use
application.Calculate
where appropriate.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks it works
1. If all the 50,000 by 50 rows do not actually change every 20 minutes then it would be better to write your code so as to exclude them from the calculations once they have been processed. To re-process them every 20 minutes, if they don't change, will make the macro very slow. So confine the macro only to the latest data - unless all the data must to be updated every 20 minutes.
2. 2GB ram is too small. RAM is pretty cheap - increase it to 3GB if you're on an older system Win XP for example. If you have a 64bit OS then install 4GB of RAM.
Patrick