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

[#1]

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

[#2]

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

[#3]

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

Thanks again,

Mike

[#4 · Accepted solution]

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

[#5]

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

[#6]

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

[#7]

Hi Martin,

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

Mike

[#8]

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

[#9]

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

[#10]

Martin,

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

Ken