Please help with cut&paste to "Save As" problem!!

[Ron M.]

I have a complex spreadsheet that several people use. Some of the data
in the spreadsheet includes (with example values):

Region 15
District 345345
City Abilene
Budget # 1

When the user saves the spreadsheet, he is required to use all four of
these in the filename. In this case, it would be (15)345345Abilene_1.

This is somewhat awkward to type (they have hundreds of them to do), so
I used a formula to catenate all four of these cells into one. The data
in that cell reads exactly like the file name should:
"(15)345345Abilene_1."

My idea was to save them the trouble of typing in all that garbage.
They could just select this cell, hit "Save As" and paste it into the
filename box in the "Save As" window. The problem is, when they paste
it in, it adds a bunch of spaces at the end, before the ".xls." It
looks like:

(15)345345Abilene_1 .xls.

Can somebody PLEASE tell me how to prevent this? I've tried everything
short of killing my cat...(-;

For what it's worth, some of this data is in merged cells, including
the filename cell. I tried unmerging all of them and it had no effect.

Ron M.

 

[Guest]

You don't say what your formula is, but could you not add ".xls" to the end
and store that in the cell?

 

[Ron M.]

I tried that. It doesn't work. It pastes into the Filename box (in the
Save As window) as:

(15)45345Abilene_1.xls .xls

Here's the formula I'm using, by the way. The data is in B1, D1, H1 and
T1. Just a simple ()&()&()&().... :

=("(")&(B1)&(")")&(D1)&(H1)&("_")&(T1)

What I'd REALLY like to have is just a button, where the user could
click it, and it would do a Save As with this filename, then close the
file, but I hate to beg.

Thanks a bunch,
Ron M.

 

[Guest]

Here you go then:-

Sub Macro1()
fname = "(" & Range("B1") & ")" & Range("D1") & Range("H1") & "_" &
Range("T1") & ".xls"
pathname = "C:\Documents and Settings\" ' Substitute your pathname here
ActiveWorkbook.SaveAs Filename:=pathname & fname, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

P.S. How's the cat?

 

[Dave Peterson]

Maybe your concatenation formula is screwed up--or maybe that Budget # field
contains a bunch of spaces following the 1.

=trim("(" & a1 & ")" & b1 & c1 & d1)

might get you closer.

If you had a region of 1 and wanted to save it with 2 digits (01), you could
use:

=trim("(" & text(a1,"00") & ")" & text(b1,"000000") & c1 & d1)

(I included the district as 6 digits.)

 

[Ron M.]

Crap. Thanks, Bigwheel, but it doesn't work. Turns out people have
different paths. Also, the file is saved on a remote server, not on
their desktops. Some have to manually log in to that server. So I need
some way to just cut and paste that cell's string into the SaveAs
filename box without it sticking in all those spaces. Normally, you can
double-click on a cell and select only its contents, but if there's a
formula in the cell, the contents change to the formula when you
double-click on it.

Any help would be appreciated. The cat's still alive, but he's looking
at me funny.... (-;

Ron M.

 

[Guest]

Is it possible to replace the pathname with
"\\servername\sharename\foldername\" or something similar? The only other
thing I can think of is to copy the cell with the formula, and Paste Special,
Values to another cell then use that cell to cut and paste into the Save As
filename box

 

[Ron M.]

Have any of you tried doing this?

Ron M.

 

[Dave Peterson]

yep.

I had to edit the cell with the formula, hit F9 copy from the formula bar. Then
I hit escape to not save my change.

Then I pasted into the file|saveAs dialog.

What didn't work when you tried it?

I was kind of amazed that your explanation on how to copy the cell with the
formula even came close to working when you pasted it into the file|saveAs
dialog.

But you said it did.