Making Excel generate Access-Like Reports

Hi Julie
For some unknown reason I cannot find your last message
to me or my last message to you. My problem with the
code was using "NewInput" instead of "New Input" which
was the correct name of my worksheet.
Perhaps those messages go somewhere else after so many
entries in a newsgroup regarding the same subject?
Hope this gets to you.
Vince
-----Original Message-----

wrote in message
...

-----Original Message-----

"VJ7777" wrote
in
message
...

-----Original Message-----
Hi Vince

you can also put code against the THISWORKBOOK
object
on
the workbook_open
event (double click on ThisWorkbook in the VBE
window,
choose Workbook from
the lhs drop down & Open from the right),
you'll need to edit the code to:

If IsEmpty(Sheets("NewInput").Range("B2")) then
Sheets("NewInput").Range("B2").Value = Format
(Now, "dd mmmm yyyy")
End If

hope this helps
Cheers
JulieD


"VJ7777"
wrote
in
message
...

-----Original Message-----
Hi Vince

to place the code in the workbook, right mouse
click
on
any sheet tab and
choose view code. This will display the VB
Editor ...
on the top left there
should be a little area with folders & sheets &
the
name
of your workbook in
bold - if not, choose View / Project Explorer
from
the
menu.

under the project with your workbook name in
bold,
you
will see "NewInput"
and the rest of your sheets - double click on
the
NewInput one, and a white
piece of paper should appear on the right hand
side of
the screen , up the
top are two drop down boxes, choose
worksheet
from the lhs one and Activate from the right
then the words

Private Sub Worksheet_Activate()
End Sub

should appear .. place my code between these two
lines.
Change the A1 to B2
You can "switch" between the code window & your
workbook
by using Alt & F1l
.... switch back, ensure B2 on the NewInput
sheet
has
nothing in it, switch
to another sheet, come back to the NewInput
sheet
the
date should be filled
in. Now you can test out both bits of code i
gave
you
to decide which one
is the one you want.

(by the way, it is always a good idea to make a
backup
testing any code etc)

With regards to your other questions, i'm still
having a
look at them, but
i've got a few other things on at the moment and
havent'
really had time to
sit down & think about them properly. If you
haven't
done so already you
might like to do a search of google
(groups.google.com -
advanced search:
search string changing workbook names
formulas ...
search groups
microsoft.public.excel* ), i would do it for you
and
see
if i come up with
anything but my internet explorer's not working
at
the
moment

Hope this helps
Cheers
JulieD


wrote in
message
...

-----Original Message-----
Hi

can't help (yet) with the other questions but
4. Finally, on another subject, in a cell,
the
formula "=Today()" enters today's date in a
cell
automatically. Is there a way enter
today's
date
automatically the first time a worksheet
in a
workbook
is
handled but this date is never changed no
matter
how
many
times the worksheet is modified?

you can put code against each sheet (itself)
in
the
vbe
window similar to
Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell
address
as
required
Range("A1").Value = Now
End If
End Sub

