Export objects to excel using macro.

[#1]

Hi ,

I have few text boxes, button and multibox which are taking care of navigation of large amount of pivot tables. I have successfully exported related pivot tables and parameter multibox to excel using their object Ids in macro code written below:-

Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = TRUE
Set XLDoc = XLApp.Workbooks.Add
j = ""
j = "Sheet1"

ActiveDocument.GetSheetObject("MB40").CopyTableToClipboard true
XLDoc.Sheets(j).Range("A" & 1).Select
XLDoc.Sheets(j).Paste

ActiveDocument.GetSheetObject("CH452").CopyTableToClipboard true
XLDoc.Sheets(j).Range("D" & 17).Select
XLDoc.Sheets(j).Paste

Now I need to export report title to excel which is either in text box or button. When I try to export say TX31 or BU 31 in excel using same above code, it is exporting some other random object but not TX31 or BU 31. Can't we not export text box or button using this code or I am missing something in code. Please confirm.

Also, parameter multibox is exported properly to excel and working fine until we select multiple values in single parameter. In this case it does not show those multiple selected values in excel. Please confirm how to achieve this.

Thanks,

Kirti

[#2]

Hey,

Thanks for tips!!

Can you tell me if we can update the field names in current selection box at UI level or only option of renaming fields is through script?

Regards,

Kirti

[RSvebeck]

Hi!

If you mean renaming fields to be shown in the "current selection box", you must do this in the script part.

Write like this at the very end of your script:

RENAME FIELD OldFieldName to NewFieldName;

You may repeat that line for all fields you want to rename.

You can have space in the field name, but use bracets like this:

RENAME FIELD MonthID to [Month Number];

Best Regards

Robert

[#4]

Hey,

I tried this renaming thing, script was good with this syntax.

But after reloading I could not find my new field name in fields. Can you please tell me where can I find these new names and how to use them in my multibox?

Thanks,

Kirti

[RSvebeck]

Hi

Should work, remember that field names are case sensitive. You may post you document here and I can have a look at it if you like.

Best Regards, Robert

[#6]

Hi Robert,

Is there a way to replace some text in an exported excel worksheet via a macro ?

Sheet.Columns("C:C").Replace What="qmem", Replacement="",LookAt=1

The above code always throws an error that "Microsoft excel cannot find any data to replace. Check if your search formatting criteria are defined correctly. If you are sure that matching data exists in the workbook, it may be on a protected sheet. Excel cannot replace data on a protected worksheet."

[RSvebeck]

Hi!

This should do it:

sub ReplaceInExcel()

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = true          

Set xlDoc = xlApp.Workbooks.open("c:\replace.xlsx")

Set objWorkSheet = xlDoc.Worksheets(1)        

objWorkSheet.Cells(1,1).Value = replace(objWorkSheet.Cells(1,1).Value,"AAA","BBB")  'Replace AAA with BBB in the cell A1     

xlDoc.Save     

xlApp.Quit

End sub