vba 工作表的Excel Vba复制方法失败

Excel Vba Copy Method of Worksheet fails

提问by JC75

I am trying to copy an excel sheet from one workbook to another without the user seeing anything but I keep getting the error 'Copy Method of Worksheet Class failed'. It seems you cant copy one sheet to another workbook unless the workbook is visible?

我正在尝试将 Excel 工作表从一个工作簿复制到另一个工作簿，而用户没有看到任何内容，但我不断收到错误消息“工作表类的复制方法失败”。除非工作簿可见，否则您似乎无法将一张工作表复制到另一个工作簿？

Thanks for any help.

谢谢你的帮助。

Hers is the code that fails:

她是失败的代码：

Private Sub CommandButton1_Click()

Dim xlapp As Excel.Application
Dim wkBook As Excel.Workbook

'Connect to Excel
Set xlapp = New Excel.Application

'Set workbook and open workbook in directory
Set wkBook = xlapp.Workbooks.Open(sFileName, xlUpdateLinksNever, ReadOnly:=False)


ThisWorkbook.Sheets("Logistics").Copy Before:=wkBook.Sheets(1)

wkBook.Close True
xlapp.Quit
Set xlapp = Nothing

End Sub

回答by assylias

If you do it from Excel, you don't need calls to Excel.Application and this should work:

如果您从 Excel 执行此操作，则不需要调用 Excel.Application，这应该可以工作：

Private Sub CommandButton1_Click()

  Dim wkBook As Workbook

  'Set workbook and open workbook in directory
  Set wkBook = Workbooks.Open(sFileName, xlUpdateLinksNever, ReadOnly:=False)

  ThisWorkbook.Sheets("Logistics").Copy Before:=wkBook.Sheets(1)

  wkBook.Close True

End Sub

If you need something similar to your code, you need to use the current Excel Application for ThisWorkbook to point to the right workbook:

如果您需要类似于您的代码的内容，您需要使用当前 Excel 应用程序来指向正确的工作簿：

Set xlapp = ThisWorkbook.Application

If you need to do that without the user seeing the changes, you can use:

如果您需要在没有用户看到更改的情况下执行此操作，您可以使用：

Application.ScreenUpdating = False
'Insert the rest of the code
Application.ScreenUpdating = True

回答by brettdj

If you want to do it in the background then you should use a controlling workbook to automate bothworkbooks

如果您想在后台执行此操作，则应使用控制工作簿来自动化两个工作簿

Better again run it as a vbsrather than vba

最好再次将其作为vbs而不是vba 运行

1. Copy this code into NotePad
2. Change the path of your source and destination workbooks to suit
3. Save it as a vbsfile, ie "test.vbs"
4. Click on the vbsfile to execute the copy in the background

1. 将此代码复制到记事本中
2. 更改源和目​​标工作簿的路径以适应
3. 将其另存为vbs文件，即“test.vbs”
4. 点击vbs文件在后台执行复制

Alternatively put this code inside a Sub in VBA and run it from the controlling workbook with both the source and destination files closed (suggest you Dimension the variables properly if you use VBA)

或者，将此代码放在 VBA 中的 Sub 中，并在源文件和目标文件都关闭的情况下从控制工作簿中运行它（如果您使用 VBA，建议您正确标注变量）

Dim objExcel
Dim Wb1
Dim Wb2
Dim ws
Set objExcel = CreateObject("excel.application")
On Error Resume Next
Set Wb1 = objExcel.Workbooks.Open("c:\temp\source.xlsm")
Set Wb2 = objExcel.Workbooks.Open("c:\temp\dest.xlsm")
Set ws = Wb1.Sheets("logistics")
If Not IsEmpty(ws) Then
    ws.Copy Wb2.Sheets(1)
    objExcel.DisplayAlerts = False
    Wb2.Save
    objExcel.DisplayAlerts = True
    wscript.echo "success"
Else
    wscript.echo "copy failed"
End If
Wb2.Close False
Wb1.Close False
On Error GoTo 0
objExcel.Quit
Set objExcel = Nothing

回答by Siddharth Rout

But I dont want the user to see the workbook opening and closing? Is there any way to copy from one workbook to another without the user seeing the workbook that is being copied to? Thanks – JC75

I need to set the workbook to visible = false, but there is no visible property for the workbook. And if i set the application window to visible = false,I get the same orginal error of 'Copy Method of Worksheet Class failed' – JC75

但我不希望用户看到工作簿的打开和关闭？有没有办法从一个工作簿复制到另一个工作簿，而用户不会看到正在复制到的工作簿？谢谢 – JC75

我需要将工作簿设置为可见 = false，但工作簿没有可见属性。如果我将应用程序窗口设置为可见 = false，我会收到相同的原始错误，即“工作表类的复制方法失败” – JC75

Another way to achieve what you want.

另一种方式来实现你想要的。

'~~> From within excel
Sub Sample()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet
    Dim sFileName As String

    sFileName = "C:\Temp.xls"

    Set wb1 = ActiveWorkbook
    Set ws1 = wb1.Sheets("Logistics")

    Set wb2 = Workbooks.Open(sFileName)
    ActiveWindow.Visible = False

    wb2.Sheets.Add Before:=wb2.Sheets(1)
    ws1.Cells.Copy wb2.Sheets(1).Cells

    'Windows(wb2.Name).Visible = True
    wb2.Close SaveChanges:=True

    Set wb1 = Nothing
    Set wb2 = Nothing
End Sub

回答by kayle

Try this

尝试这个

Sub CopyAcross()
  Workbooks("Model24.xls").Sheets("Custom").Copy Before:=Workbooks("Master.xls").Sheets(1)
End Sub