About Us

VJ7777

"Myrna Larson" wrote:

The question ISN'T what should the NEW file name be. It's what is the OLD file
name that is to be replaced.

The cell on the worksheet, in column F, says the old *last name* is Garcia and
therefore old *file name* is SUPPOSED TO BE Garcia. The trouble is, it ISN'T
Garcia. It's GARCIAL. How is the macro supposed to figure that out? How would
the macro know that in *this* case it should add an L to the last name to get
the file name?

If you KNOW what the old file name is, then you need two input boxes, one to
ask the user for the old name, the other to request the new name.

As far as you being notified of responses, I normally post only the the
newsgroup. I don't send an email copy because most people don't use a real
address because of the spam problem. You really need to keep checking the
group for a reply.

I've sent a copy of this to the address . I
don't know that will matter or nt.

On Mon, 31 Jan 2005 20:11:02 -0800, VJ7777
wrote:

"Myrna Larson" wrote:

The fundamental problem is that when the macro was written, the 2
specifications were that (1) the person's last name could be found to the
right of the 1st space in the text in column F on the 1st sheet, and in
column
B on the 2nd sheet, and (2) the workbook name consisted of that last name
followed immediately by ".xls"

Then you hit a problem: that naming scheme can no longer work because you
have
two people with the same last name. When that occurred, (1) the file was
created and named "GarciaL.xls" (you haven't said how the name is
assigned).
(2) But in the workbook, BPR!A1 gives the last name, Garcia, not the
filename
GarciaL.

So the macro can no longer work because the 2nd assumption -- that the file
name is always the last name + .xls -- has been violated. It sounds like
you
need TWO cells on the worksheet, one containing the client's name, the
other
the base file name.

If you set up a 2nd cell for the file name, then you would need to do that
for
*all* existing workbooks. Then you have to change the formula in column F
to
look at the cell containing the file name instead of cell $A$1. The 2nd
macro
has to be changed to look for the file name not in column B, but in a new
column.

BTW, you *DO* need to resolve the underlying problem here. You could just
manually fix the worksheet names in the formulas in the rows below the
Garcia
row. But the problem will recur when (a) you have another duplicate client
name, or (b) you try to insert a new client after Garcia.

the example I gave of Garcia.Lettie; then GarciaL, is really what
happened.
The formula in Column F is:
...&" "&LEFT('\\Server1\Daily 2005\[Garcial.xls]BPR'!$A$1,9)

Hi, Myrna:
Can you tell me what I am doing wrong in dealing with this discussion group.
I can't seem to get in sync with your replies. I keep checking the "notify
me of replies box" but never get notified and twice Microsoft has a problem
with my input so it loses it.

But, back to the macro, I did not specify that the file name must be the
same as the customer name. That was an assumption of the person who wrote
the macro. Quite simply, all I want to do is change the file name used in
the formulae on the previous row to the file name entered in the Name box.
Again, thank you for your continued assistance.
Vince

Aha! The light finally dawns. I now understand what you have been telling me. How could the macro be changed so the file names could be entered in a separate column (say Column A) which would not be printed with the report? Therefore the OLD file name would always be whatever is in the row above; the NEW file name would be typed in Column A on the subject row. Then anything could be printed in Column F (which would now be Column G). This is really what I wanted to do in the first place. I could use that macro in a dozen easily programmed future new/different reports.

Apparently this macro merely copies across the row until it comes to a blank
cell - this is what I really need. There are a number of occasions where I'd
like to create and run a one-time report looking at one, two, or more pieces
of data in each customer file.

I'd really appreciate it if you could modify the macro to do just that.
Best regards,
Vince

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Is my file corrupted?	Kevin	Excel Discussion (Misc queries)	3	March 16th 10 01:28 PM
Import from SAP corrupted	JJSEE	New Users to Excel	1	August 11th 09 09:46 PM
Corrupted file	poul, denmark	Excel Discussion (Misc queries)	2	April 24th 05 08:07 PM
Corrupted File	trini	Excel Discussion (Misc queries)	0	March 12th 05 02:07 AM
VBA --- Corrupted xls file	Jag Man	Excel Programming	4	January 3rd 04 07:35 PM

Menu

About Us