访问VBa-在Excel列中循环

幸运卢克82

我在从Access循环遍历Excel文件时遇到麻烦。这是我的代码:

     Sub Xceltest()

        Dim XcelApp As Object
        Dim XcelBook As Excel.Workbook
        Dim x, i
        Set XcelApp = CreateObject("Excel.Application")
        XcelApp.ScreenUpdating = False

Set XcelBook = XcelApp.Workbooks.Open("C:\Users\Lucky\Desktop\Test\Sample.xlsx")

   With XcelBook

i = XcelApp.Rows(1).Find(What:="Število", LookIn:=xlValues, Lookat:=xlWhole).Column

x = XcelApp.Range(XcelApp.Cells(1, i), XcelApp.Cells(XcelApp.Rows.Count, i).End(xlUp)).Value

        For i = 2 To UBound(x)
            If Not IsNumeric(x(i, 1)) Then

            ExcelApp.Quit
            Set ExcelApp = Nothing
            MsgBox "This Excel file is not valid"
        : Exit Sub

            End If

        Next i

        End With

        XcelApp.Quit
        XcelApp = Nothing

        End Sub

无论我做什么,我总是在这行中得到一个错误:

i = XcelApp.Rows(1).Find(What:="Število", LookIn:=xlValues, Lookat:=xlWhole).Column

或这一个:

 For i = 2 To UBound(x)

错误是“对象不支持此属性或方法”或“对象变量或未设置块变量”。我该如何解决这个问题,有人知道吗?

幸运卢克82

我已经设法使它工作:

 Sub Xceltest()

        Dim XcelApp As Object

        Dim x, i
        Set XcelApp = CreateObject("Excel.Application")
        XcelApp.ScreenUpdating = False

        XcelApp.Workbooks.Open("C:\Users\Lucky\Desktop\Test\Sample.xlsx")

   With XcelApp

i = XcelApp.Rows(1).Find(What:="Število", LookIn:=xlValues, Lookat:=xlWhole).Column

x = XcelApp.Range(XcelApp.Cells(1, i), XcelApp.Cells(XcelApp.Rows.Count, i).End(xlUp)).Value

        For i = 2 To UBound(x)
            If Not IsNumeric(x(i, 1)) Then

            ExcelApp.Quit
            Set ExcelApp = Nothing
            MsgBox "This Excel file is not valid"
        : Exit Sub

            End If

        Next i

        End With

        XcelApp.Quit
        XcelApp = Nothing

        End Sub

我只需要从代码中删除ExcelBook,现在就可以正常工作了。

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章