UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How Error Trapping Excel Import To Access Table, Access 2010    
 
   
gint32
post Feb 23 2018, 12:00 PM
Post#1



Posts: 244
Joined: 8-May 09
From: Australia


I am using something like the following lines of code below, the trouble is I don't get an error code when it falls over, but instead it gives me a huge msgbox stating that it could be this or that possibly due to file violations etc!etc! ..Can someone please tell me how can I trap this type of message properly!

Many thanks for reading!


CODE
      If FileExists(strFileName) Then
      
        'File Exists in the Location
        On Error GoTo Err1
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
        "tblImporteddata", StrfileAndPath & ".xlsx", True, "2018NewData!A1:H1000"

err1:
bla bla
msgbox " The data is not in the correct format, Data not imported!"
exit sub

This post has been edited by gint32: Feb 23 2018, 12:01 PM
Go to the top of the page
 
doctor9
post Feb 23 2018, 01:45 PM
Post#2


UtterAccess Editor
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


gint32,

My best guess is that your table has one or more fields marked as Required or are part of a Primary/Composite key which is causing the error. Try importing a troublesome file manually into your test environment and then compare the successfully-imported records to the original worksheet to see if you can spot what's going wrong with the rows that didn't get imported. Then, once you've figured that out, you can use VBA to open the file in an instance of Excel (in the background) and search for those problems BEFORE you import the data with the TransferSpreadsheet code. If any problem is encountered, you can then have a detailed messagebox along the lines of "The file you're trying to import is missing data in cell C147. This data is required before import. Aborting."

(This example requires the Microsoft Excel x.x Object Library to work.)
CODE
Public Function ErrorFree(strPathAndFilename As String) As Boolean

    Dim xlApp As Excel.Application, wb As Excel.Workbook, ws As Excel.Worksheet
    Dim lngRow As Long, varLastValue As Variant

    ErrorFree = True

'   Open a new instance of Excel
    Set xlApp = CreateObject("Excel.Application")
'   Open the workbook
    Set wb = xlApp.Workbooks.Open(strPathAndFilename)
'   Make a reference to the data worksheet
    Set ws = wb.Sheets("NameOfSheetWithDataGoesHERE")
    
'   Sort the data by column D to make searching for duplicate values easier
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("D2:D" & ws.UsedRange.Rows.Count), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A1:D" & ws.UsedRange.Rows.Count)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
'   Loop through all of the rows containing data
    For lngRow = 2 To ws.UsedRange.Rows.Count
        
'       Check to make sure column C isn't missing any data
        If ws.Cells(lngRow, 3) = "" Then
            MsgBox "Missing data from C" & lngRow & " - Aborting."
            ErrorFree = False
            GoTo Done
        End If
    
'       Make sure there are no duplicate values in column D
        If ws.Cells(lngRow - 1, 4) = ws.Cells(lngRow, 4) Then
            MsgBox "There are duplicate values in column D.  Aborting."
            ErrorFree = False
            GoTo Done
        End If
    
    Next lngRow
    
Done:
    wb.Close False  'Close without saving changes
    xlApp.Quit
    Set ws = Nothing
    Set wb = Nothing
    Set xlApp = Nothing

End Function


Use this function just like you're using FileExists.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
gint32
post Feb 24 2018, 07:19 AM
Post#3



Posts: 244
Joined: 8-May 09
From: Australia


Hi and Thanks,

I wasn't to sure how to amend this to suit my existing VBA, so I amended it slightly and then just pasted your code into my exist VBA (directly above the line in question) which is as you know is the
QUOTE
DoCmd.TransferSpreadsheet ......


I now find that it does pre-check and also sorts the given columns as you specified, but I still have the same question, how can I error trap if possible, so I don't get the message about the file violations(which I found out to be) due to the temp existing table (tblImporteddata), having previously imported the very same data all because it fell over and never got to the delete query or even the update query which transfers it to the live data (Staff Records).

Now I know I should clear the temp table after each upload, but if it falls over on the first upload then the rest of the clearing(deletes etc ) will not run, also if a users runs the code a second time around uploading the very same data to the temp upload table I still get the error message, which in the real world this may happen, it'll still produce this message that I mentioned in my OP.
So your code is good for checking the data, before running the docmd.transfer .....but if it still falls over I still get the same message. Is there not a way of getting the error code for this huge Key violations message, how do you get or find it? see picture attached.





QUOTE
Dim xlApp As Excel.Application, wb As Excel.Workbook, ws As Excel.Worksheet
Dim lngRow As Long, varLastValue As Variant
Dim errorFree As Boolean
If errorFree = False Then
errorFree = True

'xlApp.Quit
Set ws = Nothing ' t
Set wb = Nothing ' t
Set xlApp = Nothing 't

' Open a new instance of Excel
Set xlApp = CreateObject("Excel.Application")
' Open the workbook
Set wb = xlApp.Workbooks.Open(StrfileAndPath)
' Make a reference to the data worksheet
Set ws = wb.Sheets("2018NewOfficers")
Stop

' Sort the data by column D to make searching for duplicate values easier
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("B2:B" & ws.UsedRange.Rows.count), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("A1:H" & ws.UsedRange.Rows.count)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

' Loop through all of the rows containing data
For lngRow = 2 To ws.UsedRange.Rows.count

' Check to make sure column B isn't missing any data
If ws.Cells(lngRow, 2) = "" Then
MsgBox "Missing data from C" & lngRow & " - Aborting."
errorFree = False
GoTo Done
End If

' Make sure there are no duplicate values in column D
If ws.Cells(lngRow - 1, 2) = ws.Cells(lngRow, 2) Then
MsgBox "There are duplicate values in column C. Aborting."
errorFree = False
GoTo Done
End If

Next lngRow

Done:
wb.Close False 'Close without saving changes
xlApp.Quit
Set ws = Nothing ' t
Set wb = Nothing ' t
Set xlApp = Nothing 't

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
"tblImporteddata", StrfileAndPath & ".xlsx", True, "2018NewData!A1:H1000"


Go to the top of the page
 
gint32
post Feb 25 2018, 08:52 PM
Post#4



Posts: 244
Joined: 8-May 09
From: Australia


Many thanks , As I said I've tried that and had no issues with implementing your suggestions, so still get the msgbox..see screen shot/picture, any other ideas on how to trap this msgbox
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th June 2018 - 09:33 PM