Help with "save as" macro

  • Thread starter Thread starter bobmor
  • Start date Start date
B

bobmor

I am trying to set up a macro that saves a file into a certain
directory with the filename that I have entered into one of the cells
in the sheet. For example, if I enter "abc" into a certain cell , I
want the macro to save the file as "o:\sidest 2003\abc.xls". I can't
find good documentation on this anywhere, so any help would greatly
appreciated.

Thanks
Bob
 
One way:

Public Sub BobsSaveAs()
Const PATH As String = "o:\sidest 2003\"
With ActiveWorkbook
.SaveAs FileName:=PATH & _
.Sheets("Sheet1").Range("A1").Value & ".xls"
End With
End Sub

This assumes the macro will be saved in personal.xls or another
workbook. If it will only apply to the workbook with the macro in
it, change ActiveWorkbook to ThisWorkbook. Adjust your sheet name
and cell reference to suit.

For more documentation, look up SaveAs in Help.
 
and i would the dialog box file that comes after "file save as" to stay
on the screen (with all the default infor below), but allowing the user
to change if necessary...?

thank you...!
 
Try:

Chdrive "O"
Chdir "o:\sidest 2003"
Application.Dialogs(xlDialogSaveAs).Show (range("a1").value)

assuming that the name is in cell a1.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
 
Back
Top