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.
Rajnish_Sharma_ibmPrinciple ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

patrickabCommented:
Rajnish_Sharma_ibm,

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
dlmilleCommented:
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
SiddharthRoutCommented:
May I see your VBA Procedure?

Sid
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

dlmilleCommented:
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.Application")
   
    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
Arno KosterCommented:
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

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
[...]
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Open in new window


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.
PatOBrienCommented:
Here is a windows script that will open an excel workbook and run a macro...  This can be set up to run every 20 minutes in the Windows Job Scheduler.

If your workbook relies on an add-in then you should also open the add-in (do this first before opening your wb).

Copy this code to a file with the .vbs extension.

Dim fname
Dim xlApp
dim macroName

'path / name of workbook to open
fname = "X:\FodlerA\FolderB\My_workbook.xls"
macroName = "MyProcedure"

Set xlApp = CreateObject("Excel.Application")

xlApp.visible = False

xlApp.DisplayAlerts = False
xlApp.AskToUpdateLinks = False

'open the workbook
Set xlWB = xlapp.workbooks.open(fname)
'run the macro
xlApp.Run(macroName)

'save the wb
xlWB.Save

'turn alerts back on
xlApp.DisplayAlerts = True
xlApp.AskToUpdateLinks = True

xlWB.Close
xlapp.Quit
Set xlwb = Nothing
Set xlapp = Nothing

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rajnish_Sharma_ibmPrinciple ConsultantAuthor Commented:
Thanks it works
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.