Problem with VBA in Excel X on Mac OS x
simon margulies
I got to transcribe a VBA-based Excel Spreadsheet programmed in excel 98 on
macintosh.
Newly it should run on Windows and Mac OS X. I tried on Windows and until
now everything works fine!
Sadly in the office x version for Mac OS X there occurs a strange error
messages:
As User can perform actions on the spreadsheet that create new textfiles, I
need the user to type in the name of the new textfile to then save it with
that name.
When, for some strange reason, the user wants to exit the Save-Dialog
without saving anything, my VBA-Script should close the newly build file,
open an other one and finally exit the sub.
To make this possible I used the following if-clause:
If (Application.Dialogs(xlDialogSaveAs).Show) = False Then //if the user
presses on Ścancelą
Application.DisplayAlerts = False
ActiveWorkbook.Close //here the script gets the error
Workbooks.Open Filename:=pfad_stzh
Exit Sub
End If
This if clause works on all other mentioned environements.
The problem in Mac OS X seams to be, that the if-clause always turns true
and the following statements are executed. But on the screen I got still the
Save-Dialog and would like to save the file. So the if-clause shouldnąt be
true or false at this point. It seems that the script simply continues
without waiting for the user to complete the Save-Dialog. The script then
gets an error on line ActiveWorkbook.Close. This I think is quite logical
because the active Window is the Save-Dialog and not the newly created
text-file. Often Excel then crashesŠ
The strange things are: The script works on other systems, doing the script
in the Śtest modusą in the vba-editor (step-by-step with F8) results no
error.
I got Mac OS X 10.2.3 and the newest update for office X installed.
Does anyone got a hint?
Thank you very much
Simon margulies
Bob Greenblatt
Rather than show the dialog box, you should use
application.getsaveasfilename
This will return false if the dialog was cancelled.
--
Bob Greenblatt [Excel MVP]
Bobgre...@msn.com
www.bandlassoc.com
simon margulies
in BA407198.1B1C4%bobgre...@msn.com:
Ok, thanks i'll try that. But still the above mentioned behavior rests
unclear.
simon margulies
<sei...@access.unizh.ch> in BA426BE5.4D8%sei...@access.unizh.ch:
Works fine on os x but has problems with the arguments on windows xp and mac
os 8.X and 9.x (office 98)?
Please help!
simon
Bob Greenblatt
What problems are you having? Please provide a code sample and the nature
of the issue.
simon margulies
<bobgre...@msn.com> in BA482557.1C071%bobgre...@msn.com:
Problem with .GetSaveAsFilename : I am not able to set the default save
format to text - the enduser should not have to choose this format but
already having it set. Following example works on all computers fine, but in
os x the default save format is allways .xls and not .txt The code:
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.DefaultSaveFormat = xlText
Dim Name
Name = Application.GetSaveAsFilename(InitialFilename:="D 2003 Name
Vorname.txt")
If (Name <> False) Then
With Application
.SheetsInNewWorkbook = 1
End With
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=Name, FileFormat:=xlText,
CreateBackup:=False
name_kunde = Workbooks(Workbooks.Count).Name 'liefert den Namen der zu
letzt geöffneten/gespeicherten/erstellten Datei: in diesem fall: das D des
Kunden xy
pfad_kunde = Workbooks(Workbooks.Count).Path + Application.PathSeparator
+ name_kunde 'liefert den Pfad mit Name der zu letzt
geöffneten/gespeicherten/erstellten Datei: in diesem fall: das D des Kunden
xy
Else 'falls sichern vom User abgebrochen wird
Application.DisplayAlerts = False
Workbooks.Open Filename:=pfad_stzh 'variable wird im autoopen modul 5
gesetzt
Exit Sub
End If
As I didn't had any successes I tried it with dialog.show but running it
always produces an error on the line 'ActiveWorkbook.Close' on os x. on the
other platforms it works fine...
The error occurs while the dialogbox is still being shown. So I don't
understand why the if-statement turns false.
With Application
.SheetsInNewWorkbook = 1
End With
'Als Textdatei speichern
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="D 2002 Name Vorname",
FileFormat:=xlText, _
CreateBackup:=False
If (Application.Dialogs(xlDialogSaveAs).Show) = False Then
'bei Abbruch d xx schliessen
Application.DisplayAlerts = False
ActiveWorkbook.Close
'Datei Start ist wieder da
Workbooks.Open Filename:=pfad_stzh
Exit Sub
End If
name_kunde = Workbooks(Workbooks.Count).Name
pfad_kunde = Workbooks(Workbooks.Count).Path + Application.PathSeparator +
name_kunde
thanks
Bob Greenblatt
I'm not sure what's wrong. I realize that the file format can not be
controlled with getsaveasfilename. I tried your code and it seemed to work
fine. I did not get an error on the activeworkbook.close, and the saveas
dialog was dismissed properly. Are you sure you have the latest office
upgrades installed?
simon margulies
<bobgre...@msn.com> in BA4D6C5F.1CDD1%bobgre...@msn.com:
> Simon,
>
> I'm not sure what's wrong. I realize that the file format can not be
> controlled with getsaveasfilename. I tried your code and it seemed to work
> fine. I did not get an error on the activeworkbook.close, and the saveas
> dialog was dismissed properly. Are you sure you have the latest office
> upgrades installed?
Dear Bob,
I got service release 1 installed. The problem is strange because it only
appears on os x and not on the other platforms. When I test the code step by
step pressing F8 no error occurs. It must be a runtime problem that for some
strange reason the if-clause always turns false.
As you may have seen, I tried it too by setting the defaultsaveformat to
text (till now only tried on os x) but sadly the getsaveasfilename dialog
doesn't get it...
Do you have other ideas?
Bob Greenblatt
As I said, it works fine on my system (OS 10.2.3, Excel X SR1). Since you
will not be able to set the file type to text perhaps you need to rethink
how the files are processed. I do not have a good work around for you.
simon margulies
<bobgre...@msn.com> in BA516B25.1D921%bobgre...@msn.com:
Thank you anyway
Simon
simon margulies
<sei...@access.unizh.ch> in BA51ED75.239D%sei...@access.unizh.ch:
Could the problem be that I'm using the German version of Microsoft Office?
Bob Greenblatt
Yes, I suppose that is possible. Perhaps something slipped in the conversion
of the object libraries.