Stop Macro if Action Canceled

[Guest]

I am fairly new to developing applications in Access. I have an application where a menu form calls a macro to
(1) Import Excel data into a table
(2) Rename the imported table to "Current Month
(3) Perform various querie
(3) Append the table to a "YearToDate Tabl

My problem occurs in step (1) when a user cancels the select file (to import) action. The macro continues and renames the macro (rather than the imported table) "current month". Is there a way to trap this condition and stop the macro if the user does not select a file to be imported

 

[John Viescas]

You've just stumbled across the biggest reason why you should never use
macros - no error trapping.

How are you renaming the object? With the Rename action, you can explicitly
specify the object type, old name, and new name - so it should fail at that
point if the table did not get imported (the object does not exist), not
rename your macro (that I assume has the focus).

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

I am fairly new to developing applications in Access. I have an

application where a menu form calls a macro to

(1) Import Excel data into a table
(2) Rename the imported table to "Current Month"
(3) Perform various queries
(3) Append the table to a "YearToDate Table

My problem occurs in step (1) when a user cancels the select file (to

import) action. The macro continues and renames the macro (rather than the
imported table) "current month". Is there a way to trap this condition and
stop the macro if the user does not select a file to be imported?

 

[Guest]

John V.
Thanks for the response. Re your questions: I am using the Rename action. As I said I am new to access development however, I believe I am unable to specify the object type since that would also require me to name the source file. I am not able to name the source file as the source file name changes monthly

Thank
John Dionn

----- John Viescas wrote: ----

You've just stumbled across the biggest reason why you should never us
macros - no error trapping

How are you renaming the object? With the Rename action, you can explicitl
specify the object type, old name, and new name - so it should fail at tha
point if the table did not get imported (the object does not exist), no
rename your macro (that I assume has the focus)

--
John Viescas, autho
"Microsoft Office Access 2003 Inside Out
"Running Microsoft Access 2000
"SQL Queries for Mere Mortals
http://www.viescas.com
(Microsoft Access MVP since 1993

I am fairly new to developing applications in Access. I have a

application where a menu form calls a macro t

(1) Import Excel data into a tabl
(2) Rename the imported table to "Current Month
(3) Perform various querie
(3) Append the table to a "YearToDate Tabl

import) action. The macro continues and renames the macro (rather than th
imported table) "current month". Is there a way to trap this condition an
stop the macro if the user does not select a file to be imported

 

[John Viescas]

I guess I don't understand. You say you're using a macro to import an
spreadsheet and rename it, which implies to me that you're using
TransferSpreadSheet and Rename actions. You must name the Table (a required
parameter) in TransferSpreadSheet, so it's that name you should specify in
the Old Name field of the Rename action.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

John V.
Thanks for the response. Re your questions: I am using the Rename

action. As I said I am new to access development however, I believe I am
unable to specify the object type since that would also require me to name
the source file. I am not able to name the source file as the source file
name changes monthly.

 

[John Viescas]

Gotcha. And you provide the final table name to the Wizard that starts when
you select the file and click Import. Other than instructing the user to
always specify some standard table name for the import in the last panel of
the Wizard, I can't think of any clean way to do this with a macro. Your
Rename would then specify that "standard" table name to rename.

Of course the real solution is to use Visual Basic, prompt the user for the
file name to import, and then supply that as a variable to the
TransferSpreadsheet method with a known table name.

You could write a simple function to do that and then call it with RunCode.
Put this in a module you create from the database window:

Public Function GetSpreadSheet() As Integer
Dim strFileName As String

' First set an error trap to skip errors
On Error Resume Next
' Delete the old table
CurrentDb.TableDefs.Delete "TempExcelTable"
' Set a generic error trap
On Error GoTo GetSpreadSheet_Err
' Ask the user for the file name
strFileName = InputBox("Enter the path and file name you want to import.",
_
"Enter Spreadsheet File")
If Len(strFileName) = 0 Then
MsgBox "You didn't enter anything."
GetSpreadSheet = False
Exit Function
End If
' Think we got a name, try to import it
DoCmd.TransferSpreadSheet acImport, , "TempExcelTable", _
strFileName, True ' True assume first row is column names
MsgBox "Import successful."
GetSpreadSheet = True

GetSpreadSheet_Exit:
Exit Function

GetSpreadSheet_Err:
MsgBox "Error encountered: " & Err & ", " & Error
GetSpreadSheet = False
Resume GetSpreadSheet_Exit
End Function

If you use the above code, the imported spreadsheet will always be called
"TempExcelTable" - or you can substitute any name you like in the code. You
won't have to do a rename.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

John
I am using the RunCommand Action along with "import" which opens a

selection box just like menu selection file open or import. This action
does not require a parameter such as file name or table.