By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,401 Members | 1,381 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,401 IT Pros & Developers. It's quick & easy.

Is there a way to retrieve the response from accmdImportAccess?

P: 7
Wassup everybody,

This is my first time posting on this message board.

I am building a button to import an Excel file. I am using:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunCommand acCmdImportAttachExcel
to open import an excel file into Access and append it to an existing table. I have some more code after this that only works if the user imports the excel file.

My problem is when the user clicks "Cancel" on the Import Window, the rest of my code gets executed that results in error messages.

Is there anyway exit the procedure if the user decides to cancel the import?

Jul 24 '10 #1
Share this Question
Share on Google+
8 Replies

P: 283
Hello Aarmando,

you could try this
You will have to make some small adjustments to fit your table and excel import file.

Hope this helps

put this in the command buttons OnClick event
Expand|Select|Wrap|Line Numbers
  3. On Error GoTo Err_Command0_Click
  5. If MsgBox("Are you sure you want to import file?", vbOKCancel) = vbOK Then
  6.  DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:=" Your Table Name ", filename:="C:\Path to file\.xls", HasFieldNames:=True
  7. Else
  8.   Me.Undo
  9. End If
  11. Exit_Command0_Click:
  12.     Exit Sub
  14. Err_Command0_Click:
  15.     MsgBox Err.Description
  16.     Resume Exit_Command0_Click
Jul 24 '10 #2

P: 7
hey slenish,

Thanks for quick response. I had a feeling that will have to use TransferSpreadsheet instead. I will give a it a shot later this week and let you know how it went.
Jul 24 '10 #3

Expert Mod 15k+
P: 31,768
In a situation such as this you certainly want to use error handling (On Error etc).

However, to handle you situation specifically you can use On Error Resume Next. The following line then checks the Err object to determine what caused an error if there was one. More specifically, did the error related to cancelling come up.

To determine what number that actually is, run your code without any error handling and cause it to fail (cancel the import). The error number is what you want to use to compare against Err (The default property of this object is .Number). Simply exectute the code that depends on a successful import if Err <> {that value}.

This is a technique that can work generally for you. Determine the error number to trap using the code with no error handling, then use the number found within the error handling you proceed to add.

It is also possible to handle with an error routine. In this case if the error number is not what you're looking for (generally 0) then Resume Next allows your code to continue after the line that failed.
Jul 24 '10 #4

P: 7
Hey NeoPa,

Thanks for your tip. After reviewing my code some more, I found that I dont get an error at all if the user chooses to cancel the import. BUT i still need to retrieve the response from the Import Wizard Window.

In a nutshell here is what I want my code to do:
Case 1: If its a new record, import Excel file and append to existing table. Then go to that newly imported record.

Case 2: If it is existing record, import Excel file and append to existing table. Then replace the existing values in the current record with the newly imported Excel record. Finally Delete the newly imported Excel record.

Here is my code. I borrowed some of the code from the CarryOver() procedure that appends data to a new record.

Expand|Select|Wrap|Line Numbers
  1. Private Sub impCP_Click()
  2.     'Scope: To import data from an Excel file. In cases of importing a Excel file into a existing record, to replace
  3.     '       the contents of the current record with the newly imported contents.
  4.     Dim rs As DAO.Recordset         'Clone of form.
  5.     Dim frm As Form
  6.     Dim ctl As Control              'Each control on form.
  7.     Dim strControlSource As String  'ControlSource property.
  8.     Dim strControl As String        'Each control in the loop
  9.     Dim delsql As String            'SQL Update string
  10.     Dim strbk As String             'Holds the location of  current record
  11.     Dim aryControls As Variant      'Holds the Controls Name
  12.     Dim lngKt As Long               'Count of controls assigned.
  14.     Set frm = Me.Form
  16.     If frm.NewRecord Then
  17.         'Purpose: Allow importing a Excel file if the current record is a "New" Record
  18.         DoCmd.RunCommand acCmdImportAttachExcel
  19.         DoCmd.Requery
  20.         DoCmd.GoToRecord , , acLast
  22.         Else
  23.             'Purpose:Allow importing a Excel file if the current record already has a ID no.
  24.             'Imports the Excel File
  25.             DoCmd.RunCommand acCmdImportAttachExcel
  26.             DoCmd.Requery
  27.             Set rs = frm.RecordsetClone
  29.             'Assigns the current Record's Bookmark value to the String strbk
  30.             With rs
  31.                 .FindFirst "CoverID = " & Me.CoverID
  32.                 If .NoMatch Then
  33.                     MsgBox "did not work" 'something????
  34.                 Else
  35.                     frm.Bookmark = rs.Bookmark
  36.                     strbk = rs.Bookmark
  37.                 End If
  38.             End With
  40.             'Moves to last record. Whcih should be the newly imported Excel file
  41.             rs.MoveLast
  43.             'Loops through the controls on the form
  44.             For Each ctl In frm.Controls
  45.                 strControl = ctl.Name
  47.                 'Ignores controls that don't have a ControlSource
  48.                 If HasProperty(ctl, "ControlSource") Then
  49.                     strControlSource = ctl.ControlSource
  50.                     With rs(strControlSource)
  51.                         'Ignores Autonumber fields
  52.                         If ((.Attributes And dbAutoIncrField) = 0&) Then
  53.                             If ctl.Value = .Value Then
  54.                                 ' do nothing
  55.                             Else
  56.                                 ctl.Value = .Value
  57.                                 lngKt = lngKt + 1&
  58.                             End If
  59.                         End If
  60.                     End With
  61.                 End If
  62.             Next
  64.             'delsql = "DELETE FROM tblCover " & _
  65.                       "WHERE ((tblCover.[CoverID] = " & strGrw & "));"
  66.             'DoCmd.RunSQL delsql
  67.         End If
  69. Set rs = Nothing
  70. End Sub
The problem is the record is overwritten by whatever the last record is the recordset even if the user did not import any Excel file

Have any tips?
Jul 27 '10 #5

Expert Mod 15k+
P: 31,768
Sorry Armando. I ran out of time again tonight. Been busy busy, but I'll try to get back to this tomorrow time allowing.
Jul 29 '10 #6

Expert Mod 15k+
P: 31,768
Aarmando, I'm trying to work on this but I find no reference to acCmdImportAttachExcel. Is it possible you are using an Access 2007 specific item without mentioning you're using Access 2007?

I get an error number of 2501 (The RunCommand action was canceled.) if I even try to run it at all.
Jul 29 '10 #7

P: 7
Yes, I am using Access 2007. I am sorry for not mentioning it.

I had a new idea for solving the problem. Maybe if I store how many records there are in the Recordset, then get the new RecordCount after the user made a choice in the Import Wizard.

If their equal: no records were added, thus the user didn't import an Excel file

If their unequal: a record was added, then the user did import an Excel file.

Ill let you guys know how it went
Jul 29 '10 #8

Expert Mod 15k+
P: 31,768
Yes. Please do.

Sorry I can't be much help, but I'm not very familiar with what you're doing and I can't even test it out, so I'm a little hindered in that.

Good luck anyway and Welcome to Bytes!
Jul 29 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.