Error 2042 in Excel ActiveX Control LT 11/7/2008 9:10:01 PM 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! [CODE] 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) [/CODE]

Re: Error 2042 in Excel ActiveX Control "Arvin Meyer [MVP]" <arvinm[ at ]mvps.invalid> 11/8/2008 3:27:42 AM 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 "LT" <LT[ at ]discussions.microsoft.com> wrote in message news:419D965E-092C-4626-A830-8A6FA34340A5[ at ]microsoft.com... [Quoted Text] > 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! > > [CODE] > 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) > [/CODE]

Re: Error 2042 in Excel ActiveX Control LT 11/10/2008 3:10:12 PM 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) "Arvin Meyer [MVP]" wrote: [Quoted Text] > 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 > > > "LT" <LT[ at ]discussions.microsoft.com> wrote in message > news:419D965E-092C-4626-A830-8A6FA34340A5[ at ]microsoft.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! > > > > [CODE] > > 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) > > [/CODE] > > >