save as cell A1 to to a specific drive

[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

 

[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

 

[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

 

[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