英文:
Variant variable type receives runtime error 13
问题
我正在用VBA编写一个小脚本，它执行基本的下钻操作，从找到的文件中收集数据并将其放入数组中。我知道我找到了正确的文件，并且我确保重新调整数组大小以容纳新的添加（是的，在尝试访问数据之前我打开了文件）。无论如何，当我尝试访问工作表中的单元格值时，我都会遇到数据类型不匹配（运行时错误13）。这发生在我知道是xls文件的第一个访问的文件上。
对于每个 oFile 在 oFolder.Files 中
长度 = UBound(Inspections)
ReDim Preserve Inspections(length + 1)
ReDim Preserve fridgeDoc(length + 1)
ReDim Preserve fridgeWalk(length + 1)
ReDim Preserve nrgEff(length + 1)
ReDim Preserve mheDoc(length + 1)
ReDim Preserve mheWalk(length + 1)
ReDim Preserve buildingDoc(length + 1)
ReDim Preserve buildingWalk(length + 1)
Workbooks.Open oFile
'错误发生在下一行
burner = Workbooks(oFile).Worksheets(1).Cells(11, 3)
MsgBox (VarType(burner))
这更加令人困惑，因为出于诊断目的，我创建了一个 Variant 类型的 burner 变量，我不知道什么可能会违反 Variant 数据类型。如果我能得到任何关于进一步调试技巧的提示，那将是非常棒的。附带本地化代码片段。谢谢！
英文:
I am writing a small script in VBA that does a basic drilldown and collects data from the files it finds and puts them into arrays. I know I am finding the right files and I am making sure to redim my arrays to accommodate new additions (and yes I open the files before trying to access data). Regardless I'm getting a data type mismatch (runtime error 13) when I try to access cell values in the sheets. This occurs on the first file accessed which I know is a xls file.
For Each oFile In oFolder.Files
length = UBound(Inspections)
ReDim Preserve Inspections(length + 1)
ReDim Preserve fridgeDoc(length + 1)
ReDim Preserve fridgeWalk(length + 1)
ReDim Preserve nrgEff(length + 1)
ReDim Preserve mheDoc(length + 1)
ReDim Preserve mheWalk(length + 1)
ReDim Preserve buildingDoc(length + 1)
ReDim Preserve buildingWalk(length + 1)
Workbooks.Open oFile
'error occurs at line directly below
burner = Workbooks(oFile).Worksheets(1).Cells(11, 3)
MsgBox (VarType(burner))
This is even more puzzling because for diagnostic purposes I made a burner variable of Variant type and I don't know what could even violate a variant data type. If I could get any tips on further debugging techniques that'd be awesome. Localized code snippet included. Thanks!
答案1
得分: 1
你应该使用：
burner = Workbooks(oFile.Name).Worksheets(1).Cells(11, 3).Value
在Excel中打开的工作簿是根据它们的名称引用的，而不是根据它们的完整路径。
但最好的做法是，正如BigBen所指出的，可以这样做：
With Workbooks.Open(oFile)
burner = .Worksheets(1).Cells(11, 3).Value
.Close False
End with
英文:
You should use:
burner = Workbooks(oFile.Name).Worksheets(1).Cells(11, 3).Value
Workbooks open in excel are referenced by their name, not by their full path.
Ideally though, as noted by BigBen, it's tidier to do something like:
With Workbooks.Open(oFile)
burner = .Worksheets(1).Cells(11, 3).Value
.Close False
End with
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论