none
VS 2005使用 Excel Range.Find 問題 RRS feed

  • 問題

  • Private Function SearchInSheet(ByRef NowSht As Excel.Worksheet, ByVal NowRange As String, ByVal NowPinName As String) As Excel.Range

            Dim LastRow, NowRow As Double
            Dim Row1, Row2 As Integer
            Dim XT As String
            Dim xlFormulas As Double
            Dim xlNext, xlByRows, xlpart As Integer

            'Dim SearchInSheet As Excel.Range


            Row1 = 0
            Row2 = 0
            XT = CStr(NowSht.Range(Strings.Left(NowRange, Strings.InStr(NowRange, ":") - 1)).Value)
            If (XT = NowPinName) Then
                SearchInSheet = NowSht.Range(Strings.Left(NowRange, Strings.InStr(NowRange, ":") - 1))
                Exit Function
            End If

            Do
                NowSht.Activate()
                With NowSht.Range(NowRange).Select

                    SearchInSheet = NowSht.Range(NowRange).Find(What:=NowPinName, LookIn:=xlFormulas, _
                     LookAt:=xlpart, SearchOrder:=xlByRows, SearchDirection:=Excel.XlSearchDirection.xlNext, _
                     MatchCase:=False, MatchByte:=False, SearchFormat:=False)

                End With


                If (SearchInSheet Is Nothing) Then
                    Exit Function
                End If

                If (SearchInSheet.Value Is NowPinName) Then
                    Exit Function
                End If

                NowRow = CDbl(Strings.Right(SearchInSheet.Address, Len(SearchInSheet.Address) - InStr(2, SearchInSheet.Address, "$"))) - 1
                Row1 = CInt(NowRow)
                's()
                If (NowRow = LastRow) Then
                    SearchInSheet = Nothing
                    Exit Function
                End If

                NowRange = Strings.Left(NowRange, InStr(2, NowRange, "$")) & Trim(CStr(CDbl(Str(NowRow)) + 1)) & Strings.Right(NowRange, Len(NowRange) - InStr(1, NowRange, ": $" ) + 1)

                If Row1 = Row2 Then
                    SearchInSheet = Nothing
                    Exit Function
                End If

                Row2 = Row1

            Loop While (NowRow < LastRow)
        End Function

     

     

     

    Run到紅色的部份出現下面錯誤訊息

    COMException 未處理

    無效的索引。 (發生例外狀況於 HRESULT: 0x8002000B (DISP_E_BADINDEX))

     

    可以幫我看看哪兒出問題了嗎?

    因為我之前在VB用這樣是可以RUN

    改到VS 2005 就出問題了

    麻煩各位大大可以幫幫忙~~

    2009年1月19日 下午 12:40

解答

所有回覆

  • 查 Excel 線上手冊標準引數順序,直接使用引數傳遞,不要使用命名引數。

     

    在 VBNET 呼叫 COM 有時命名引數會短路...

    2009年1月19日 下午 01:21
  • 不好意思~~

    我應該要再補充一段程式

     

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

            Dim XlsApp As New Excel.Application
            Dim Xlsbok1 As Excel.Workbook
            Dim Xlsbok2 As Excel.Workbook
            Dim XlsSheet1 As Excel.Worksheet
            Dim XlsSheet2 As Excel.Worksheet
            Dim xx As String
            Dim HitRange As Excel.Range

            XlsApp = New Excel.Application
            XlsApp.Visible = True
            Xlsbok1 = XlsApp.Workbooks.Open("F:\xxx\BOM_Explosion_Report_0927.xls")
            Xlsbok2 = XlsApp.Workbooks.Open("F:\xxx\0927.xls")
            XlsSheet1 = CType(Xlsbok1.Worksheets(1), Excel.Worksheet)
            XlsSheet2 = CType(Xlsbok2.Worksheets(1), Excel.Worksheet)

            HitRange = SearchInSheet(CType(XlsSheet1, Excel.Worksheet), "$B$1: $B$30000", CStr(XlsSheet2.Range("B10").Value))
            

    End Sub

     

     

     

     

     

    Private Function SearchInSheet(ByRef NowSht As Excel.Worksheet, ByVal NowRange As String, ByVal NowPinName As String) As Excel.Range

            Dim LastRow, NowRow As Double
            Dim Row1, Row2 As Integer
            Dim XT As String
            Dim xlFormulas As Double
            Dim xlNext, xlByRows, xlpart As Integer

            'Dim SearchInSheet As Excel.Range


            Row1 = 0
            Row2 = 0
            XT = CStr(NowSht.Range(Strings.Left(NowRange, Strings.InStr(NowRange, ":") - 1)).Value)
            If (XT = NowPinName) Then
                SearchInSheet = NowSht.Range(Strings.Left(NowRange, Strings.InStr(NowRange, ":") - 1))
                Exit Function
            End If

            Do
                NowSht.Activate()
                With NowSht.Range(NowRange).Select

                    'SearchInSheet = NowSht.Range(NowRange).Find(What:=NowPinName, LookIn:=xlFormulas, _
                    ' LookAt:=xlpart, SearchOrder:=xlByRows, SearchDirection:=Excel.XlSearchDirection.xlNext, _
                    'MatchCase:=False, MatchByte:=False, SearchFormat:=False)

                    SearchInSheet = NowSht.Range(NowRange).Find(NowPinName)
                End With


                If (SearchInSheet Is Nothing) Then
                    Exit Function
                End If

                If (SearchInSheet.Value Is NowPinName) Then
                    Exit Function
                End If

                NowRow = CDbl(Strings.Right(SearchInSheet.Address, Len(SearchInSheet.Address) - InStr(2, SearchInSheet.Address, "$"))) - 1
                Row1 = CInt(NowRow)
                's()
                If (NowRow = LastRow) Then
                    SearchInSheet = Nothing
                    Exit Function
                End If

                NowRange = Strings.Left(NowRange, InStr(2, NowRange, "$")) & Trim(CStr(CDbl(Str(NowRow)) + 1)) & Strings.Right(NowRange, Len(NowRange) - InStr(1, NowRange, ": $") + 1)

                If Row1 = Row2 Then
                    SearchInSheet = Nothing
                    Exit Function
                End If

                Row2 = Row1

            Loop While (NowRow < LastRow)
        End Function

     

     

    呼叫副程式 SearchInSheet 時,

    NowSht沒有帶到Sheet name, 我再猜是不是這出的問題

    是不是我哪邊宣告或是設定有問題~~

    2009年1月19日 下午 02:16
  • XlsSheet1 = CType(Xlsbok1.Worksheets(1), Excel.Worksheet)

    你這邊指定了 Index ,就不用 Name ,這是一個多型參數。

     

    另外看這個:

    http://forums.microsoft.com/MSDN-CHT/Search/Search.aspx?words=Select+%u907f%u514d&localechoice=31748&SiteID=14&searchscope=forumscope&ForumID=805

    2009年1月19日 下午 05:15