如果工作簿和工作表不活动，则无法使用范围 [英] Can't work with ranges if workbook and sheet are not active

问题描述

当我尝试将值从一个工作簿导入到另一个工作簿时，我收到应用程序或对象定义的错误。我已经能够通过明确激活工作簿并在引用每个范围之前选择工作表来解决问题，但是如果可能的话我想避免这种情况。代码中的这两个工作簿都是开放的。任何想法？

这会为我生成错误：

  Dim wbImport作为工作簿
 Dim wbReceive As Workbook 
 Const sExcept =Sheet2 Name
 Const sSht =Sheet1 Name
 Dim rExceptions As Range 
 
 wbReceive。 Sheets（sExcept）.Rows（1）.Insert shift：= xlDown 
设置rExceptions = wbImport.Sheets（sSht）.Range（Cells（rCell.Row，iHeadCol），Cells（rCell.Row，iLastCol））
 wbReceive.Sheets（sExcept）.Range（Cells（1，iHeadCol），Cells（1，iLastCol））。Value = rExceptions.Value'错误发生在这里
  

运行正常，但我想避免。选择和。激活

  wbReceive.Sheets（sExcept）.Rows（1）.Insert shift：= xlDown 
 wbImport.Activate 
 wbImport.Sheets（sSht）.Select 
设置rExceptions = wbImport.Sheets（sSht）.Range（Cells（rCell.Row，iHeadCol），Cells（rCell.Row，iLastCol ））
 wbReceive.Activate 
 wbReceive.Sheets（sExcept）。选择
 wbReceive.Sheets（sEx Cept）.Range（Cells（1，iHeadCol），Cells（1，iLastCol））。Value = rExceptions.Value 
  

$ b $在调试时，它看起来像 wbReceive.Sheets（sExcept）行中引用的单元格实际上引用了 wbReceive 工作簿。不知道为什么会这样，因为wb和sheet被明确引用？

解决方案

 设置rExceptions = wbImport.Sheets（sSht）.Range（Cells（rCell.Row，iHeadCol），_ 
 Cells（rCell.Row，iLastCol））
  / pre> 
 
 
您有资格范围，但不是单元格：任何一个如果不符合特定工作表将引用ActiveSheet。有些反直觉，（合格）包装范围不会casade下来... 
 
 
 
尝试这是：
 使用wbImport.Sheets（sSht）
设置rExceptions = .Range（.Cells（rCell.Row， iHeadCol），_ 
 .Cells（rCell.Row，iLastCol））
结束
  
 
I am getting an Application or Object Defined Error when I try to import values from one workbook to another. I have been able to resolve it by explicitly activating the workbooks and selecting the sheets before referencing the range on each, but I would like to avoid that if possible. Both workbooks are open at this point in the code. Any thoughts?

This generates errors for me:
Dim wbImport As Workbook
Dim wbReceive As Workbook
Const sExcept = "Sheet2 Name"  
Const sSht = "Sheet1 Name"
Dim rExceptions As Range

wbReceive.Sheets(sExcept).Rows(1).Insert shift:=xlDown
Set rExceptions = wbImport.Sheets(sSht).Range(Cells(rCell.Row, iHeadCol), Cells(rCell.Row, iLastCol))
wbReceive.Sheets(sExcept).Range(Cells(1, iHeadCol), Cells(1, iLastCol)).Value = rExceptions.Value 'error occurs here
This runs fine, but I'd like to avoid the .Select and .Activate
wbReceive.Sheets(sExcept).Rows(1).Insert shift:=xlDown
wbImport.Activate
wbImport.Sheets(sSht).Select
Set rExceptions = wbImport.Sheets(sSht).Range(Cells(rCell.Row, iHeadCol), Cells(rCell.Row, iLastCol))
wbReceive.Activate
wbReceive.Sheets(sExcept).Select
wbReceive.Sheets(sExcept).Range(Cells(1, iHeadCol), Cells(1, iLastCol)).Value = rExceptions.Value
As I was debugging, it looked like cells referenced in the wbReceive.Sheets(sExcept) line were actually referencing a different sheet in the wbReceive workbook. Not sure why that would be the case though, since the wb and sheet were explicitly referenced?
解决方案
Set rExceptions = wbImport.Sheets(sSht).Range(Cells(rCell.Row, iHeadCol), _
                                              Cells(rCell.Row, iLastCol))
You've qualified Range, but not Cells: either of these if not qualified with a specific sheet will refer to the ActiveSheet. Somewhat counter-intuitively, the (qualified) wrapping Range doesn't "casade" down...

Try this:
With wbImport.Sheets(sSht)
    Set rExceptions =  .Range(.Cells(rCell.Row, iHeadCol), _
                              .Cells(rCell.Row, iLastCol))
End with


                        
这篇关于如果工作簿和工作表不活动，则无法使用范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！