Send Multiple Straight Tables to One XLS Sheet Looped through Field

[#1]

Hi All,

Sorry for the long Heading...

Ok here is my scenario, i will try keep it as simple as possible. I have one Sales report by Store and one Sales report by Product. What needs to happen is a Department needs to be selected, the 2 reports sent to excel (on 1 Sheet - side by side), the Sheet renamed to the Department Name. This process must happen for each Department until you have multiple sheets in a single document. I have no trouble with the Looping process or naming each sheet per Department.

My problem is getting the 2nd report on the sheet.

Here is my Macro:

Sub SalesReport

print_excel "CH1028"              'Change this to be the relevant chart objectID that you would like to send to Excel

End Sub

Sub print_excel(v_object)

Dim sheetname,i

Set XLApp = CreateObject("Excel.Application")

XLApp.Visible = FALSE

Set XLDoc = XLApp.Workbooks.Add

Set Ftg = Activedocument.Fields("Department") '''This is the field I am looping on.

Set UrvFtg = Ftg.GetPossibleValues

Set MyTable = ActiveDocument.GetSheetObject(v_object)

For i= 0 to UrvFtg.Count-1

sheetname = ""

sheetname = UrvFtg.Item(i).Text

Ftg.Select sheetname

If Len(sheetname)>29 Then '''Checking length of Sheet caption

sheetname=left(sheetname,27)

End If

sheetname= replace(sheetname,"'","") '''Only alphanumeric .

sheetname= replace(sheetname,"/","")

sheetname= replace(sheetname,":","")

NoOfRows = MyTable.GetRowCount

If NoOfRows > 1 Then

XLDoc.Sheets.Add.Name= "tmp"

XLSheet XLDoc,"tmp",v_object,sheetname

End If

Next

Ftg.Clear

XLApp.Visible = TRUE

End Sub

Function XLSheet(ExcelDoc,SheetName,ChartName,Category)

Set obj = ActiveDocument.GetSheetObject(ChartName)

obj.CopyTableToClipboard True

ExcelDoc.Sheets(SheetName).PasteSpecial '-4163

ExcelDoc.Sheets(SheetName).Columns("A:A").ColumnWidth = 100

ExcelDoc.Sheets(SheetName).cells.select

ExcelDoc.Sheets(SheetName).columns.select

ExcelDoc.Sheets(SheetName).cells.mergecells = false

ExcelDoc.Sheets(SheetName).Cells.EntireRow.AutoFit

ExcelDoc.Sheets(SheetName).Cells.EntireColumn.AutoFit

ExcelDoc.Sheets(SheetName).Cells(1, 5).Select

ExcelDoc.Sheets(SheetName).name = Category

End Function

Help would as always be hugely appreciated.

Thanks,

Richard

[#2]

What else do you need from me, should i build an example qvw?

[marcus_sommer · Accepted solution]

It looked that you copied the second chart over the first chart, right? You need then to transfer your for-loop counter i to the export-function which will then specify (per further if-loop) the target cell for your paste-statement. I think if you looked here you will find various examples how to adress this:

VB Macro to Loop Field (Dynamic) and Export Chart (Dynamic) to Excel & Name Sheets by Field Valu...

Export to Excel for each dimension value as separate sheet

QlikTip #32: Exporting multiple QV objects to a single Excel document

- Marcus

[#4]

Thanks Marcus,

The 3rd link you sent me was definitely a winner!, just added a loop by field on there and a field variable for the Sheet Naming and i was sorted.

Thanks