Save to a location other than default

[RMires]

I've created a button to save a file with a name created from a cell, but I
need to be able to save it to a particular location which is other than the
default location.

 

[Eduardo]

Hi,

ActiveWorkbook.Save
ActiveWorkbook.SaveAs Filename:= _
"N:\2009 Uploads\GLL\GLL 2009 TB.xls", FileFormat:=xlExcel8,
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Change the directory and name to fit your needs just need to change this
N:\2009 Uploads\GLL\GLL 2009 TB.xls

if this helps please click yes, thanks

 

[RMires]

It helps, but it's not quite that simple. I am also changing the file name at
the same time. I'm not quite sure how to connect the file location
"W:\Hebert\Operations" with the code that creates the file name
..Worksheets("OPENING STK").Range("b20").Value & ".xls"

 

[Dave Peterson]

dim myFileName as string

with activeworkbook
myfilename = "W:\Hebert\Operations\" _
& .Worksheets("OPENING STK").Range("b20").Value & ".xls"

.saveas filename:=myfilename, FileFormat:=xlWorkbookNormal
end with

 

[Chip Pearson]

Try something like the following:


Sub AAA()
Dim FolderName As String
Dim FileName As String
Dim FullName As String

FolderName = "C:\Test\" '<<< CHANGE
FileName = Worksheets("Sheet1").Range("A1").Text '<< CHANGE
If InStr(1, FileName, ".xls") = 0 Then
' add the XLS extension if it is not present
FileName = FileName & ".xls"
End If
If StrComp(Right(FolderName, 1), "\", vbBinaryCompare) <> 0 Then
' add trailing slash to folder if not present.
FolderName = FolderName & "\"
End If
FullName = FolderName & FileName
ThisWorkbook.SaveAs FullName
End Sub


Change the lines marked with <<< CHANGE to the appropriate values. The
code will save the workbook using the name in cell A1 in the folder
"C:\Test". If the filename in A1 does not end in ".xls", that will be
appended to the file name.

If you have the folder in which the file should be saved in some cell,
change

FolderName = "C:\Test\"
' to something like
FolderName = Worksheets("Sheet1").Range("C1").Text

If you want to prompt the user to select a folder, download the
modBrowseFolder.bas module at
http://www.cpearson.com/Excel/BrowseFolder.aspx . Then, do something
like

FolderName = BrowseFolder()
If FolderName = vbNullString Then
' user cancelled. get out.
Exit Sub
End If


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[RMires]

What does the underscore do?

dim myFileName as string

with activeworkbook
myfilename = "W:\Hebert\Operations\" _
& .Worksheets("OPENING STK").Range("b20").Value & ".xls"

.saveas filename:=myfilename, FileFormat:=xlWorkbookNormal
end with

 

[Dave Peterson]

The space then underscore is the line continuation symbol.

If I hadn't used it, then the line would have been extremely long and your
newsreader may have wrapped in an incorrect location.