将VBA代码从一个工作簿中的工作表复制到另一个工作簿？ [英] Copy VBA code from a Sheet in one workbook to another?

问题描述

我一直在使用以下行将VBA模块从一个工作簿复制到另一个工作簿，我不知道是否有一个更简单的方法，但是它们已经正常工作：

I've been using the lines below to compy VBA modules from one workbook to another and I don't know if there is an easier way, but they have been working fine:

Set srcVba = srcWbk.VBProject
Set srcModule = srcVba.VBComponents(moduleName)

srcModule.Export (path) 'Export from source
trgtVba.VBComponents.Remove VBComponent:=trgtVba.VBComponents.Item(moduleName) 'Remove from target
trgtVba.VBComponents.Import (path) 'Import to target

但是现在我需要复制一个Sheet中的VBA代码，而不是在模块中。上述方法对于这种情况不起作用。

However now I need to copy VBA code that is in a Sheet, not in a Module. The above method doesn't work for that scenario.

我可以使用什么代码将工作表中的VBA代码从一个工作簿复制到另一个工作簿？

What code can I use to copy VBA code in a sheet from one workbook to another?

推荐答案

您不能删除并重新导入 VBComponent ，因为这将逻辑删除整个工作表。相反，您必须使用 CodeModule 来操纵组件中的文本：

You can't remove and re-import the VBComponent, since that would logically delete the whole worksheet. Instead you have to use CodeModule to manipulate the text within the component:

Dim src As CodeModule, dest As CodeModule

Set src = ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
Set dest = Workbooks("Book3").VBProject.VBComponents("ThisWorkbook") _
    .CodeModule

dest.DeleteLines 1, dest.CountOfLines
dest.AddFromString src.Lines(1, src.CountOfLines)

这篇关于将VBA代码从一个工作簿中的工作表复制到另一个工作簿？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！