Save as Macro Help needed

[aweight]

Hi, Any advice for a very frustrated excel user?

I want to create a macro that will do the following:

Take the workbook that is open, lets say its called "My workbook" and save it with a string from a cell in one of the sheets in that work book, lets say the cell is D12 and the sheet is "Client" and for example the string in that cell is Andy

So that the result is:

C:My DocumentsMy workbook Andy.xls

Hope this makes sense, basically I want to save different versions of this workbook with the client name included in the filename.

Thanks

 

[NikNik]

Try this, setting the folder specified in the strfilename variable to whatever you need:

Sub SaveIt()

Dim strFileName As String

strFileName = "c:My DocumentsMy workbook" & Range("D12").Value

ActiveWorkbook.SaveAs Filename:=strFileName, FileFormat:=xlNormal

End Sub

 

[NikNik]

Just read the request again - to get the current workbook in the name as well try setting strfilename as follows:

strFileName = ThisWorkbook.Path & "" & Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " " & Range("D12").Value

 

[Ekim]

Slight amendment to the previous macro:

Sub SaveIt()

'Set the directory to save the files
dPath = "C:My Documentstemp"

Dim strFileName As String

With ThisWorkbook
strFileName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & " _ " & Range("D12").Value

'Save file
'Delete the macro button from client's copy
ActiveSheet.Shapes("Button 1").Select
Selection.Cut

.SaveAs dPath & strFileName

End With
End sub

Notes:

1. See the third line - change the path to where you wish to save your files. Include the apostrophes and the last backslash (…temp").

2. The macro adds an under-score between the file name and the client's name e.g. YourFileName_Andy. If you don't like this then amend it or delete it from the 6th line:

&" _" &

3. The macro is designed to be run from a button (from the Forms menu). The following code will delete the button from the client's copy (don't want to confuse clients with a button). If you do not intend to use a button, then delete the following lines from the macro (the macro will hang otherwise because it will try to delete something that doesn't exist).

' Delete the macro button from client's copy
ActiveSheet.Shapes("Button 1").Select
selection.Cut

4. Note that above code refers to "Button 1" - make sure that the button that you use also refers to that name (otherwise change the code to "Button 2" or whatever).

There is probably a better way of doing what you are trying to achieve. I will shortly post another macro (in this thread).

Edit:
For some reason, this Board adds another backslash to directory references - there is only one backslash.


Regards,

Mike
This message was edited by Ekim on 2002-08-28 13:44