using excel file as recordset

[Luis]

Hello.
I have a link to an Excel file and i want to use this file as recordset on a
module.
The problem is that when i link the file certain records get the value #NUM!.
When i try to assign any of these records to a variable i get a Numeric
Field Overflow error message.
I thought that i could create a link specification where i force the field
to be Text, but since it is an Excel file i cannot create a specification.

I've created an Error Handler to ignore these values but some how this error
handler only works to the first #NUM! value that appears.

Anyone can help me on this problem?

thanks

Luis

 

[Klatuu]

There are a couple of possible solutions here. One would be to save the Excel
worksheet to a csv file, set up your spec for it, and use it instead of the
xls version.

Another, if that is not practical, is to use automation to read throught the
recordset and correct values.

My preferred way is to create a table with the structure you need and import
the Excel file into it. In once, case, I had to use a combination of the
second two options. The data were so unreliable, I had to use automation to
read trough the Excel file, clean the data, and write it to a table.

Here is some sample code:

'Open Excel
On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo LoadAdjustedActuals_Err
DoEvents
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)
xlBook.Worksheets("Actuals_res_export").Activate
ActiveSheet.Range("F3").Select
Selection.End(xlDown).Select
intLastRow = Selection.Row
If intLastRow = 0 Then
MsgBox "No Data to Import" & vbNewLine & "Spreadsheet may be open by
another user", _
vbExclamation + vbOKOnly, "Import Adjusted Actuals"
GoTo LoadAdjustedActuals_Exit
End If

'Start the Loop
For intRowCount = 3 To intLastRow
rstAccess.AddNew
For intColCount = 6 To 42
rstAccess.Fields(intColCount - 6) = _
IIf(intColCount < 26, ActiveSheet.Cells(intRowCount,
intColCount), _
Nz(ActiveSheet.Cells(intRowCount, intColCount), 0))
Next intColCount
rstAccess.Update
Next intRowCount
Me.txtAccessDollars = DSum("[CURRENT MO $'s]", "AdjustedActuals")
Me.txtAccessRows = rstAccess.RecordCount
strCurrDollarsRange = "AP3:AP" & CStr(intLastRow)
Me.txtXlDollars =
xlApp.WorksheetFunction.Sum(ActiveSheet.Range(strCurrDollarsRange))
Me.txtXlRows = intLastRow - 2
MsgBox "Import Complete", vbExclamation + vbOKOnly, "Import Adjusted
Actuals"