Opening an Excel file from access

[funloving]

I am trying to open an existing Excel file from an Access and trying to send it a value and based on some calculations it returns some value back to Access which i need to display in the report. the problem is when i am trying to open the excel file it does not open it .

My code is as follows:
Dim objxl As Excel.Application
Dim objwb As Excel.Workbook
Dim objSheet As Excel.Worksheet
Set objxl = CreateObject("Excel.Application")
Set objwb = objxl.Workbooks.Open("c:\work\calculator.xls")

Set objSheet = objwb.Worksheets(1)

objSheet.Activate
objSheet.Cells(9, b).Value = Me.txtAvgCCS
Dim x As Integer
x = objSheet.Cells(15, b).Value
If x <> 0 Then
txtP001 = x
End If
objwb.Close
objxl.Quit
Set objSheet = Nothing
Set objwb = Nothing
Set objxl = Nothing


But it gives the following error "Application defined or object defined error"

When i ran the debug window I found that the objwb does not have any value at all...

How do I go about solving this issue.

Thanks in advance.

 

[funloving]

I just checked that it opens the file and that particular worksheet but it does not input the value into the Excel worksheet

objSheet.Cells(2,9).Value = Me.txtAvgCCS

When i check in the Debug mode : me.txtAvgCCS has a value = 0.1

but that value is not input in Excel that means the above command is not working.

Can you pls help.

Thanks in advance

 

[PHV]

I'd replace this:
objSheet.Cells(9, b).Value = Me.txtAvgCCS
with this:
objSheet.Cells(9, "b").Value = Me.txtAvgCCS
or this:
objSheet.Range("B9").Value = Me.txtAvgCCS

And similarly for "B15"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[funloving]

thanks ..it worked like a charm...