Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Have you tried creating a Table Box with all the relevant variables from the linked tables included?
It's not possible for this right now as the data in each column represents different things for each row..
Thanks again,
Mike
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
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
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
Hi Martin,
Thanks again for that and, again, it works great!
Mike
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
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
Martin,
Thanks! I will make sure to do that. I will let you know if it works or not.
Ken