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
> Error 3274 When Trying To Import Excel To Access, Access 2010    
 
   
nenakat
post Aug 9 2018, 11:49 AM
Post#1



Posts: 13
Joined: 16-August 17



Hi:

I have VBA to import multiple Excel files into the Access database, and then save the file to a different location. This is based on a post I had read here from 'Tina T' notworthy.gif - and it has been working fine for the past two months. Now, some days when clicking on the button to import the file, the infamous 'Error 3274 - External table is not in the expected format' is rearing its head. pullhair.gif

I have researched both here in UtterAccess, and Googled it. I have relinked to the file, have saved the Excel file from .xlsx to .xlx, redone the Business Objects file that produces the Excel file - and still this error will show up sometimes, and other days it is smooth sailing.

Does anyone know what else I can try to resolve this?

Here is the VBA that I use. It is called from a Macro that first downloads text file(s), then this Excel file(s), then sends out a comparison report via email


Public Function ImportFacetsDE()
On Error GoTo ImportFacetsDE_Error

Dim strOrigFileName As String
Dim strNetWorkFolder As String
Dim strFinishedFolder As String

'Identify folder where report from Facets is located
strNetWorkFolder = "\\somepath...\PHP-Daily Direct Entry Reports\"
strFinishedFolder = "\\somepath...\PHP-Daily Direct Entry Reports\Verified Reports\"

strOrigFileName = Dir(strNetWorkFolder & "*.xls*")

Do Until strOrigFileName = ""
Name strNetWorkFolder & strOrigFileName As strNetWorkFolder & "MyXcelImport.xlsx"
CurrentDb.Execute "qryAppDE"
Name strNetWorkFolder & "myXcelImport.xls*" As strFinishedFolder & strOrigFileName
strOrigFileName = Dir
Loop

ImportFacetsDE_Error:
If Err.Number = 0 Then
Resume Next
ElseIf Err.Number = 20 Then
Resume Next
Else
MsgBox "An error occurred when trying to import Business Objects report. " & _
"Please report the error to Claims Business Analyst as follows." & vbCrLf & _
"Error #: " & Err.Number & vbCrLf & _
"Description: " & Err.DESCRIPTION, _
vbOKOnly Or vbInformation, "OnBase vs Facets"
CANCEL = True
End If
Resume Error_Handler_Exit

Thank you in advance for any help you could provide
Katy
Go to the top of the page
 
theDBguy
post Aug 9 2018, 11:57 AM
Post#2


Access Wiki and Forums Moderator
Posts: 75,777
Joined: 19-June 07
From: SunnySandyEggo


Hi Katy,

Welcome to UtterAccess!
welcome2UA.gif

I have been seeing a few of these reports lately, so it makes me think it's a bug. However, I would have expected it to apply only to 2016, but you're using 2010.

Did you say it only happens sometimes, not all the time? If so, does it always fail on the same specific file(s)?

If it is a bug, I am not sure what we can do to fix it.
Go to the top of the page
 
nenakat
post Aug 9 2018, 05:05 PM
Post#3



Posts: 13
Joined: 16-August 17



Hi DbGuy:

yes, it happens on the same file. I have a Business Objects report that is saved automatically in .xlsx format to the folder, then in the morning, I click on the button to import.
Yesterday the import worked fine, no problem. Today, the error 3274 appeared when trying to import the Excel file. crazy.gif

Go to the top of the page
 
hpoyta
post Mar 7 2019, 03:12 PM
Post#4



Posts: 36
Joined: 6-April 05



I'm having this same issue. was a solution ever found for this?

The import code I have is working on my old machine, but not on the new one. There are 4 uploads and only 1 is failing, so its not an issue with the importing as a whole -- it is just specific to this file. I've tried compact/repairing the database; i tried re-saving the file; i even tried reordering the field names so that they aligned with the order in the Excel file (this was not necessary on the old machine where the import does work). There is no difference between the Office version, Access file, or the upload file (XLS). I'm not sure if there is a difference in the OS (both are Win 10, but maybe different minor versions), but I don't see how that would impact this situation.


Go to the top of the page
 
sxschech
post May 8 2019, 02:27 PM
Post#5



Posts: 12
Joined: 2-October 18



Not sure if this will help in your circumstance as this was set up to adjust cell formats for easier import to help avoid making assumptions about the cell data type, but maybe it might clean up something to ease your import process? File will be output with txt.xlsx so that it does not replace the original file. Also the code runs without needing to set a reference to excel.

