save as cell A1 to to a specific drive

J

Johnnyboy5

Hi

When I use the macro below it does save the file as per cell A1 and
it does save it to the chosen drive. But it then closes Excel.

On checking the file location it is saved there.

Any ideas what’s wrong.

Thanks

John


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim FileSaveName


ChDrive "H"
ChDir "H:\Temp"


ThisFile = Range("A1").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub
 
D

Don Guillett Excel MVP

Hi

When I use the macro below  it does save the file as per cell A1 and
it does save it to the chosen drive.   But it then closes Excel.

On checking the file location it is saved there.

Any ideas what’s wrong.

Thanks

John

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
    Dim FileSaveName

    ChDrive "H"
    ChDir "H:\Temp"

    ThisFile = Range("A1").Value
    ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub

Try it this way (saveCOPYas) to also avoid changing the default dir
\folder. Also, includes the sheet & .xls

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
mydrive = "H:"
mydir = "Temp"
myname = Sheets("sheet1").Range("a1")
ms = mydrive & "\" & mydir & "\" & myname & ".xls"
ActiveWorkbook.SaveCopyAs Filename:=ms
End Sub
 
J

Johnnyboy5

Try it this way (saveCOPYas) to also avoid changing the default dir
\folder. Also, includes the sheet & .xls

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
mydrive = "H:"
mydir = "Temp"
myname = Sheets("sheet1").Range("a1")
ms = mydrive & "\" & mydir & "\" & myname & ".xls"
ActiveWorkbook.SaveCopyAs Filename:=ms
End Sub

Hi

thanks - however doesnt appear to work.

John
 
J

Johnnyboy5

Hi

thanks - however doesnt appear to work.

John

Sorry it does work - what is confusing is that when you go to file
save as - it show "documents" even thought the file does save to "my
dir" etc.

The issue is the other people who will be using this sheet wont
understand where its been save to - is there a way to change that
dialog box so it shows its saved to "mydir" etc

John
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top