Error 2042 in Excel ActiveX Control

[LT]

Hi everyone,

Has anyone ever used the Excel ActiveX control in their forms or know of a
good reference?

I have the OWC11.Spreadsheet.11 control on a form and I am able to set cell
values but have been struggling with trying to use Excel functions. For
example, I am trying to find the row number of a cell with a certain text
value using the MATCH function and I get an error (2042).

Does anyone know what might be causing that?
Many thanks!

Dim cellReference As Variant
Dim objExcel As Excel.Application

Set objExcel = CreateObject("Excel.Application")
'set objExcelWorkbook = objExcel.Workbooks

cellReference = objExcel.Application.Match("Ancillary",
xlsReceiptContainer.ActiveWorkbook.Worksheets("ETSReceipt").Range("A1:A150"),
False)

 

[Arvin Meyer [MVP]]

I don't think you've reference Excel functions correctly. Here's one that I
wrote that works fine:

Public Function XL360(DateStart As String, DateEnd As String) As Double
Dim objXL As New Excel.Application
XL360= objXL.WorksheetFunction.Days360(DateStart,DateEnd)
Set objXL = Nothing
End Sub

?XL360("1/1/07", "12/31/07")
360

 

[LT]

Thanks Arvin! That definitely got me closer.

I noticed that when I use the MATCH function though it is not finding my
search parameter. It seems to find the second to last cell that is in the
column every single time. I noticed that for one of the words that I am
querying it has an asterix at the start (it's like a header file)... could
that cause a problem?

Dim objExcel As New Excel.Application

cellReference = objExcel.WorksheetFunction.Match("Ancillary:",
xlsReceiptContainer.ActiveWorkbook.Worksheets("ETSReceipt").Range("ETSReceipt!$A1:$A150"), True)

I don't think you've reference Excel functions correctly. Here's one that I
wrote that works fine:

Public Function XL360(DateStart As String, DateEnd As String) As Double
Dim objXL As New Excel.Application
XL360= objXL.WorksheetFunction.Days360(DateStart,DateEnd)
Set objXL = Nothing
End Sub

?XL360("1/1/07", "12/31/07")
360
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi everyone,

Has anyone ever used the Excel ActiveX control in their forms or know of a
good reference?

I have the OWC11.Spreadsheet.11 control on a form and I am able to set
cell
values but have been struggling with trying to use Excel functions. For
example, I am trying to find the row number of a cell with a certain text
value using the MATCH function and I get an error (2042).

Does anyone know what might be causing that?
Many thanks!

Dim cellReference As Variant
Dim objExcel As Excel.Application

Set objExcel = CreateObject("Excel.Application")
'set objExcelWorkbook = objExcel.Workbooks

cellReference = objExcel.Application.Match("Ancillary",
xlsReceiptContainer.ActiveWorkbook.Worksheets("ETSReceipt").Range("A1:A150"),
False)