GetSaveAsFileName help wanted

[wayne ingalls]

I have an Excel file with some custom userforms that collect various
data to populate two worksheets. After the user has finished entering
all data, I'd like to be able to let the user click on a command button
to automate saving the file in CSV format.

I have looked at the example in the online help which shows how to write
the code for saving as text. The online help doesn't bother telling you
how to save in other formats, but I was able to generate an example for
part of what I needed by recording a macro.

I'm stuck at trying to guess the syntax for the file filter to only
allow the user to save in CSV format.

I'm using Office X and need it to work for both PC and Mac users.

I believe this should work for a PC user:

fName = Application.GetSaveAsFilename(fileFilter:="CSV (*.csv), *.csv")

I have tried this and other combinations similar to the line above for
Mac users, but get runtime errors (fileFilter:="TEXT" will work, but I
don't want to save as plain text):

fName = Application.GetSaveAsFilename(fileFilter:="CSV")

This portion of my code seemed to work, but if there's a better way, I'm
open to suggestions:

If fName <> False Then
Worksheets("Sheet1").SaveAs FileName:=fName, FileFormat:=xlCSV
End If

Thanks in advance for any help. It would be great if applications still
came with printed manuals, or acceptable if there were online manuals.
Unfortunately most software companies don't seem to think it's worth
documenting how to use their products.

-wayne

 

[Bob Greenblatt]

Wayne,

Check Help for getsaveasfilename. The file filter will not work on
macintosh. Realize that this returns only a file name. Both macintosh and
windows Excel do not care a whit about the name (and/or extension) but
recognize the file type intrinsicly. So, your saveas statement with the
fileformat:=xlcsv will work fine on both platforms.

You can parse the name returned by the getsaveasfilename to determine
whether or not you should append the csv extension. Remember that it doesn't
really make any difference on either platform.

You should also set a variable based on the platform where the code is
running, I use: bMac=left(application.operatingsystem,3)="Mac"

Then you can code as follows:
If bmac then
use the mac version of getsaveasfilename
Else
use the windows version
End if

 

[wayne ingalls]

Check Help for getsaveasfilename.

I did read it several times before posting. I also checked my VBA/Excel
book, but since it's geared toward Windows, it doesn't offer any
explanation of what might look different on a Mac.

The file filter will not work on macintosh.

That's not what the online help says, but I'm willing to believe
you know what you're talking about since the help is severely
lacking in detail.

Realize that this returns only a file name. Both macintosh and
windows Excel do not care a whit about the name (and/or extension) but
recognize the file type intrinsicly. So, your saveas statement with the
fileformat:=xlcsv will work fine on both platforms.

I wanted to use GetSaveAsFileName not only to return the user's choice
of file name but also to let the user decide where the file should be
saved. If I used the FileSaveAs, I have to decide where to put the file
and I know that I'm not going to be able to guess the correct location
for every user. I'm lucky if I can guess where I'm going to want to put it!

The reason I wanted to use the file filter was to show the user that the
file will be saved as CSV and not as a normal Excel file. The original
Excel file is very large, but the person using the data doesn't need the
custom user forms. Saving as CSV deletes all the VBA as well as formulas
embedded in cells on a separate worksheet, bringing the file size down
to something manageable for attaching to email. Most of my users have a
hard time thinking of saving to other formats, hence the macro to do the
work for them.

You can parse the name returned by the getsaveasfilename to determine
whether or not you should append the csv extension. Remember that it doesn't
really make any difference on either platform.

Good tip. The file filter does that for me on Windows and I added some
code to detect and strip any .xls extension on Mac files.

You should also set a variable based on the platform where the code is
running, I use: bMac=left(application.operatingsystem,3)="Mac"

Then you can code as follows:
If bmac then
use the mac version of getsaveasfilename
Else
use the windows version
End if

I used
#If Win32 then
' do the windows stuff
#ElseIf Mac then
' do the mac stuff
#End If

which gives similar results. I didn't include that in my original
question because it wasn't causing me any problems. I have included it
here in case it might help someone else in the future.

Anyway, thanks for explaining that file filters don't work on the Mac
regardless of what the online help would lead you to believe. That
together with the suggestion to decide how to handle file extensions was
what I needed to finish off my project. Once again, it would be really
cool if there was more extensive as well as accurate documentation for
this program. It would save us all a lot of time!

-wayne