Re: Error reporting between Access and Excel

From: Andy (anonymous_at_discussions.microsoft.com)
Date: 08/20/04 

Date: Fri, 20 Aug 2004 03:58:54 -0700

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?

>-----Original Message-----
>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
>--
>Steve Arbaugh
>ACG Soft
>http://ourworld.compuserve.com/homepages/attac-cg
>
>
>
>"Andy" <anonymous@discussions.microsoft.com> wrote in
message
>news:166401c46f37$fea5ce20$a301280a@phx.gbl...
>> 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!
>
>
>.
>