Hey Rod,
I was re-reading the thread today and decided to check about the excel.dll as I don't know why I had assumed that it was there but then just found out that it wasn't therefore the problems I was having!! doh!!
Anyway, the code below is what I am using and it is working:
Code:
Public Sub importFDU()
On Error GoTo Err_Handler
Dim wbFDU As Workbook
Dim objExcelApp As Excel.Application
Dim db As Database
Dim rstCustomer As Recordset
Dim columnW As String
Dim searchInA As String
Dim A As String
Dim W As String
Dim iCounter As Integer
Set db = openDatabase(sourceDb, False, False, passwordDB)
Set rstCustomer = db.OpenRecordset("Select * from tblCustomer ")
'--create Excel object:
Set objExcelApp = New Excel.Application
objExcelApp.Workbooks.Open ("C:\FDU\BIR_FDU.XLS")
Set wbFDU = objExcelApp.Workbooks(1)
If rstCustomer.EOF = False Then
rstCustomer.MoveFirst
Do While rstCustomer.EOF = False
iCounter = 1
A = "A" & iCounter
W = "W" & iCounter
' open the source workbook, read only
Do Until wbFDU.Worksheets("Sheet1").Range(A).Formula = ""
searchInA = wbFDU.Worksheets("Sheet1").Range(A).Formula
If rstCustomer!clientID = searchInA Then
columnW = wbFDU.Worksheets("Sheet1").Range(W).Formula
rstCustomer.Edit
rstCustomer.Fields("premiumFromFDU") = columnW
rstCustomer.Update
End If
iCounter = iCounter + 1
A = "A" & iCounter
W = "W" & iCounter
Loop
rstCustomer.MoveNext
Loop
End If
wbFDU.Close False ' close the source workbook without saving any changes
Set wbFDU = Nothing ' free memory
rstCustomer.Close
Set rstCustomer= Nothing
db.Close
Set db = Nothing
Exit Sub
Exit_Program:
DoCmd.Quit
Err_Handler:
MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
"Error Number " & err.Number & vbCrLf & _
"Error Description" & err.Description & vbCrLf & _
"Your application will close!", _
vbCritical, "An Error has Occured"
Resume Exit_Program
End Sub
Thanks for your help mate!!! Really appreciate it!!