Run an excel macro in batch?? 4

[cramd]

I have created an excel macro, i.e. open excel, record macro, save it, modify code, then run it. Now all works the way I need it to but I would like to take this macro one step further. The macro needs to run against a network folder that contains over 30,000 documents. When I run this macro, I don't really want to open excel and sit at my machine watching this thing run interactively all day--I need to run a batch program for this--an executable program maybe?? I'm not sure how to get started with this, does anyone have any helpful links or examples that may help me?

Diane

 

[Nikita6003]

Hi Diane,

we have an overnight batch running from a single excel worksheet that takes a number of textfiles and spreadsheets which are FTP-ed accross to a file server, and imports the result into a database. The Excel sheet then performs cleaning and calculating routines - all of this at night, in a certain order, and without human interaction! All we need to do when we come in is to check out error and info logs for anything strange. And although the process does sometimes crash (causing us hours delay while we sit & watch our screen ;-)) it's proven to be fairly robust.
So there's no need to work with executables unless you want to/have to (reason we chose Excel was mainly because we weren't allowed to buy a VB enterprise licence by the company that hired us ...)

The only thing is, I can't quite make out what you need. Do you want to

a. Set up a routine that will process these files in a loop? If so, what have you coded & what are your requirements?
b. set up a sort of control workbook which can be set to run macros at a pre-determined time at night? (take a look at the

OnTime

function!)

In both cases there's quite simple solutions - plz give a little more information, or if you like, I can take a look at the xl sheet you´ve got

Let me know which, if any ...

Cheers
Nikki

 

[cramd]

Nikki:
Thanks for your interest in my post. Hopefully the following will help you understand what I'm trying to accomplish:

I have a folder that contains 30,000 csv files. My macro opens each file in a worksheet, converts text to columns, saves the worksheet (still as a .csv file) then closes the worksheet. It is a repetitive loop until all files have been processed. This will be a one time process to get all files with text converted into columns. To begin the macro, I open excel, select tools, macro, select my macro and let it run. I have tested my macro with 2,000 files and all works well—except for the fact I am watching each worksheet open and close. I would really like to get this to work with a batch process, especially before I run this against the full folder. Below is the shortened version of my code:


Option Explicit
Dim NumberofFiles As Integer
Dim foundfiles() As String
Dim i As Integer

Sub csv()

Call GetDocs
Call OpenFiles
End Sub

Private Sub GetDocs()

Dim fs As FileSearch

Set fs = Application.FileSearch

With fs
.LookIn = "G:"
PROCESS FILES
End With
End Sub

Private Sub OpenFiles()
Dim thisfile As String

For i = 1 To NumberofFiles
Workbooks.Open (foundfiles(i))

Columns("A:A&quot.Select
PROCESS TEXT TO COLUMNS
SAVE WORKSHEET
Next i
End Sub

 

[Nikita6003]

Hi Diane,

I've got a conversion of csv files running as well, an use

Application.Workbooks.Open Filename:=p_sFileArray(l_iFileCounter), Delimiter:=","

to open the files without having to convert to columns. Enter whatever delimiter is used - I've used the comma because of the csv file type, but we also receive pipe-delimited files and it works fine for that as well.
Perhaps that would remove the need of opening and converting text to columns (in case you're using the DATA > CONVERT TEXT TO COLUMNS option)

If you still need to convert your files, and you don't want to sit & watch Excel doing this, use the option

Application.ScreenUpdating = False
Application.DisplayAlerts = False

at the start of your routine, and switch both options back on when it's done:

Application.ScreenUpdating = True
Application.DisplayAlerts = True

The ScreenUpdating will stop Excel displaying what it does, the DisplayAlerts will suppress any Dialog boxes that might pop up (such as the "Do you want to save changes ..."dialog box). For all alerts Excel will assume it should take the default answer (in the case of "Do you want to .."the default answer's YES)

I also find it useful to use the statusbar to display progress: all you need to do is to use

Application.Statusbar = 'Processing file " & p_sFileArray(l_iFileCounter)

or
[code[ Application.StatusBar = "Processing file " & l_iFileCounter & " of " & Ubound(p_sFileArray) " files."[/code]
That way you can keep an eye on progress - the Statusbar´s the only object that WILL update when ScreenUpdating is set to FALSE

Now you will not be able to use this instance of Excel while it's running the macro (you CAN always break into the code ...) but you CAN open another instance and work as per usual, leaving your macro to run quietly in the background.


PLZ NOTE: If there´s an error or if you break into the macro execution manually, DO NOT forget to set the ScreenUpdating, DisplayAlerts & Statusbar options back to FALSE. If you forget - exit Excel ans reopen - this will reset these options automatically


HTH! If not, plz reply

Cheers,
Nikki

 

[RobBroekhuis]

Nikki's cautionary note is a good one, especially for displayalerts. For ScreenUpdating it's not really necessary - this gets set back to TRUE when the code stops executing.
Rob

 

[cramd]

Nikki & Rob,
Thanks for the tips. Nikki's helpful code worked great and also thanks for the status bar tip. This will be useful in many applications!

Diane

 

[RobBroekhuis]

Diane,
Nikki took some time to really help you. Consider awarding him a star - that's TekTips' way to say thanks
Rob

 

[RobBroekhuis]

Oops - I spoke too early. And I'm not deserving of a star for my tiny contribution to the thread. But thanks anyway ;-)
Rob

 

[cramd]

Sorry, actually I realized after I posted the star that I was on the wrong post--OOPS, --but oh well, I'm sure you deserve it!
Diane

 

[Nikita6003]

All I can say - \Rob;'s posts have been really useful to me - he's worth his weight in starts ...

Cheers - and thanks for the star Diane!
Nikki ;-)