Is there a way to retrieve the response from accmdImportAccess?

[Aarmando]

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:

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?

Thanks

[slenish]

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

 

On Error GoTo Err_Command0_Click
    
If MsgBox("Are you sure you want to import file?", vbOKCancel) = vbOK Then
 DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:=" Your Table Name ", filename:="C:\Path to file\.xls", HasFieldNames:=True
Else
  Me.Undo
End If
        
Exit_Command0_Click:
    Exit Sub

Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click

[Aarmando]

hey slenish,

Thanks for quick response. I had a feeling that will have to use TransferSpreads heet instead. I will give a it a shot later this week and let you know how it went.

[NeoPa]

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.

[Aarmando]

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.

Private Sub impCP_Click()
    'Scope: To import data from an Excel file. In cases of importing a Excel file into a existing record, to replace
    '       the contents of the current record with the newly imported contents.
    Dim rs As DAO.Recordset         'Clone of form.
    Dim frm As Form
    Dim ctl As Control              'Each control on form.
    Dim strControlSource As String  'ControlSource property.
    Dim strControl As String        'Each control in the loop
    Dim delsql As String            'SQL Update string
    Dim strbk As String             'Holds the location of  current record
    Dim aryControls As Variant      'Holds the Controls Name
    Dim lngKt As Long               'Count of controls assigned.

    Set frm = Me.Form
    
    If frm.NewRecord Then
        'Purpose: Allow importing a Excel file if the current record is a "New" Record
        DoCmd.RunCommand acCmdImportAttachExcel
        DoCmd.Requery
        DoCmd.GoToRecord , , acLast
        
        Else
            'Purpose:Allow importing a Excel file if the current record already has a ID no.
            'Imports the Excel File
            DoCmd.RunCommand acCmdImportAttachExcel
            DoCmd.Requery
            Set rs = frm.RecordsetClone
            
            'Assigns the current Record's Bookmark value to the String strbk
            With rs
                .FindFirst "CoverID = " & Me.CoverID
                If .NoMatch Then
                    MsgBox "did not work" 'something????
                Else
                    frm.Bookmark = rs.Bookmark
                    strbk = rs.Bookmark
                End If
            End With
            
            'Moves to last record. Whcih should be the newly imported Excel file
            rs.MoveLast
            
            'Loops through the controls on the form
            For Each ctl In frm.Controls
                strControl = ctl.Name
                
                'Ignores controls that don't have a ControlSource
                If HasProperty(ctl, "ControlSource") Then
                    strControlSource = ctl.ControlSource
                    With rs(strControlSource)
                        'Ignores Autonumber fields
                        If ((.Attributes And dbAutoIncrField) = 0&) Then
                            If ctl.Value = .Value Then
                                ' do nothing
                            Else
                                ctl.Value = .Value
                                lngKt = lngKt + 1&
                            End If
                        End If
                    End With
                End If
            Next
            
            'delsql = "DELETE FROM tblCover " & _
                      "WHERE ((tblCover.[CoverID] = " & strGrw & "));"
            'DoCmd.RunSQL delsql
        End If
    
Set rs = Nothing
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?

[NeoPa]

Sorry Armando. I ran out of time again tonight. Been busy busy, but I'll try to get back to this tomorrow time allowing.

[NeoPa]

Aarmando, I'm trying to work on this but I find no reference to acCmdImportAtta chExcel. 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.

[Aarmando]

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

[NeoPa]

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!