How to export to Excel include Title Caption?

[senpradip007]

Hi,

I am looking for a macro to export the data currently select in a straight table(not the column headers, just data) and send to a specified excel file on the next available row.  I do not wish to create a new excel file with this macro and I do not want to override what is already in the excel file.  I want to just add on to it.

Could someone help me out?

Thanks in advanced.

PS

[m_woolf]

sub AppendDataToExcel  

  Const xlCellTypeLastCell = 11

  Set XLApp = CreateObject("Excel.Application")

  XLApp.Visible = True

  Set XLWorkbook = "C:\Temp\WorkbookName.xlsx")   ' Workbook should have the Header Row on Sheet1

  Set XLSheet = XLWorkbook.sheets("Sheet1")

  XLSheet.select

  Set objRange = XLSheet.UsedRange

  objRange.SpecialCells(xlCellTypeLastCell).Activate

  intLastRow = XLApp.ActiveCell.Row + 1

  XLSheet.Range("A" & intLastRow).Select

  ActiveDocument.GetSheetObject("CH178").CopyTableToClipboard false

  XLSheet.Paste ' -4163    'values

  XLWorkbook.Save

  XLWorkbook.Close

  XLApp.Quit

  set v = nothing

  set XLSheet = nothing

  set XLWorkbook = nothing

  set XLApp = nothing

end sub

[senpradip007]

Hi,

Thanks for the prompt reply. But its not working.

Regards.

PS

[avinashelite]

hi

[avinashelite]

Hi ,

You have 3 way s to do it .

1.Go to settings >User Preference >Export > then check on the export with caption in chart option.

2. Macro

Sub ExcelExpwCaption

     'Set the path where the excel will be saved

     filePath = "E:\TestBB.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("LB02")

     Set chartProperties = tableToExport.GetProperties

     tableToExport.CopyTableToClipboard true

     'Get the caption

     chartCaption = tableToExport.GetCaption.Name.v

     'MsgBox chartCaption

     'Set the first cell with the caption

     Sheet.Range("A1") = chartCaption

     'Paste the rest of the chart

     Sheet.Paste Sheet.Range("A2")

     excelFile.Visible = true

     'Save the file and quit excel

     WorkBook.SaveAs filePath

     WorkBook.Close

     excelFile.Quit

     'Cleanup

     Set WorkBook = nothing

     Set excelFile = nothing

End Sub

3. NPrinting tool

[ecolomer · Accepted solution]

See this example:

[senpradip007]

Thanks for your prompt reply. Tried with your app, but when I click on "Export Excel" prompting the VBA (macro) code instead of excel.

Please help.

[ecolomer]

Hi,

You need define directory in the macro, in the tirdh line

And you put the name of the table in the macro in line nº 16

[ecolomer]

And you need to select option in the macro "Access to System

[senpradip007]

Thanks for your prompt reply. Tried the first solution but data has been exported without the caption.

Tried with the second solution as well, but when I click on "Export Excel" prompting the VBA (macro) code instead of excel.