Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Export to excel - is there a way of exporting multiple Chart/Table box into one Sheet?

Hi all,

Is there a way of exporting multiple Chart/Table box into one Sheet?

I have to end up exporting to excel multiple times and then copy and pasting info from different sheets to one excel sheet.

Thanks,

Mike

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Export to excel - is there a way of exporting multiple Chart/Table box into one Sheet?

Hi Mike

I've tried to create a macro to export several object into one sheet.

It is only possible to copy and paste the values and not the formats, but at least it's a start.

/Martin

9 Replies
Not applicable

Export to excel - is there a way of exporting multiple Chart/Table box into one Sheet?

Have you tried creating a Table Box with all the relevant variables from the linked tables included?

Not applicable

Export to excel - is there a way of exporting multiple Chart/Table box into one Sheet?

It's not possible for this right now as the data in each column represents different things for each row..

Thanks again,

Mike

Not applicable

Export to excel - is there a way of exporting multiple Chart/Table box into one Sheet?

Hi Mike

I've tried to create a macro to export several object into one sheet.

It is only possible to copy and paste the values and not the formats, but at least it's a start.

/Martin

Not applicable

Export to excel - is there a way of exporting multiple Chart/Table box into one Sheet?

Thanks Martin! That is a great example!

I can see that the VBScript does not take into VBA functions automatically and I need to change the formats of the cells to text to enable crossover of numbers with leading zeros. Does anyone have an example of this? I have looked around and tried the following script:

XLSheet.Columns(

"A:Z").NumberFormat = "@"

and

Columns("A:I").Select
Selection.NumberFormat = "@"

The second is from Excel but uses VBA and does not work.. Sorry, but not used VBscript before.

Thanks, Mike





Not applicable

Export to excel - is there a way of exporting multiple Chart/Table box into one Sheet?

Hi Michael

It was not really as easy as i thought it would be, to keep the leading zeros. I did find a workaround on this.

I believe you are and the right path, when trying to change the format of the cells, to a regular textformat in Excel. This is just not enough, as you also need to use the pastespecial method. As another thing, it would not paste values into Excel unless I had an open workbook. I created a workaround for this, by opening an instance of excel and at the end of the script, close the workbook again.

Have a look at the attached example.

/Martin

Not applicable

Export to excel - is there a way of exporting multiple Chart/Table box into one Sheet?

Hi Martin,

Thanks again for that and, again, it works great!

Mike

Not applicable

Export to excel - is there a way of exporting multiple Chart/Table box into one Sheet?

I notice on the Copy_Paste_To_Excel_Values sample app that if you change the macro code to do a PasteSpecial on the first table but have the export go into sheet 2 instead of sheet 1 of the workbook, the PasteSpecial function fails. The failure message is "Select method of Range class failed".

It appears to have issues with using PasteSpecial on any sheet other than sheet1 . Is there a workaround for this?

Ken

Not applicable

Export to excel - is there a way of exporting multiple Chart/Table box into one Sheet?

Hi Ken

You need to select the sheet in before it is possible to insert anything on that sheet - just like a macro within Excel.

If i remember it all right, you have a line in the script - something like



set XLSheet = XLDoc.Worksheets(2)

After you have done this "set", you need to select/activate the sheet - it can be done easily with:

XLSheet. Select



Hope that helps.

/Martin

Not applicable

Export to excel - is there a way of exporting multiple Chart/Table box into one Sheet?

Martin,

Thanks! I will make sure to do that. I will let you know if it works or not.

Ken