Prev: Extracting Data from Table
Next: Comparing Time in a Formula
From: Tony on 23 Mar 2010 09:44
I am trying to use a command button to save a spreadsheet using cell values
as the file name from the tab Master. For example I want to use cells B2
(Tony.Dungate) and F2 (2010.March) to create a file name of
Tony.Dungate.2010.March.xls. Essentialy I would need something like :

Master!B2&.&Master!G2&".xls"

Can someone advise me on the correct code to do this. I have been trying to
use the following without success:


Const sRoot As String = "V:\Database Logs\"

With ActiveWorkbook
.SaveAs Filename:=sRoot & _
.Worksheets("Master").Range("B2").Text, _
FileFormat:=xlExcel8
.Close
From: Steve Jones on 23 Mar 2010 10:59

In your spreadsheet can you enter in a cell say A1 or appropriate.

=B2& " "&F2 (this will result in "Tony Dungate 2010.March")

Then create the following sub.


Sub filename()

Dim Name As String
Range("A1").Select
Name = ActiveCell
ActiveWorkbook.SaveAs filename:="V:\Database Logs\" & Name & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub


You may well get some better constructed answers than this.











"Tony" <Tony(a)discussions.microsoft.com> wrote in message
news:6FF0F0E0-BA2C-463F-B003-AA7AFCEC747E(a)microsoft.com...
>I am trying to use a command button to save a spreadsheet using cell values
> as the file name from the tab Master. For example I want to use cells B2
> (Tony.Dungate) and F2 (2010.March) to create a file name of
> Tony.Dungate.2010.March.xls. Essentialy I would need something like :
>
> Master!B2&.&Master!G2&".xls"
>
> Can someone advise me on the correct code to do this. I have been trying
> to
> use the following without success:
>
>
> Const sRoot As String = "V:\Database Logs\"
>
> With ActiveWorkbook
> .SaveAs Filename:=sRoot & _
> .Worksheets("Master").Range("B2").Text, _
> FileFormat:=xlExcel8
> .Close


 | 
Pages: 1
Prev: Extracting Data from Table
Next: Comparing Time in a Formula