Export some table box to excel in same doc but different sheet

Fri, 29 Oct 2010 10:03:14 GMT

Hello,

I have a problem to export more then 3 table box in 3 sheet, i use thise macro:

sub CopyToXL

' Set Excel App

set XLApp = CreateObject("Excel.Application") ' Define Object

XLApp.Visible = True 'Visible set as true

set XLDoc = XLApp.Workbooks.Add 'Open new workbook

ActiveDocument.Fields("AC4 status").Select "Missing"

ActiveDocument.Fields("AC4 status").ToggleSelect "Invalid"

' TAB AC Level 4

'Table box List of Materials

set table = ActiveDocument.GetSheetObject("TB04")

' Copy table and paste into Excel

set XLSheet = XLDoc.Worksheets(1) 'Select sheet where data should be pasted

XLSheet.Name="AC4"

table.CopyTableToClipboard true 'Copy data to Clipboard

XLSheet.Paste XLSheet.Range("A1")

XLsheet.Cells.EntireColumn.AutoFit

XLsheet.Cells.EntireRow.AutoFit

' TAB APC Attributes

'Table box List of Materials

set table = ActiveDocument.GetSheetObject("CH20")

' Copy table and paste into Excel

set XLSheet = XLDoc.Worksheets(2) 'Select sheet where data should be pasted

XLSheet.Name="Material"

table.CopyTableToClipboard true 'Copy data to Clipboard

XLSheet.Paste XLSheet.Range("A1")

XLsheet.Cells.EntireColumn.AutoFit

XLsheet.Cells.EntireRow.AutoFit

'Table box Format Att

set table = ActiveDocument.GetSheetObject("CH23")

' Copy table and paste into Excel

set XLSheet = XLDoc.Worksheets(3) 'Select sheet where data should be pasted

XLSheet.Name="Format"

table.CopyTableToClipboard true 'Copy data to Clipboard

XLSheet.Paste XLSheet.Range("A1")

XLsheet.Cells.EntireColumn.AutoFit

XLsheet.Cells.EntireRow.AutoFit

'Table box Brand Att

set table = ActiveDocument.GetSheetObject("CH27")

' Copy table and paste into Excel

set XLSheet = XLDoc.Worksheets(5) 'Select sheet where data should be pasted

XLSheet.Name="Brand"

table.CopyTableToClipboard true 'Copy data to Clipboard

XLSheet.Paste XLSheet.Range("A1")

XLsheet.Cells.EntireColumn.AutoFit

XLsheet.Cells.EntireRow.AutoFit

ActiveDocument.ClearAll false

end sub

Anyone experienced anything similar?

Rgds

topic Export some table box to excel in same doc but different sheet in QlikView

Export some table box to excel in same doc but different sheet