Macro to Export to excel , conditionally hidden objects.

[ashishpalkar]

Hi,

I am looking for a macro to export the data currently select in a chart table(including column headers). 

I have following Chart Tables with conditional show.

When Button1 is clicked  -  CH114 - 5 columns

When Button2 is clicked C1Pg - 10 columns

whenever user clicks on Button 1 CH114 is shown, and that table should export to excel.

whenever user clicks on Button 2 C1Pg is shown, and that table should export to excel.

I do not wish to create a new excel file , whenever any button is selected that chart table should export to excel. I want to override what is already in the excel file. 

Could someone help me out? 

My knowledge of VB macros is basic

Thanks in advance,

[trdandamudi]

May be as attached as below:

I created a sample for you and this should give a good idea to complete the rest. when you click on the "Export To Excel" button it does overwrite the excel file. So please modify the code accordingly as per your needs. Hope this helps,,,,

[tamilarasu]

Ashish,

Do you want to export the charts in two separate excel files.? Do you want to update both the excel files whenever the user clicks the button.? Please elaborate!

[ashishpalkar]

Hi Tamil,

I want to use same excel file for both chart tables.

[tamilarasu]

Please confirm the below points.

* Chart 1 and Chart 2 will be exported to Sheet 1 and Sheet 2 in the excel file.

* Whenever the user clicks the button, the existing excel file in the folder will be replaced with the updated one.

* If you delete the excel file, new one will be created whenever the button clicked in defined path.

[ashishpalkar]

Hi Tamil

Yes Correct ,

* Chart 1 and Chart 2 will be exported to Sheet 1 and Sheet 2 in the excel file.

* Whenever the user clicks the button, the existing excel file in the folder will be replaced with the updated one.

* If you delete the excel file, new one will be created whenever the button clicked in defined path.

[tamilarasu]

I will look into it. Give me sometime.

[tamilarasu]

Hi Ashish,

Sorry for the late reply (I had some work). First of all, I would not recommend this method. Because it makes the chart switch slow. It would be better, If you can keep a separate button to export both the charts. I have created a sample as per your initial requirement.

Sub Export
Dim XLApp  

FileName = "Test.xlsx" 
FilePath = ActiveDocument.GetVariable("vPath").GetContent.String
ResetShow = ActiveDocument.GetVariable("vShow").GetContent.String
Show = ActiveDocument.GetVariable("vShow").GetContent.String

If Show = 2 then
ActiveDocument.Variables("vShow").SetContent "1", true  
End If

If Right(FilePath,1)<> "\" then
FilePath = FilePath & "\"
End If

File = FilePath & FileName

Set XLApp = CreateObject("Excel.Application")  
XLApp.Visible = False
Set XLDoc = XLApp.Workbooks.Add

'Chart 1 
ActiveDocument.GetSheetObject("CH01").CopyTableToClipboard True
XLDoc.Sheets(1).Paste

ActiveDocument.Variables("vShow").SetContent "2", true 

'Chart 2
ActiveDocument.GetSheetObject("CH02").CopyTableToClipboard True 
XLDoc.Sheets(2).Paste

XLDoc.Sheets(1).Name = "Chart1"  
XLDoc.Sheets(2).Name = "Chart2"

XLApp.DisplayAlerts = False
XLDoc.Sheets(3).Delete
XLDoc.SaveAs File 
XLApp.DisplayAlerts = True
XLApp.Application.quit  

ActiveDocument.Variables("vShow").SetContent ResetShow, true

set XLApp = Nothing  
set XLDoc = Nothing  
set XLSheet = Nothing  
'Msgbox "Exported Sucessfully"  
End Sub

Note: It seems that the chart can not be exported, If it is hidden. So, I have activated the chart and reset again as per the user selection in macro itself. You can modify the variables according to your original file.