变量的变体类型会收到运行时错误 13。

huangapple go评论89阅读模式
英文:

Variant variable type receives runtime error 13

问题

我正在用VBA编写一个小脚本,它执行基本的下钻操作,从找到的文件中收集数据并将其放入数组中。我知道我找到了正确的文件,并且我确保重新调整数组大小以容纳新的添加(是的,在尝试访问数据之前我打开了文件)。无论如何,当我尝试访问工作表中的单元格值时,我都会遇到数据类型不匹配(运行时错误13)。这发生在我知道是xls文件的第一个访问的文件上。

  1. 对于每个 oFile oFolder.Files
  2. 长度 = UBound(Inspections)
  3. ReDim Preserve Inspections(length + 1)
  4. ReDim Preserve fridgeDoc(length + 1)
  5. ReDim Preserve fridgeWalk(length + 1)
  6. ReDim Preserve nrgEff(length + 1)
  7. ReDim Preserve mheDoc(length + 1)
  8. ReDim Preserve mheWalk(length + 1)
  9. ReDim Preserve buildingDoc(length + 1)
  10. ReDim Preserve buildingWalk(length + 1)
  11. Workbooks.Open oFile
  12. '错误发生在下一行
  13. burner = Workbooks(oFile).Worksheets(1).Cells(11, 3)
  14. 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.

  1. For Each oFile In oFolder.Files
  2. length = UBound(Inspections)
  3. ReDim Preserve Inspections(length + 1)
  4. ReDim Preserve fridgeDoc(length + 1)
  5. ReDim Preserve fridgeWalk(length + 1)
  6. ReDim Preserve nrgEff(length + 1)
  7. ReDim Preserve mheDoc(length + 1)
  8. ReDim Preserve mheWalk(length + 1)
  9. ReDim Preserve buildingDoc(length + 1)
  10. ReDim Preserve buildingWalk(length + 1)
  11. Workbooks.Open oFile
  12. 'error occurs at line directly below
  13. burner = Workbooks(oFile).Worksheets(1).Cells(11, 3)
  14. 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所指出的,可以这样做:

  1. With Workbooks.Open(oFile)
  2. burner = .Worksheets(1).Cells(11, 3).Value
  3. .Close False
  4. 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:

  1. With Workbooks.Open(oFile)
  2. burner = .Worksheets(1).Cells(11, 3).Value
  3. .Close False
  4. End with

huangapple
  • 本文由 发表于 2023年6月30日 04:31:32
  • 转载请务必保留本文链接:https://go.coder-hub.com/76584437.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定