Getting values from Excel worksheet

  • Thread starter Thread starter Ken Warthen
  • Start date Start date
K

Ken Warthen

In an Access 2007 database I'm trying to retrieve some values from an Excel
workbook. My code opens the workbook finds the desired column and row, but
when I try to retrieve the value of the target cell I get an
Application-defined or Object-defined error message. If anyone can point out
where I'm going wrong here, I'd be very appreciative. My code follows.

TIA,

Ken

Public Function fGetPromotionValue(dPromotionWeek As Date, strExcelWorksheet
As String, strExcelRange As String) As Variant
On Error GoTo PROC_ERROR
Dim strFile As String
Dim objXL As Object
Dim xlWB As Object
Dim Cell As Range
Dim strRow As String
Dim strCol As String

strFile = Me.txtConsolidatedPath
Set objXL = CreateObject("Excel.Application")
Set xlWB = objXL.Workbooks.Open(strFile)
objXL.visible = True
With xlWB
Worksheets(strExcelWorksheet).Select
'//Get column for promotion week
For Each Cell In Range("BF4:DX4")
If Cell.Value = dPromotionWeek Then
strRow = Range(strExcelRange).Row
strCol = Cell.Column
If Worksheets(strExcelWorksheet).Range(strRow,
strCol) <> Empty Then
fGetPromotionValue =
Worksheets(strExcelWorksheet).Range(strRow, strCol)
Else
fGetPromotionValue = 0
End If
Exit For
End If
Next Cell
End With
xlWB.Close
Set objXL = Nothing


PROC_EXIT:
Exit Function
PROC_ERROR:
Call ShowError("frmUpdateConsolidatedData", "fGetPromotionValue",
Err.Number, Err.Description, Err.Source)
Resume PROC_EXIT
End Function
 
Try This:

Sub ReadSheetNames(FileToRead)
Dim strFileName As String
Dim varWhat as Variant
Dim XlApp As Excel.Application
Dim xlBook1 As Excel.Workbook
strFileName = FileToRead
Set XlApp = Excel.Application
Set xlBook1 = XlApp.Workbooks.Open(strFileName)
With xlBook1
strWhat = .Worksheets(1).Cells(1, 1).Value
End With
XlApp.Quit

End Sub

Regards

Kevin
 
In the With statement I inadvertently mistyped the variable name.
It should be varWhat.

Kevin
 
The problem with my code occurs when there is no value in the cell. When
there is a value it works fine. I guess my question is how to test for an
empty cell condition. In its current state empty cells produce an error.

Ken
 
Use the NZ() function to convert the null to a zero length string "" or
Single space " ".
 
Back
Top