export to excel two sheets with different names

Question

I have a container that contains two tables : CH10001 and CH10002

with the following code I can export 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

How can I export CH10001 and CH10002 in the same workbook but with dynamic sheet name? And add the getdate in the name of the sheet for example?

Answer 1

If you want to export these tables in separeted sheets, this should help you :

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