Question on overall approach to an Excel macro

[battists]

Hi there. I'm involved in the maintenance of a human resource system.
was able to obtain from someone else an Excel spreadsheet that use
Macros to parse through some text files and return a chart of how man
users were logged into the system at any given time. (The macro
actually come up with a lot of other information that I don't need.)

The way it works today, you enter a date, and select a server from
drop-down list. Then you click a button that runs the macros an
generates an Excel spreadsheet for that day. This is very cumbersom
for me to use, since I have to run the program once for each serve
that I maintain, each day. If I fall a few days behind, that adds u
rapidly.

I'm looking for a way to re-write the macros so that instead, I coul
enter a start date and end date, and also a list of servers instead o
just one. Then, when I click the button, the program would loop throug
server #1 for each date in the range, creating one spreadsheet per day
Then, when it had finished going through the range of dates, it woul
go to the next server in the list, and repeat until complete.

Unfortunately, I only know enough VBA to understand what the macros ar
doing, and I know that the approach is everything in rewriting this.

Do you have any recommendations for how to approach this? at thi
point, I'm really most interested in an overall framework like "Use a
array for X, and then loop through something or other".

I'd be more than happy to post the code that's in the macros today, bu
I thought I'd start with the general topic first. Incidentally,
believe these macros were originally written back in Excel 97, but I'
now on Office 2003.

Thanks,

Stev

 

[Bob Phillips]

I would store all of the servers in a range on a worksheet, and the dates in
another range. You can use worksheet formulae to handle the dates, rather
than VBA, as it is so simple. For instance, let's assume that you are only
interested in weekdays. Enter your start date in B1 say, and then in B2, use
=B1+CHOOSE(WEEKDAY(B1),1,1,1,1,1,3,2) and drag it down to make sure that you
never get a weekend date. Chop off any dates you don't want.

Then loop around the servers and the dates to process all of the files, like
this

cServers = Cells(Rows.Count,"A").End(xlUp).Row 'number of servers
cDates = Cells(Rows.Count,"B").End(xlUp).Row ' number of dates

For i = 1 to cServers
'process server name is in Cells(i,"A").Value
For j = 1 to cDates
'processs dates - in Cells(j,"B").Value
'do yourv stuff
Next j
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

I would assume the macro uses a variable to hold the server name and the
date.

sName = cboServeList.Value
dtval = txtData.Text

so you would remove lines like that and put the code inside a loop

Dim srvr as variant
Dim dtStart as Date, dtEnd as Date
srvr = Array( "Server1", _
"Server2", _
"Server3")

dtStart = #03/21/2004#
dtEnd = #03/25/2004#

for i = lbound(srvr) to ubound(srvr)
for j = clng(dtStart) to clng(dtEnd)
sName = srvr(i)
dtval = cdate(j)

' exisiting modified code

Next
Next