Enter numbers in closed workbook

[Daniel Bonallack]

I have provided users with a workbook with twenty macros (let's call this
macro.xls).

I would like to understand which macros are being used, and how frequently.
So I have an Excel file on the server called Summary.xls (let's say it is
under L:\Excel Tools\Summary.xls)

In column A, I have "Macro 1", "Macro 2", "Macro 3" etc
In column B, I would like to increment the number upwards by 1, each time
the user runs the relevant macro to completion.

What line of code would I put at the completion of each of my twenty macros,
so that the relevant cell in the closed Excel file increments upwards by one?

Something like (?):
x = ' my relevant macro row in the closed book
z = Workbooks("L:\Excel Tools\Summary.xls").sheets("Sheet1").cells(x,2).value
Workbooks("L:\Excel Tools\Summary.xls").sheets("Sheet1").cells(x,2).value =
z + 1

This didn't work...

Thanks in advance for any help

regards
Daniel

 

[Bob Phillips]

Daniel,

You could write to a closed workbook using ADO, but quite honestly, I would
not bother. I would just open it when Macro.xls is opened, and close it when
it is closed, and then write to it. You could even hide the workbook on
opening so as the users of the macros would not know.

All you then need is to know which macro is executing, and write to the
relevant line of the summary. You seem to have this. The code to open and
close it would be like

Private Sub Workbook_Activate()
Set oSummaryWB = Workbooks.Open(Filename:="L:\Excel
Tools\Summary.xls").)
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
oSummaryWB.Save
oSummaryWB.Close
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

You would also need to declare oSummmaryWB as a public workbook object in a
standard code module.

Of course, this all falls down if the Macros.xls will be run by several
people at the same time, as writing to the same Summary workbook will not be
possible, not without sharing it, and that is so flaky I wouldn't go that
way. In this instance, you need another solution. Is this a likelihood, or
is a single instance Macros file.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Daniel Bonallack]

Hi Bob,

I was hoping you would answer!

The template (macro.xls) will be used by our teams in London, NY and Sydney.
The three teams share a server, but as opening the 4Mg file from the server
will be too slow for two of the three regions, each team will have a copy on
their local server. I was hoping that the small summary.xls file could live
on the common server, and so even if all three instances of macro.xls were
open at once, they could still write to the closed summary.xls file on the
common server.

However, I could take your suggestion, but also have three copies of
Summary.xls living on the local server in each region, then have an
additional procedure to gather the data from these three files. But then I
have the additional problem - as macro.xls is solely used for crunching data
to new files, it's designed to be opened by several people at once in one
region, and as such, the process breaks down as per your response.

So I'm a bit stuck. If you have any other thoughts, I would be delighted to
hear them!

Or maybe it would be OK - the process of opening and closing summary.xls
must be pretty quick, so I would have to be unlucky to have a conflict?


regards
Daniel

 

[Bob Phillips]

Hi Daniel,

Thoughts inline


(replace somewhere in email address with gmail if mailing direct)

The template (macro.xls) will be used by our teams in London, NY and Sydney.
The three teams share a server, but as opening the 4Mg file from the server
will be too slow for two of the three regions, each team will have a copy on
their local server. I was hoping that the small summary.xls file could live
on the common server, and so even if all three instances of macro.xls were
open at once, they could still write to the closed summary.xls file on the
common server.

I thought that might be the case.

However, I could take your suggestion, but also have three copies of
Summary.xls living on the local server in each region, then have an
additional procedure to gather the data from these three files. But then I
have the additional problem - as macro.xls is solely used for crunching data
to new files, it's designed to be opened by several people at once in one
region, and as such, the process breaks down as per your response.

You have a number of options, it all depends upon the importance of the data
and how far you want to go.

The simplest way IMO would be to aggregate the information in an array or a
collection as the macro.xls file is being used, and then upon closing, write
the data away. The techniques to write the data away could be to a file
(even a text file) or a workbook, or a database.

another choice is to have each person writing to a file, either locally or
even centrally, unique to each user in a specific directory, and then have
another process that sweeps through the data files to compile a master
report.

Or, along the same lines, but better IMO would be to hold the data in a
database and write new data to a table there. Using ADO you can easily write
to the database, in fact a nice class module would do the trick nicely.

Or maybe it would be OK - the process of opening and closing summary.xls
must be pretty quick, so I would have to be unlucky to have a conflict?

It might do, but there is always the chance that it might fail, is that
important, or just inconvenient? You could just add some could to see
whether the workbook has been opened read-only (that is someone else already
has it), and if so, wait and try again. You would hold a retry count so that
it doesn't go on forever in case there is a problem, and if the retry count
is reached, you could just give up, or maybe even dump the data to a
log-file which you could manually collate.

As I said, you have a number of options, it just depends upon how far you
want to go, ease of development or completeness of solution.

Let me know if you want any help with whichever way that you decide to go.

BTW, why don't you make Macros.xls an add-in? I take it is only used to give
access to your set of macros.

 

[Daniel Bonallack]

Thanks Bob. I'd like to go with the "easiest" method, seeing as this is more
of interest to me than anyone else, so I like the idea of checking read-only
then trying again.

As for an addin - that's a good idea. I initially had a couple of
objections (there are 20 hidden template sheets), but I don't think there are
any real reasons why it couldn't be converted.

Thanks again.

regards
Daniel

 

[Bob Phillips]

Do you want me to give you a starter, or do you want to start it and me to
join in if you need help?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)