Excel跨工作簿复制表格

场景介绍

RPA流程里，经常需要处理Excel，把一个Excel的的表格复制到另一个Excel表格的场景很常见，然而UiBot中没有处理这种情况的命令。

方案设计

步骤

Excel提供宏功能，可以处理这种情况，下面概述宏命令步骤：

  1. 打开要复制的表格所在工作簿及要复制到的工作簿，下面简称源工作簿及目的工作簿

  2. 因语法限定只能根据工作簿名称操作对应工作簿，故需截取路径中的工作簿名称

  3. 根据工作簿名加表名，复制源工作簿的表到目的工作簿

  4. 保存及关闭源工作簿及目的工作簿

宏源码

基于复用的考虑，工作簿路径及表名设定为宏参数

参数：

  • fromWorkbook：源工作簿文件路径

  • toWorkbook：目的工作簿文件路径

  • fromSheet：要复制的表的名称

Sub CopySheet(fromWorkbook As String, toWorkbook As String, fromSheet As String)
    Workbooks.Open (fromWorkbook)
    Workbooks.Open (toWorkbook)
    idx = InStrRev(fromWorkbook, "\") + 1
    fromWorkbook = Mid(fromWorkbook, idx, Len(fromWorkbook) - idx + 1)
    idx = InStrRev(toWorkbook, "\") + 1
    toWorkbook = Mid(toWorkbook, idx, Len(toWorkbook) - idx + 1)
    Workbooks(fromWorkbook).Sheets(fromSheet).Copy Before:=Workbooks(toWorkbook).Sheets(1)
    Workbooks(fromWorkbook).Save
    Workbooks(fromWorkbook).Close
    Workbooks(toWorkbook).Save
    Workbooks(toWorkbook).Close
End Sub
最后更新于 29th Oct 2020