Error reporting between Access and Excel

[Andy]

This is a bit complicated and the problem is in two parts!

I have an Access database from which I have created a
procedure that, in theory, iterates through a number of
recordsets and on each iteration dumps the data to an
Excel object that it creates and then runs an Excel macro
to perform various formatting and calculation tasks. The
last thing the Excel macro does is attempt to save the new
workbook with a given name. The workbook is then closed
and the Access procedure continues with the next recordset.

If the name already exists the standard 'File name exists,
do you want to replace it?' type dialog box appears with
the Yes, No and Cancel buttons. If the user clicks Yes
then all is well and the file is overwritten. However...

Problem 1
If the user clicks No or Cancel a '1004' error is thrown,
which I can trap but I don't know how to identify which
button was pressed.

Problem 2
Assuming I can identify the button that was pressed, I
would like to be able to let Access know that the Cancel
button was pressed so that it can exit the Access
procedure. Incidentally, if No is pressed then I will
make the Save As dialog box appear but I do know how to do
that!

 

[SA]

Andy:

Unfortunately with Excel, the error values returned by the Err.Number
function often return values like 1004 for many different errors. Knowing
that in your situtation, you can check yourself with code to find out if the
file exists and pop a message box. If you look around the web (e.g.
www.mvps.org/vbnet) or in the MS knowledgebase, you'll find many places to
get code to launch the file save as dialogs.

Use the Dir() function to find out if the file exists, something like this:

Function FileActions (TargetFile as String, NewFile as String) as Byte
'Supply both the target file name and a string variable to the NewFile
parameter
'NewFile would be filled on return from this function if the user selected a
new file name
'The return value from the function would be used by the rest of your
program to determine
'What to do.....
Dim intResponse as Integer, boolExists as boolean

If Len(Dir(TargetFile)) > 0 Then boolExists = True
If boolExists = False Then
FileActions = False
Exit Function
Else
intResponse = MsgBox ("The target file " & TargetFile & " already
exists, do you want to " & _
"overwrite it?", vbYesNoCancel + vbQuestion)
Select Case intResponse
Case vbYes
FileActions = False
Case vbNo
'call a function here to display the SaveAs dialog
'the return of which would be returned to original calling function
NewFile = SomeFunctionToCallSaveAsDialog()
FileActions = 1
Case vbCancel
FileActions = 2
End Select
End Function

 

[Andy]

Steve, thanks for this - I've just got back from my hols
so hope you pick this up!

This all makes sense to me except the part dealing with
getting a new file name if the user clicks No. If you are
assigning the return value to the NewFile variable (which
is declared as a parameter of the FileActions function)
how can this be passed back to the original calling
function? Won't the FileActions function just return the
byte value assigned to it and the NewFile value be lost
when the function ends?