I have a form with one button the code for that button is - On Error Resume Next
-
DoCmd.TransferSpreadsheet acImport, 8, "FuelConsumption NewData", Me.tb_FileName, 1, "sheet2$"
-
-
On Error Resume Next
-
DoCmd.TransferSpreadsheet acImport, 8, "Inbound NewData", Me.tb_FileName, 1, "Inbound Trains$"
-
DoCmd.TransferSpreadsheet acImport, 8, "Outbound NewData", Me.tb_FileName, 1, "Outbound Trains$"
-
-
On Error Resume Next
-
DoCmd.TransferSpreadsheet acImport, 8, "LPMH NEWDATA", Me.tb_FileName, 1 ', "March 09$"
-
-
MsgBox "Import Complete"
This is a text box that contains the location of the file being imported
I am aware that "On Error Resume Next" is the improper way to deal with error handling but I haven't been able to find a solution to fix the problem I have
The Problem is that, in case that a spread sheet doesn't match any of the 4 or doesn't meet the format then show a error message that states "Import file is not in the correct format." but I don't want to show a error message for any of the DoCmd.TransferS preadsheet and no matter what there will be an error message for them because I can only import one spreadsheet at a time and they are located on 3 seperate spreadsheets/workbooks.
The code that i've attempted to fix the problem is this - 'On Error GoTo Err1
-
' DoCmd.TransferSpreadsheet acImport, 8, "FuelConsumption NewData", Me.tb_FileName, 1, "sheet2$"
-
' MsgBox "FuelConsumption Import Complete"
-
-
'On Error Resume Next
-
-
'Err1:
-
'On Error GoTo Err2:
-
'DoCmd.TransferSpreadsheet acImport, 8, "Inbound NewData", Me.tb_FileName, 1, "Inbound Trains$"
-
'DoCmd.TransferSpreadsheet acImport, 8, "Outbound NewData", Me.tb_FileName, 1, "Outbound Trains$"
-
'MsgBox "Inbound/Outbound Import Complete"
-
-
'On Error Resume Next
-
-
'Err2:
-
'On Error GoTo Err3:
-
'DoCmd.TransferSpreadsheet acImport, 8, "LPMH NEWDATA", Me.tb_FileName, 1 ', "March 09$"
-
'MsgBox "LPMH Import Complete"
-
-
'On Error Resume Next
-
-
'Err3:
-
-
'MsgBox "The Import data selected is not in the correct format"
-
-
'Exit Sub
Using this code no matter what, I get an error for atleast one of the transferspreads heets. It would pass one then import then error or not import any data and just error.
Run-Time Error '3125'
Please help!
Thanks.
5 8356 ADezii 8,834
Recognized Expert Expert
I'm a little confused as to what exactly you are asking but I think that
may help. This line effectively disables Error Handling in the Procedure in which it is invoked.
Where would i place that statement?
Hopefully this explanation will help.
I have one text box to one location of one excel spreadsheet
I have one button and that button gets the location of the file from the text box but it has to get 3 different locations in order to import all the data.
Example:
to Import file "test2.xls"
for table 2
it goes through
DoCmd.TransferS preadsheet acImport, 8, "Table 1", Me.tb_FileName, 1, "Test1"
DoCmd.TransferS preadsheet acImport, 8, "Table 2", Me.tb_FileName, 1, "Test2"
DoCmd.TransferS preadsheet acImport, 8, "Table 3", Me.tb_FileName, 1, "Test3"
So in order to get to import "test2.xls" it erros at Test1 then imports through Test2 and errors at Test3.
I don't want it to error for neither of them but, I do want it to error if it erros for ALL of them (if the spreadsheet being imported doesn't meet the criteria for Test1, Test2,Test3).
ADezii 8,834
Recognized Expert Expert
This is a rather unusual request, but assuming I now understand your dilemma correctly, the following code will generate an Error Message only under the condition that ALL THREE TransferSpreads heet() actions produce an Error. If only 1, None, or 2 fail, the Error Message will not appear. - Dim intNumOfErrors As Integer
-
On Error Resume Next
-
-
intNumOfErrors = 0 'Initialize
-
-
DoCmd.TransferSpreadsheet acImport, 8, "Table 1", Me.tb_FileName, 1, "Test1"
-
If Err.Number <> 0 Then 'Error!
-
intNumOfErrors = intNumOfErrors + 1
-
Err.Clear 'Clear the Error Object
-
End If
-
-
DoCmd.TransferSpreadsheet acImport, 8, "Table 2", Me.tb_FileName, 1, "Test2"
-
If Err.Number <> 0 Then 'Error!
-
intNumOfErrors = intNumOfErrors + 1
-
Err.Clear 'Clear the Error Object
-
End If
-
-
DoCmd.TransferSpreadsheet acImport, 8, "Table 3", Me.tb_FileName, 1, "Test3"
-
If Err.Number <> 0 Then 'Error!
-
intNumOfErrors = intNumOfErrors + 1
-
Err.Clear 'Clear the Error Object
-
End If
-
-
If intNumOfErrors = 3 Then
-
MsgBox "All 3 Imports Failed!", vbCritical, "ALL Imports Failure"
-
End If
Thank you it worked perfectly
ADezii 8,834
Recognized Expert Expert
Glad it worked out for you.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: gwaddell |
last post by:
I have a stored procedure that is loading data into a global temp table.
Here is all the code for that stored procedure.
CREATE PROCEDURE
AS
SET NOCOUNT ON
BEGIN
|
by: user_5701 |
last post by:
Hello, I'm getting an error with a Docmd.Transferspreadsheet line of
code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel2000,
"tblTest", pathAndFilename, True
The above line works perfectly fine, but when I change the table name
argument (tblTest) to the name of a query, it fails and gives the
following error:
|
by: Polly |
last post by:
I had a macro that ran a parameter query and created and opened an
Excel file with the system date as part of the file name, but I had to
change the file name by hand. So I converted the macro to code using
tools-->references.
The converted macro included the following statement:
DoCmd.OutputTo acQuery, "qselLabelsBloodLog_output",
"MicrosoftExcelBiff8(*.xls)", "S:\susan_cheree\lb041129.xls", True,
"", 0
|
by: aleksandra_83 |
last post by:
Hello,
I have searched all over google groups and internet, but I have not
found a solution to this problem that has worked. The solutions I found
helped me single out the line that is causing the problem, though.
I programmatically open a Microsoft Excel file from Access, do some
data checks etc., define a named range, perform
DoCmd.TransferSpreadsheet, then delete the range, set all referenced
objects to Nothing, etc. However, Excel...
|
by: sara |
last post by:
I have a procedure to automate bringing several Excel files into our
Access tables, on a daily basis.
The problem is that if the user has a problem, and tries to run the
import again (maybe 3 files imported then there was a data problem and
they want to re-import after fixing the problem), I can't get the Error
handling to fire if the user is attempting to import duplicate key
records.
Message when I try to import records already on...
| |
by: SarahK |
last post by:
All,
I am trying to import a table from Excel, but I get the 3709 error,
search key not found. Any ideas? My TransferSpreadsheet method
statement is:
DoCmd.TransferSpreadsheet acImport, , "tblStaging", strFilePath &
"Temp.xls", True, ""
Thanks in advance,
|
by: robert.waters |
last post by:
Hello,
When executing DoCmd.TransferSpreadsheet to an .xls file that's being
used as a datasource for a Word mail merge document, if that Word
document is currently open, the TransferSpreadsheet action fails with
an 'Error 0'. I have tried different methods of trapping that error,
and I still cannot get my app to indicate that the user should exit
Word: I have tried disabling the error handler before DoCmd is called,
looking for an Err...
|
by: ineedahelp |
last post by:
Can anyone help me figure out why I continue to get a runtime error '3010' "TABLE qryMultiSelect ALREADY EXISTS'? Here is my code. thank you in advance for any help!!!
Private Sub cmbSelectionDone_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
|
by: Kosmos |
last post by:
Hey ya'll...I can't seem to figure out why I'm getting this error message, but it all started when I added the new line of code with the recSet5.AddNew --- when I ran the first line, the logic worked and the fields were populated properly, when I added all the rest, it wouldn't run and even when I tried to go back, it still won't run now...I am getting the following error message:
"Either BOF or EOF is True, or the current record has been...
|
by: repath |
last post by:
I tried the code with automation:
For Each sheet In objWorkBook.sheets 'for each sheet in excel loop
Debug.Print "Imported sheet is :"; sheet.Name
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, sheet.Name, "OperatorData", True, sheet.Name & "!"
Next
the OPERATORDATA excel file has 10 work sheets.
after importing 6 sheets, this run time error is displayed indicating DOCMD.TransferSpreadSheet line
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |