Thanks marcus for your help. I am struggling with one small error. I am trying below macro .
'Set the path where the excel will be saved
SET v = ActiveDocument.Variables("vStartDate")
varDate = v.GetContent.STRING
SET v1 = ActiveDocument.Variables("vEndDate")
varDate1 = v1.GetContent.STRING
filePath = "H:\ExcelReport_"&varDate&"_"&varDate1&".xls"
'Create the Excel spreadsheet
Set excelFile = CreateObject("Excel.Application")
excelFile.Visible = true
'Create the WorkBook
Set WorkBook = excelFile.WorkBooks.Add
'Create the Sheet
Set Sheet = WorkBook.WorkSheets(1)
'Get the chart we want to export
Set tableToExport = ActiveDocument.GetSheetObject("TB01")
Set chartProperties = tableToExport.GetProperties
'Get the caption
chartCaption = tableToExport.GetCaption.Name.v
'Set the first cell with the caption
Sheet.Range("A1") = chartCaption
'Paste the rest of the chart
excelFile.Visible = true
' 'Save the file and quit excel
' WorkBook.SaveAs filePath
Set WorkBook = nothing
Set excelFile = nothing
it export to excel but not saving the file in location. Could you please help me in this
Make sure that you have the proper access rights to this target-folder and put your filePath into a message-box to see if your variables for v1 return the expected value and you get a valid path - there could be also a path-syntax issue if your dates are stored like MM/DD/YYYY then windows doesn't acccept slash/backslash and some other chars within the path.
A changed order of naming the file wouldn't make a difference and the saving needs to be the last step before closing and clean everything.
One thing which could be take some time is to get the chart-data and copy them into the clipboard if it's really a large table and could only speed up this if you make this table smaller and/or by replacing text dimensions/expressions with a numeric content (ID's instead of descriptions).
Another point could be that your drive H: is a very slow (connected) network-storage - in this case you could change it to a local storage and make afterwards a manually or batch-triggered copy/move action.
To find out which part of your routine takes how many time you could implement a timekeeping, like:
start = start = now()
msgbox "step x: " & FormatDateTime(now() - start, 0)
i tried in IE and it shows
Macro parse failed. Funionality was lost
Error: ActiveX component cant create object: 'Excel.Application'
I have changed document setting security macro override also, but everytime though i save my macro with allow system access mode it is changing to safe mode and giving me this error.
is there anything else i need to enable?