import spreadsheet error 3349

[toryee]

Hi,
I'm trying to import Excel worksheets into Access using VBA, and the destination Access table is a linked table to SQL Server. Some cells in Excel have division by zero error, as #DIV/0!. When such field is encountered, import process gives me the error '3349, numeric field overflow'. The import statement I used is:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "TEMP1", sPath, True, "SHEET1!IMPORT_SECTION"

I would like to have these cells to be imported into Access as blank cells or having the value '0'. I tried setting the Access destination cell using both text and number:double type, but they both generate error 3349. In VBA, I don't want to have 'on error resume next' since it would not import the spreadsheet at all and there are many other cells with numeric values that needs to be imported. I use Access and Excel 2000. Any suggestions?

thanks for any help.
tory

 

[VBslammer]

If you import into a local Access table, Access will nullify any cell errors for you, then you can send the results to your linked SQL Server table:

Sub TestImportDivZero()
  Dim strSQL As String

  'Import into local Access table - nullifies cell errors.
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
      "TEMP1", "C:\DivZero.xls", True, "IMPORT_SECTION"
  
  DoCmd.SetWarnings False
  
  'Copy results into SQL Server table.
  strSQL = "INSERT INTO dbo_Temp1(NAME, AVERAGE) SELECT TEMP1.NAME, TEMP1.AVERAGE FROM TEMP1"
  DoCmd.RunSQL strSQL
  
  'Delete temp local records.
  strSQL = "DELETE * FROM TEMP1"
  DoCmd.RunSQL strSQL
  
  DoCmd.SetWarnings True
  
End Sub

VBSlammer

Unemployed in Houston, Texas

 

[toryee]

Thank you for the help VBSlammer. The code works well with my program.
tory