Excel VBA运行时错误'424'：对象尝试复制TextBox时需要 [英] Excel VBA Run-time error '424': Object Required when trying to copy TextBox

问题描述

我正在尝试将文本框的内容从一个工作簿复制到另一个。将第一个工作簿中的单元格值复制到第2个时，我没有问题，但是当我需要对象需要错误时尝试复制文本框。这个宏正在从包含我要复制的数据的工作簿中运行。使用 Excel 2007 代码：

  Sub UploadData（）
 Dim xlo As New Excel.Application 
 Dim xlw As New Excel.Workbook 
 Set xlw = xlo.Workbooks.Open（c：\myworkbook.xlsx）
 xlo.Worksheets（1 ）.Cells（2，1）= Range（d4）。Value'复制单元格内容（这样很好）
 xlo.Worksheets（1）.Cells（2，2）= TextBox1.Text'我需要的对象错误
 xlw.Save 
 xlw.Close 
设置xlo =没有
设置xlw =没有
结束Sub 
 
解决方案
您的宏的问题是，一旦您在代码示例中打开了目标工作簿（ xlw ），就将其设置为 ActiveWorkbook 对象，您会收到错误，因为   要解决此问题，您可以在打开另一个之前，将实际的工作簿的引用对象定义。
  Sub UploadData（）
 Dim xlo As New Excel.Application 
 Dim xlw As New Excel.Workbook 
 Dim myWb as Excel.Workbook 
 
设置myWb = ActiveWorkbook 
设置xlw = xlo.Workbooks.Open（c：\myworkbook.xlsx）
 xlo.Worksheets（1）.Cells（2，1）= myWb。 ActiveSheet.Range（d4）。值
 xlo.Worksheets（1）.Cells（2，2）= myWb.ActiveSheet.TextBox1.Text 
 
 xlw.Save 
 xlw.Close 
 Set xlo = Nothing 
 Set xlw = Nothing 
 End Sub 
  
如果您愿意，还可以使用 myWb.Activate 将您的主要工作簿放回活动中。如果您使用工作表对象执行此操作，它也可以正常工作。使用一个或另一个主要取决于你想做什么（如果有多张表等）。
 
I'm attempting to copy the contents of a text box from one workbook to another.  I have no problem copying cell values from the first workbook to the 2nd, but I get an object required error when I attempt to copy the text box.  This macro is being run from the workbook containing the data I want copied.  Using Excel 2007 Code:
Sub UploadData()
    Dim xlo As New Excel.Application
    Dim xlw As New Excel.Workbook
    Set xlw = xlo.Workbooks.Open("c:\myworkbook.xlsx")
    xlo.Worksheets(1).Cells(2, 1) = Range("d4").Value 'Copy cell content (this works fine)
    xlo.Worksheets(1).Cells(2, 2) = TextBox1.Text 'This gives me the object required error
    xlw.Save
    xlw.Close
    Set xlo = Nothing
    Set xlw = Nothing
End Sub
Thanks for any help.
解决方案
The problem with your macro is that once you have opened your destination Workbook (xlw in your code sample), it is set as the ActiveWorkbook object and you get an error because TextBox1 doesn't exist in that specific Workbook. To resolve this issue, you could define a reference object to your actual Workbook before opening the other one.
Sub UploadData()
    Dim xlo As New Excel.Application
    Dim xlw As New Excel.Workbook
    Dim myWb as Excel.Workbook

    Set myWb = ActiveWorkbook
    Set xlw = xlo.Workbooks.Open("c:\myworkbook.xlsx")
    xlo.Worksheets(1).Cells(2, 1) = myWb.ActiveSheet.Range("d4").Value
    xlo.Worksheets(1).Cells(2, 2) = myWb.ActiveSheet.TextBox1.Text

    xlw.Save
    xlw.Close
    Set xlo = Nothing
    Set xlw = Nothing
End Sub
If you prefer, you could also use myWb.Activate to put back your main Workbook as active. It will also work if you do it with a Worksheet object. Using one or another mostly depends on what you want to do (if there are multiple sheets, etc.).

                        
这篇关于Excel VBA运行时错误'424'：对象尝试复制TextBox时需要的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！