导出到excel中具有不同名称的两个工作表

debugcn 发表于 Dev

用户名

我有一个包含两个表的容器：CH10001和CH10002

使用以下代码，我可以导出CH10001

sub xport2xl()
    iRow = 1
    set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    set xlWB = xlApp.Workbooks.Add
    set xlSheet = xlWB.Worksheets(1)
    set obj = ActiveDocument.getsheetobject(ChartName)
    xlSheet.Activate
    xlSheet.Cells.Clear
    while not (isempty(xlSheet.Cells(iRow,1)))
        iRow = iRow+2
    wend
    set txt1 = ActiveDocument.GetSheetObject("CH10001")
    txt1.CopytableToClipboard TRUE
    xlSheet.Cells(iRow,1).Select
    xlSheet.Paste
end sub

如何在同一工作簿中使用动态工作表名称导出CH10001和CH10002？并在工作表的名称中添加getdate例如？

R3uK

如果要将这些表导出为单独的工作表，这应该对您有帮助：

Sub xport2xl()
iRow = 1

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Add
Set xlSheet = xlWB.Worksheets(1)

Set obj = ActiveDocument.GetSheetObject(ChartName)
xlSheet.Activate
xlSheet.Cells.Clear

While Not (IsEmpty(xlSheet.Cells(iRow, 1)))
    iRow = iRow + 2
Wend

Set txt1 = ActiveDocument.GetSheetObject("CH10001")
txt1.CopytableToClipboard True
'xlSheet.Activate           '---You might need to activate sheet
xlSheet.Cells(iRow, 1).Paste
'-----Set the name of the sheet here----
xlSheet.Name = "Your name here for CH10001"


'----------------------------------------------------
'------------ Code for the second table -------------
'----------------------------------------------------
On Error Resume Next
'---Try to set the second sheet
Set xlSheet = xlWB.Worksheets(2)
If Err.Number <> 0 Then
    '---If there is an error, add a new sheet
    Set xlSheet = xlWB.Worksheets.Add
Else
    '---Already assigned, nothing else to do
End If
On Error GoTo 0

Set txt1 = ActiveDocument.GetSheetObject("CH10002")
txt1.CopytableToClipboard True
'xlSheet.Activate           '---You might need to activate sheet
xlSheet.Cells(iRow, 1).Paste
'-----Set the name of the sheet here----
xlSheet.Name = "Your name here for CH10002"

End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。