save as cell A1 to to a specific drive

  • Thread starter Thread starter Johnnyboy5
  • Start date Start date
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
 
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
Click to expand...

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
 
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
Click to expand...

Hi

thanks - however doesnt appear to work.

John
 
Hi

thanks - however doesnt appear to work.

John
Click to expand...

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
 
Back
Top