[Excel 2007] Maybe a bug with Dialogs(xlDialogSaveAs)

[Jerry Khann]

Hello,

With Excel 2007,
I have a macro in PERSONAL.XLSB witch I start with
a button in Quick Access toolbar

***

Sub MyFastSave()
NomDuFichierFinal = "PourVoir.xls"
NomAProposer = "D:\A_Sauver\" & NomDuFichierFinal
etat = Application.Dialogs(xlDialogSaveAs).Show(NomAProposer, xlExcel8)
If etat = True Then
NomApres = ActiveWorkbook.FullName
MsgBox ("The save in Excel 2003 format of " & NomApres & " is
good.")
End If
End Sub

***

If I execute this macro when I had create a new file, the dialog box
is OK with the good path, good file name and good file format.

But if I execute this macro when I had open an existing file by "double
clic"
on the file name in his folder, the dialog box is KO (bad): good file name
and
good file format, but the path is wrong (the original one of the file).

Nothing could change this?

Could you help me, or is it a bug?

Thank's in advance

Regards

***

Sorry for my english: it isn't my native language.

 

[Héctor Miguel]

hi, Jerry !

what I can confirm is that this behavior repeats when the active workbook
has been open from a shortcut: in versions 2000, 2002, 2003 and 2007 (but in version 97)

when this situation has been given, neither you can change the directory
using the ChDir function previous to the presentation of this dialog (xlDialogSaveAs)

neither you can achieve by the windows API (SetCurrentDirectoryA) using an algorithm developed by Rob Bovey

(probably) an alternative will be through the GetSaveAsFileName method in a procedure
where its structure takes care of confirming if the user decides to carry out the saving -? -

hth,
hector.

__ OP __

 

[Jerry Khann]

Hi Héctor,

hi, Jerry !

what I can confirm is that this behavior repeats when the active workbook
has been open from a shortcut: in versions 2000, 2002, 2003 and 2007 (but
in version 97)

when this situation has been given, neither you can change the directory
using the ChDir function previous to the presentation of this dialog
(xlDialogSaveAs)

neither you can achieve by the windows API (SetCurrentDirectoryA) using an
algorithm developed by Rob Bovey

(probably) an alternative will be through the GetSaveAsFileName method in
a procedure
where its structure takes care of confirming if the user decides to carry
out the saving -? -

Thank you for your answer.

It's stupendous, maybe stupid, that the same method or property doesn't work
in the same way with Excel and Word!

So it's a bug!

I will use GetSaveAsFileName with Excel and Dialog (xlDialogSaveAs) with
Word!

***

Sub MyFastSave()
NomDuFichierFinal = "PourVoir.xls"
NomAProposer = "D:\A_Sauver\" & NomDuFichierFinal

NomApres = Application.GetSaveAsFilename( _
InitialFileName:=NomAProposer, _
Title:="Save in Excel 2003 format ")
If VarType(NomDuFichierApres) = vbBoolean Then
MsgBox "Canceled"
Else
On Error GoTo UneErreur
ActiveWorkbook.SaveAs Filename:=NomApres _
, FileFormat:=xlExcel8

MsgBox ("The save in Excel 2003 format of " & NomApres _
& " is good.")

End If

UneErreur:
If Err.Number = 1004 Then
MsgBox "Erreur : " & Err.Number
End If
End Sub

***

Thank's

Regards