CODE
Public Sub ExcelToText(ByVal stfilepath As String)
'http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28277710.html
'http://www.developark.com/1529_17747527/
'http://www.mrexcel.com/forum/excel-questions/235867-using-column-numbers-define-range-visual-basic-applications.html
'Change the formatting of an excel file to text for all used cols (last col)
'Save as to another name (filename + txt.xlsx)
'Need to set a reference to Excel (tools references)
'20150228 ss
'Incorporated Version Check so file does not need to be reopened before import
'20150521
'Changed so no longer needs to set reference to excel
'Found that last col wasn't getting all data, switched to .cells without
'range in order to capture entire sheet.
'Changed to convert all reformatted files into Excel 2007-2013 format xlsx.
'-previously kept reformat in original version, but was causing issues down
'the road.  Simplifies import process as all imported files are now the
'same format.
'201506??
'Added option to select a specific tab when doing a direct import
'need to reset sheet number to zero in the calling function when
'complete so that it won't use the wrong sheet number for normal
'operations
'http://stackoverflow.com/questions/15072098/saving-individual-sheet-in-excel-vba
'http://stackoverflow.com/questions/20632514/issue-on-saving-active-sheet-as-new-worksheet
'20160226
'Added code to trim the data to be imported
'20160227
    Dim objapp As Object
    Dim wb As Object
    Dim lastCol As Long
    Dim ExcelVersion As Long
    Dim sheetIndex As Integer
    
    If globalintSheetIndex <> 0 Then
        sheetIndex = globalintSheetIndex
    Else
        sheetIndex = 1
    End If
    
    On Error Resume Next
    Set objapp = CreateObject("Excel.Application")
    objapp.Visible = True
    
    If Dir(stfilepath) Then
        Set wb = objapp.Workbooks.Open(stfilepath, True, False)
    End If
    With wb.sheets(sheetIndex)
        .Activate
        .Cells.NumberFormat = "@"
        If Forms!frmutilities.cboImportFile = "QuickLook" Then 'trim the cells of spaces
            'http://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba
            'http://www.thespreadsheetguru.com/the-code-vault/a-fast-way-to-clean-trim-cell-values-with-vba-code
            'Hide Excel Window so that can see the status of cell updates on the Process button of Access
            '20161223
            objapp.Visible = False
            lastrow = .Range("A1").currentregion.rows.Count
            lastCol = .Range("A1").currentregion.Columns.Count
            For Each area In .Range(.Cells(1, 1), .Cells(lastrow, lastCol))
                area.value = .Evaluate("IF(ROW(" & area.ADDRESS & "),CLEAN(TRIM(" & area.ADDRESS & ")))")
                stCaption = Forms!frmutilities.btnProcess.caption
                Forms!frmutilities.btnProcess.ForeColor = vbRed
                Forms!frmutilities.btnProcess.caption = "Cleaning Cell: " & area.ADDRESS
            Next area
                Forms!frmutilities.btnProcess.caption = stCaption
                Forms!frmutilities.btnProcess.ForeColor = vbBlack
                objapp.Visible = True
        End If
        
        .Name.Delete
    End With
        'wb.Sheets(1).Range("A:BB").NumberFormat = "@"
        'http://www.rondebruin.nl/win/s5/win001.htm
        'filefomatstuff 20150405
    ExcelVersion = wb.FileFormat
    If globalintSheetIndex <> 0 Then
        wb.activesheet.Copy
        objapp.activeworkbook.SaveAs FilePath(stfilepath) & FileNameNoExt(stfilepath) & "txt.xlsx", FileFormat:=51
        wb.Close savechanges:=False
    Else
        wb.SaveAs FilePath(stfilepath) & FileNameNoExt(stfilepath) & "txt.xlsx", FileFormat:=51
        wb.Close 'savechanges:=False
    End If
    
    objapp.Quit

    Set objapp = Nothing
    'Select Case ExcelVersion
    '    Case 39
    '        ExcelVersion = 5 'excel7
    '    Case 50, 51
    '        ExcelVersion = 9 'excel12 (2007-2013, xlsx)
    '    Case 56
    '        ExcelVersion = 8 'excel8 (97-2003 format in Excel 2007-2013, xls)
    'End Select
    'globalintExcelVer = ExcelVersion
End Sub

This post has been edited by sxschech: May 8 2019, 02:32 PM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd July 2019 - 01:59 AM