访问VBa-在Excel列中循环 [英] Access VBa - loop through Excel columns
本文介绍了访问VBa-在Excel列中循环的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在从Access循环浏览Excel文件时遇到麻烦.这是我的代码:
I'm having troubles with looping through Excel file from Access. Here is my code:
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
无论我做什么,我总是在这一行中得到一个错误:
Whatever I do, I always get an error in this line:
i = XcelApp.Rows(1).Find(What:="Število", LookIn:=xlValues, Lookat:=xlWhole).Column
或这个:
For i = 2 To UBound(x)
错误是对象不支持此属性或方法"或对象变量或未设置块变量".我该如何解决这个问题,有人知道吗?
Errors are "object doesn't support this property or method" or "object variable or with block variable not set". How can I fix this, does anybody have a clue ??
推荐答案
我已努力使其正常运行:
I have manged to get It working:
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,现在它可以正常工作了.
I just had to remove ExcelBook from code, now It works fine.
这篇关于访问VBa-在Excel列中循环的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文