or

Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell
address
as
required
Range("A1").Value = Format(Now, "dd mmmm
yyyy")
End If
End Sub

Hope this helps
Cheers
JulieD



"VJ7777"

wrote
in
message
news:243901c48de1$92864e90
...
Please forgive me if I don't use proper
Newsgroup
protocol. This is my first time.

I have developed a comprehensive dealership
system
using
Excel. I probably should have used Access
but I
don't
want to redo the system yet. Everything
works
swimmingly
except:

1. I use customer name to name each
workbook
(which
contains multiple worksheets of forms and
data
pertaining
to that customer):

For example: "Jones," "Smith"
(or "Smith.John"),
etc.

I have created reports which have a row for
each
customer
and ten or more columns of data for each
customer.
I
get
the info for each column from the customer
workbook
and
worksheet using formulas like the one
illustrated
below
(ignore the complexity of the formula; I
merely
want to
automatically substitute "Smith"
for "Jones"
from a
separate master list of customer names):

Prospective Business Report (Sample Column
Headings):
Name Product PurchaseDate Cost Sell
etc.,
etc.

Example of one of many formulas:

IF('[Jones.xls]Buyer Progress Record'!
$B$12="failed","F",IF('[Jones.xls]Buyer
Progress
Record'!
$B$8="Cash Sale",'[Jones.xls]Buyer Progress
Record'!
$B$8,IF('[Jones.xls]Buyer Progress Record'!
$B$11" ?","$"&'[Jones.xls]Buyer
Progress
Record'!$B$11,IF('[Jones.xls]Buyer Progress
Record'!
$B$10=" ?","?",IF('[Jones.xls]
Buyer
Progress
Record'!
$B$10$AA$2,"! ! ! ! !",'[Jones.xls]
Buyer
Progress Record'!$B$10)))))

Everything works well so long as I copy
down
and
replace
(i.e. replace "Jones" with "Smith") to add
a
new
customer
row to the report. I cannot find a way to
use a
master
list of customer names to automatically
change "Jones"
to "Smith" to "Green," etc.

2. I would like to produce a Contact
Report
by
Salesperson (using the above mentioned
master
list
of
customer names) except that there would be
multiple
rows
for each customer. The source, Buyer
Progress
Record
worksheet, contains up to 40 lines of
contacts in
Rows
28
through 68 and, perhaps, only one or two
rows
might
contain data at any moment in time (Row 28
and
Row
29;
the remaining rows will be used as time
marches
on).
The
desired report would look like this and
only
print
rows
for each customer that contain data:

Contact Report - Salesperson Murphy -
Aug
17,
2004

Customer Name "Jones"
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description

Customer Name "Smith"
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description

Customer Name "Green"
Date of Contact Date of Next Contact
Description

3. It would be nice to be able to add to
the
master
list
of customer names automatically by having
the
computer
look to see if any new customer workbooks
have
been
added
to the "Customers 2004" folder each day.

4. Finally, on another subject, in a cell,
the
formula "=Today()" enters today's date in a
cell
automatically. Is there a way enter
today's
date
automatically the first time a worksheet
in a
workbook
is
handled but this date is never changed no
matter
how
many
times the worksheet is modified?

Thank you in advance for any help you can
render.



Hi, Julie:
Thank you , very much for your response. I
must
confess
complete lack of knowledge on how to start
implementing
your code. I have been in the computer
industry
since
the beginning (about th
etimeofEniac.Itaught
myself how to read and modify Fortran code
when I
was
product manager of a capital investment
evaluation
model
for Fortune 500 companies using Computer
Sciences'
time
sharing system. I taught myself how to
program
in
Basic
when I had the first Apple computer and, for
fun,
designed my own General Ledger system. On the
other
hand, I have pulled out the Visual Basic book
multiple
times and, as I did tonight, asked myself why
I
should
punish myself when Microsoft's explanation
never
seems
to
explain a place to begin.

If it isn't asking too much, could you please
tell
me
what steps to take to simply make your code
work
in
cell "B2" of worksheet "New Input" of
workbook "aaCustomer." Maybe, if you do
that, I
can
get
the concept of how to begin to use VB -
certainly I
have
never gotten a clue from the Microsoft VB
book.

Your message seemed to indicate that you might
have
answers to my other questions - and I suspect
VB
will
be
a major part of your answer. If so, I will
need
to
know
how to intergrate them into Excel; your
response
regarding to how to implement this will be
very
necessary
to understanding your other solutions.
Thanks again,
Vince



.Julie, thanks a million. I'll try the google
search
later today. One quick question: The date
appears
OK
after I switch to another worksheet and back
again,
but,
suppose one doesn't go to another worksheet in
today's
data entry session; how does today's date get
into
the
cell. I've tried saving the workbook and re-
opening it
but the cell remains blank unless I switch to
another
worksheet and back again
Again, I really appreciate your help!!!
Vince




.Hi Julie:
I'm afraid I am asking too much of you - it isn't
your
task in life to train me in VB. I scan the book
and it
seems to avoid simple tasks. I've tried Google for
a
list of commands (for example: goto - or moveto,
etc. -
so I can say 'if whatever, goto "a1")' and cannot
find
such a list. If you point me in the right direction
I'll
go there and study. Having started life as a
bookkeeping
machine salesperson I am strong on applications and
look
to programming as a list of simple commands to copy
into
a program to get my job done.

Having said that, the code you gave me
generated "Run-
time error '9': Subscript out of range." Here is
the
code I entered:
Private Sub Workbook_Open()
If IsEmpty(Sheets("NewInput").Range("B2")) Then
Sheets("NewInput").Range("B2").Value = Format
(Now, "dd
mmmm yyyy")
End If
End Sub

If you will help one more time with the above
questions
I'll go it alone from that point. But will hope you
can
solve the first three questions in my original
posting
when you get the time. (I'll do the Google search
you
recommended before or during the Labor Day weekend.)

Again, thank you very much.
Vince



Hi Vince

i'm happy to help so don't worry about asking
questions ..

have you got a sheet in the workbook called "NewInput"
as the code runs fine
when i copy & paste it into a new workbook with a
sheet
called NewInput (no
spaces) ...
is the code in the THISWORKBOOK object of the project
explorer and not in a
module sheet
is the format stuff all on one line?

as to the other 3 questions i will get to them when i
get a chance.

Looking forward to hearing from you.

Cheers
JulieD


.Hi Julie
Please forgive my stupidity. While shaving I realized
my
worksheet is New_Input; not, NewInput. I made the
change
and it worked fine.

Do you know a source for VB commands such as "goto"?

Thanks again.
Vince


Hi Vince

glad its working .. basically the way you need to
approach VBA is to ask
yourself what object am i looking at and what do i want
to do with it, so if
it's a cell (e.g. A1), then you need to know that cells
are part of the
range object and that range objects have a select
method, therefore to
select a cell you can use
Range("A1").select

however, if you want to select a cell on another sheet
to need to make the
sheet active & then select the cell
Sheets("Sheet1").activate
Range("A1").select

however, you can populate a cell without selecting it
Sheets("Sheet1").Range("A1").value = 5
will put the number 5 into cell A1 on Sheet1

the best place to find all this out is using the Object
Browser in VBA (view
/ object browser) or type "microsoft excel object model"
into Help in the
VBE .. this will give you all the objects you can then
click on them to find
out their properties & methods.

Hope this helps
Cheers
JulieD


.

"VJ7777" wrote in message
...
Hi Julie
For some unknown reason I cannot find your last message
to me or my last message to you. My problem with the
code was using "NewInput" instead of "New Input" which
was the correct name of my worksheet.
Perhaps those messages go somewhere else after so many
entries in a newsgroup regarding the same subject?
Hope this gets to you.
Vince

i can still see them, here's my response to your last post:

.Hi Julie
Please forgive my stupidity. While shaving I realized my
worksheet is New_Input; not, NewInput. I made the change
and it worked fine.

Do you know a source for VB commands such as "goto"?

Thanks again.
Vince


Hi Vince

glad its working .. basically the way you need to approach VBA is to ask
yourself what object am i looking at and what do i want to do with it, so if
it's a cell (e.g. A1), then you need to know that cells are part of the
range object and that range objects have a select method, therefore to
select a cell you can use
Range("A1").select

however, if you want to select a cell on another sheet to need to make the
sheet active & then select the cell
Sheets("Sheet1").activate
Range("A1").select

however, you can populate a cell without selecting it
Sheets("Sheet1").Range("A1").value = 5
will put the number 5 into cell A1 on Sheet1

the best place to find all this out is using the Object Browser in VBA (view
/ object browser) or type "microsoft excel object model" into Help in the
VBE .. this will give you all the objects you can then click on them to find
out their properties & methods.

Hope this helps
Cheers
JulieD

-----Original Message-----

"VJ7777" wrote in
message
...
Hi Julie
For some unknown reason I cannot find your last message
to me or my last message to you. My problem with the
code was using "NewInput" instead of "New Input" which
was the correct name of my worksheet.
Perhaps those messages go somewhere else after so many
entries in a newsgroup regarding the same subject?
Hope this gets to you.
Vince

i can still see them, here's my response to your last
post:

.Hi Julie
Please forgive my stupidity. While shaving I realized
my
worksheet is New_Input; not, NewInput. I made the
change
and it worked fine.

Do you know a source for VB commands such as "goto"?

Thanks again.
Vince


Hi Vince

glad its working .. basically the way you need to
approach VBA is to ask
yourself what object am i looking at and what do i want
to do with it, so if
it's a cell (e.g. A1), then you need to know that cells
are part of the
range object and that range objects have a select
method, therefore to
select a cell you can use
Range("A1").select

however, if you want to select a cell on another sheet
to need to make the
sheet active & then select the cell
Sheets("Sheet1").activate
Range("A1").select

however, you can populate a cell without selecting it
Sheets("Sheet1").Range("A1").value = 5
will put the number 5 into cell A1 on Sheet1

the best place to find all this out is using the Object
Browser in VBA (view
/ object browser) or type "microsoft excel object model"
into Help in the
VBE .. this will give you all the objects you can then
click on them to find
out their properties & methods.

Hope this helps
Cheers
JulieD



.
Hi Julie
I haven't tried your last advice because I got a pinched
nerve and couldn't sit down for a number of days. I
could use my laptop by standing up at the kitchen counter
which killed my back but I don't have Internet access in
the kitchen. I won't be able to get back to the Excel
project for several more days.
I don't want to lose touch with you. Is there a way to
move our correspondence forward or is there a quick way
to get to what is now page 40 or so? And, is there a
quick way to know when you respond to my questions?
I do appreciate your help.
Vince

Hi Vince

ouch .. .had one of those myself a few years back - not good ...

it might be easier if you email just me direct - julied_ng at hcts dot net
dot au

Cheers
JulieD

"VJ7777" wrote in message
...

-----Original Message-----

"VJ7777" wrote in
message
...
Hi Julie
For some unknown reason I cannot find your last message
to me or my last message to you. My problem with the
code was using "NewInput" instead of "New Input" which
was the correct name of my worksheet.
Perhaps those messages go somewhere else after so many
entries in a newsgroup regarding the same subject?
Hope this gets to you.
Vince

i can still see them, here's my response to your last
post:

.Hi Julie
Please forgive my stupidity. While shaving I realized
my
worksheet is New_Input; not, NewInput. I made the
change
and it worked fine.

Do you know a source for VB commands such as "goto"?

Thanks again.
Vince


Hi Vince

glad its working .. basically the way you need to
approach VBA is to ask
yourself what object am i looking at and what do i want
to do with it, so if
it's a cell (e.g. A1), then you need to know that cells
are part of the
range object and that range objects have a select
method, therefore to
select a cell you can use
Range("A1").select

however, if you want to select a cell on another sheet
to need to make the
sheet active & then select the cell
Sheets("Sheet1").activate
Range("A1").select

however, you can populate a cell without selecting it
Sheets("Sheet1").Range("A1").value = 5
will put the number 5 into cell A1 on Sheet1

the best place to find all this out is using the Object
Browser in VBA (view
/ object browser) or type "microsoft excel object model"
into Help in the
VBE .. this will give you all the objects you can then
click on them to find
out their properties & methods.

Hope this helps
Cheers
JulieD



.
Hi Julie
I haven't tried your last advice because I got a pinched
nerve and couldn't sit down for a number of days. I
could use my laptop by standing up at the kitchen counter
which killed my back but I don't have Internet access in
the kitchen. I won't be able to get back to the Excel
project for several more days.
I don't want to lose touch with you. Is there a way to
move our correspondence forward or is there a quick way
to get to what is now page 40 or so? And, is there a
quick way to know when you respond to my questions?
I do appreciate your help.
Vince

Hi Julie
Color me stupid. I've tried the following e-mail
addresses and they don't work:



Where did I go wrong?
Thanks again,
Vince
-----Original Message-----
Hi Vince

ouch .. .had one of those myself a few years back - not
good ...

it might be easier if you email just me direct -
julied_ng at hcts dot net
dot au

Cheers
JulieD

"VJ7777" wrote in
message
...

-----Original Message-----

"VJ7777" wrote
in
message
...
Hi Julie
For some unknown reason I cannot find your last
message
to me or my last message to you. My problem with
the
code was using "NewInput" instead of "New Input"
which
was the correct name of my worksheet.
Perhaps those messages go somewhere else after so
many
entries in a newsgroup regarding the same subject?
Hope this gets to you.
Vince

i can still see them, here's my response to your last
post:

.Hi Julie
Please forgive my stupidity. While shaving I
realized
my
worksheet is New_Input; not, NewInput. I made the
change
and it worked fine.

Do you know a source for VB commands such as "goto"?

Thanks again.
Vince


Hi Vince

glad its working .. basically the way you need to
approach VBA is to ask
yourself what object am i looking at and what do i
want
to do with it, so if
it's a cell (e.g. A1), then you need to know that
cells
are part of the
range object and that range objects have a select
method, therefore to
select a cell you can use
Range("A1").select

however, if you want to select a cell on another sheet
to need to make the
sheet active & then select the cell
Sheets("Sheet1").activate
Range("A1").select

however, you can populate a cell without selecting it
Sheets("Sheet1").Range("A1").value = 5
will put the number 5 into cell A1 on Sheet1

the best place to find all this out is using the
Object
Browser in VBA (view
/ object browser) or type "microsoft excel object
model"
into Help in the
VBE .. this will give you all the objects you can then
click on them to find
out their properties & methods.

Hope this helps
Cheers
JulieD



.
Hi Julie
I haven't tried your last advice because I got a
pinched
nerve and couldn't sit down for a number of days. I
could use my laptop by standing up at the kitchen
counter
which killed my back but I don't have Internet access
in
the kitchen. I won't be able to get back to the Excel
project for several more days.
I don't want to lose touch with you. Is there a way to
move our correspondence forward or is there a quick way
to get to what is now page 40 or so? And, is there a
quick way to know when you respond to my questions?
I do appreciate your help.
Vince


.

Hi Vince

both of those should have worked fine ... try again ... if they still don't
work try
hartley at techie dot com

Cheers
JulieD

--snip -- as i would like to limit the number of posts with my email address
on them due to spam