open/save dialog, user clicks cancel

T

Tim Johnson

I'm fairly new at this, so sorry if it's a stupid question.

I've used the code from the http://www.mvps.org/access/api/api0001.htm to
create dialog boxes for both importing and exporting data. I have 2
questions, though:
1.) I've had to create 2 separate macros for exports, 1 for csv files and 1
for xls. I've tried putting both in 1 module, but I kept getting errors when
I'd test for both. 1 would usually save np (it varied which one), but the
other format would either do nothing or create an error.
2.) I know there's a way to prevent/hide the error when a user clicks the
cancel button from the dialog box, but my searches have turned up nothing.

Any help is greatly appreciated.
 
K

Klatuu

If you are using the API code you downloaded and the user cancels, the call
returns and empty string. Since I don't use macros much, I don't know how
you handle that in a macro. In VBA, you test the return value to see if it
is = ""
 
T

Tim Johnson

Thanks Klatuu, I've found the answers to both (mostly through testing,
although I'm sure the web's riddled with it...I just didn't find it in my
searches).

The code I ended up using to solve both issues is this:

If strSaveFileName = "" Then
MsgBox "Action Cancelled"
Else:
If strSaveFileName Like "*xls" Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
[TableName], strSaveFileName, True
Else:
DoCmd.TransferText acExportDelim, , [TableName], strSaveFileName, True
End If
End If

For imports, of course, I replaced "strSaveFileName" with
"strInputFileName". I'm really very new at this, and have learned all I know
from this forum, so the code maybe dirty, but it works. Hopefully, this will
prove useful to someone else.
 
Top