How do I set the save as directory using VBA? (ChDir not working)

[knifey]

Hi,
I'm a VBA beginner and was after some advice on how to use the ChDrive/ChDir methods.
I've written the macro shown below to check if the title (cell A1) contains "Template" and if it does, then display the message. If ok is pressed (or if cell A1 dosn't contain "Template") then change the save as directory to the one shown below.
This all works fine upto the change of drive/directory (it dosn't change).
I've had a look at the GetSaveAsFilename method. But I don't need to save it. I just need to set the save as directory.
What am I doing wrong with the ChDrive/ChDir method? Can anyone offer any advice?
Thanks,
Roy


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

If SaveAsUI Then
i = Right(Range("A1"), 8)
If i = "Template" Then
If MsgBox("Region and project title not set. Are you sure you want to save?", vbOKCancel) = vbOK Then
ChDrive ("O")
ChDir ("O:\# BAU Testing Programme\BAU POM")
Exit Sub
Else
Cancel = True
End If
Else
ChDrive ("O")
ChDir ("O:\# BAU Testing Programme\BAU POM")
End If
End If
End Sub

 

[SkipVought]

Hi,

"I've had a look at the GetSaveAsFilename method. But I don't need to save it"

Then why are we talking about the Workbook_BeforeSave event?

GetSaveAsFilename does NOT save the file. It only gets/sets the Path/Filename. Is that not what you want?

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[knifey]

Hi Skip,
Yep all I need is for the save as dialog box to appear on the drive and folder I specify. If the filename was set to *.xls that would also be good.
The Workbook_BeforeSave event is only to activate an 'are you sure?' box when the user tries to save the workbook. The user should click cancel as a matter of course (but ok is also needed). So all I need is info on how to set the directory path in a save as dialog box.
Thanks for the info on GetSaveAsFilename. I'll read up and see what I can find.
Roy

 

[PHV]

A starting point:
Dim varPath As Variant
varPath = Application.GetSaveAsFilename("O:\# BAU Testing Programme\BAU POM", "Excel Files (*.xls), *.xls", , "your Title here")
If varPath = False Then
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[knifey]

Hi PHV,
Thanks for advice but now I have another problem. My new code shown below works in every situation apart from the point where I try to save the workbook (If varPath <> False Then, etc).
The problem is that I need to set Application.EnableEvents to True before ending the macro otherwise the event won't trigger the next time. As it stands now, when the macro ends it triggers a second save as dialog box with a default directory path specified.
Any further advice would be much appreciated.
Thanks,
Roy

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim i As String
Dim varPath As Variant

Application.EnableEvents = False

If SaveAsUI Then
i = Right(Range("A1"), 8)
If i = "Template" Then
If MsgBox("Region and project title not set. Are you sure you want to save?", vbOKCancel) = vbOK Then
varPath = Application.GetSaveAsFilename("O:\# BAU Testing Programme\BAU POM\", "Excel Files (*.xls), *.xls", , "Blah Blah")
If varPath <> False Then
Cancel = False
'other code here
Else
Cancel = True
End If
Else
Cancel = True
End If
End If
End If
Application.EnableEvents = True
End Sub