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
8 2662
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
-
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.
NeoPa 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: RobertHillEDS |
last post by:
While using the Soap generated ASP code, I would like to dump the raw
contents of the request and response objects using Response.AppendToLog.
I have tried using variations of the following code,...
|
by: zoltix |
last post by:
Hi,
How to retrieve a value in in an aspx page?
Normally it is easy, drag and drops the textarea in aspx page and it is
work. But in this case, I generate the html code manually () and put as...
|
by: Grant Merwitz |
last post by:
I am trying to write an XML file from my database.
Currently, i have this code on an ASPX page, but have also built a business
layer that manages all the DataRetrieval and is reference in my main...
|
by: Hexman |
last post by:
Code below ----
I've asked a similar question on this forum earlier. This is a slightly different situation.
Previous Question ----
I'm trying to save some specific web pages to disk as...
|
by: Jeff |
last post by:
hey
ASP.NET 2.0
I'm preparing for a certification exam on asp.net 2.0 and yesterday I took a
skill assessment test on microsoft.com. One of the questions was about
creating cookies. This was...
|
by: bazubwabo |
last post by:
hi everybody ,
could u please help me to find out the error on my asp codes,i can't retrieve the inserted values.
Here is below the code:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!DOCTYPE...
|
by: Paul Furman |
last post by:
I'm setting up credit card payment through authorize.net and they have
the option to send a POST string back to my site once complete. I'm not
sure how to proceed. They don't have much to read...
|
by: LetMeDoIt |
last post by:
Greetings,
I'm using ASP to retrieve from MSSQL and I then populate a table.
After several months of successfull retrieves, this same code now
bombs out. It turns out that if I clear out from...
|
by: =?Utf-8?B?QXhlbCBEYWhtZW4=?= |
last post by:
Hi,
I've created a class library assembly containing several string resource
files, like:
- TableColumns.resx
- TableColumns.de.resx
- General.resx
- General.de.resx
|
by: giandeo |
last post by:
Hello Experts.
Is it possible to retrieve the value from a populated pull down menu from a database and then use that value to access the same database to get the related fields.
Example:
...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |