A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

SaveAs Cell.value=filename



 
 
Thread Tools Display Modes
  #1  
Old November 23rd 06, 12:40 PM posted to microsoft.public.excel.programming
Helmut
external usenet poster
 
Posts: 111
Default SaveAs Cell.value=filename

Hi, I am using the following statement:

Application.Dialogs(xlDialogSaveAs).Show FullFilePath, vbNormalFocus

How can I include in this statement a Filename which is in a Cell.value in
order that the user can select the appropriate directory for him/her but the
filename is fixed.
thanks
Helmut
Ads
  #2  
Old November 23rd 06, 01:49 PM posted to microsoft.public.excel.programming
Bob Phillips
external usenet poster
 
Posts: 1,726
Default SaveAs Cell.value=filename

That gets the whole filename, you need FileDialog as I showed you in the
previous thread.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Helmut" > wrote in message
...
> Hi, I am using the following statement:
>
> Application.Dialogs(xlDialogSaveAs).Show FullFilePath, vbNormalFocus
>
> How can I include in this statement a Filename which is in a Cell.value in
> order that the user can select the appropriate directory for him/her but

the
> filename is fixed.
> thanks
> Helmut



  #3  
Old November 23rd 06, 02:06 PM posted to microsoft.public.excel.programming
pmonica
external usenet poster
 
Posts: 3
Default SaveAs Cell.value=filename

Hi Helmut,

Please try the following:
In cell A1 (as example) you place the following formula:
=If(B1="Lucy";"C:\Lucy\";"")&ifIf(B1="Helmut";"C:\ Helmut\";"").... as
much varable you want
You write on cell A2 the following formula:
=A1&"FILENAME.xls"

In your VBA code, you write:

sub filename()
Dim FILENAME as string
FILENAME=range("A2").value
ActiveWorkbook.SaveAs Filename:=FILENAME
end sub

You will have your file saved with the name you pre-selected on the path of
the user.
If it doesn't work, please react.

Regards,
--
Paulo Mónica
Portugal

"Helmut" wrote:

> Hi, I am using the following statement:
>
> Application.Dialogs(xlDialogSaveAs).Show FullFilePath, vbNormalFocus
>
> How can I include in this statement a Filename which is in a Cell.value in
> order that the user can select the appropriate directory for him/her but the
> filename is fixed.
> thanks
> Helmut

  #4  
Old November 23rd 06, 02:16 PM posted to microsoft.public.excel.programming
pmonica
external usenet poster
 
Posts: 3
Default SaveAs Cell.value=filename

Sorry Helmut, there is a "IF" repeated on the 2nd variable of cell A1.
--
Paulo Mónica
Portugal

"pmonica" wrote:

> Hi Helmut,
>
> Please try the following:
> In cell A1 (as example) you place the following formula:
> =If(B1="Lucy";"C:\Lucy\";"")&ifIf(B1="Helmut";"C:\ Helmut\";"").... as
> much varable you want
> You write on cell A2 the following formula:
> =A1&"FILENAME.xls"
>
> In your VBA code, you write:
>
> sub filename()
> Dim FILENAME as string
> FILENAME=range("A2").value
> ActiveWorkbook.SaveAs Filename:=FILENAME
> end sub
>
> You will have your file saved with the name you pre-selected on the path of
> the user.
> If it doesn't work, please react.
>
> Regards,
> --
> Paulo Mónica
> Portugal
>
> "Helmut" wrote:
>
> > Hi, I am using the following statement:
> >
> > Application.Dialogs(xlDialogSaveAs).Show FullFilePath, vbNormalFocus
> >
> > How can I include in this statement a Filename which is in a Cell.value in
> > order that the user can select the appropriate directory for him/her but the
> > filename is fixed.
> > thanks
> > Helmut

  #5  
Old November 23rd 06, 03:26 PM posted to microsoft.public.excel.programming
Helmut
external usenet poster
 
Posts: 111
Default SaveAs Cell.value=filename

Hi Monica,
the following did it:


Dim SvPath As String
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = False Then Exit Sub
SvPath = .SelectedItems(1)
End With
ActiveWorkbook.SaveAs SvPath & "\" & Range("Fname")

"pmonica" wrote:

> Sorry Helmut, there is a "IF" repeated on the 2nd variable of cell A1.
> --
> Paulo Mónica
> Portugal
>
> "pmonica" wrote:
>
> > Hi Helmut,
> >
> > Please try the following:
> > In cell A1 (as example) you place the following formula:
> > =If(B1="Lucy";"C:\Lucy\";"")&ifIf(B1="Helmut";"C:\ Helmut\";"").... as
> > much varable you want
> > You write on cell A2 the following formula:
> > =A1&"FILENAME.xls"
> >
> > In your VBA code, you write:
> >
> > sub filename()
> > Dim FILENAME as string
> > FILENAME=range("A2").value
> > ActiveWorkbook.SaveAs Filename:=FILENAME
> > end sub
> >
> > You will have your file saved with the name you pre-selected on the path of
> > the user.
> > If it doesn't work, please react.
> >
> > Regards,
> > --
> > Paulo Mónica
> > Portugal
> >
> > "Helmut" wrote:
> >
> > > Hi, I am using the following statement:
> > >
> > > Application.Dialogs(xlDialogSaveAs).Show FullFilePath, vbNormalFocus
> > >
> > > How can I include in this statement a Filename which is in a Cell.value in
> > > order that the user can select the appropriate directory for him/her but the
> > > filename is fixed.
> > > thanks
> > > Helmut

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
SaveAs with a pre-defined filename Stefi Excel Programming 5 November 9th 05 03:06 PM
FileName SaveAs alanford Excel Programming 1 February 18th 05 01:01 PM
Set Saveas Filename from Cell Contents Using VBA Celtic_Avenger Excel Programming 1 September 5th 04 10:57 PM
Macro to open SaveAs... and change filename to cell value Andy Excel Programming 5 July 19th 04 12:23 PM
SaveAs Filename Help Ed[_14_] Excel Programming 2 November 20th 03 07:43 PM


All times are GMT +1. The time now is 01:01 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.