19 Replies Latest reply: Jun 18, 2013 5:58 AM by Saurabh Pandit RSS

    Export to Excel

      Hi All,

       

      I am exporting straight table data to excel.

       

      Based on the dates entered in input box, data is displayed in straight table.

       

      Is there any way to export the dates also?

        • Export to Excel
          Rahul Gupta

          Hi,

           

          Add that Date Field as the First Column of the Straight Table.

           

          Regards

          • Re: Export to Excel

            Hi,

             

            As Rahul suggested, if you don't have date field in your straight table then add the date field to you table and export.

             

            Your description on problem is not so clear, above solution is purely on guess.

             

            It would be nice if you could give us more details on you requirement.

             

            - Sridhar

              • Re: Export to Excel

                Hi ..

                 

                Thanks for the replies..

                 

                I have an input box for entering start date and end date and displaying records in staraight table

                between the date ranges(inputted dates).

                 

                vStartDate and vEndDate are the input box variables.

                 

                I am using below expression for displaying the records.

                only({<[bkd-dt]={">=$(vStartDate)<=$(vEndDate)"}>}[bkgno])

                 

                My requirement is,when i export the straghit table data to excel,

                I need to display the date ranges inputted.That is, vStartDate and vEndDate values.

                No need to display the date ranges in  table.

                 

                 

                Thanks..

                 

                  • Re: Export to Excel
                    Miguel Angel Baeyens de Arce

                    Hi,

                     

                    You can label your expressions (or at least one of them) with the date range, using this in the label part in the chart properties:

                     

                    ='Sales from ' & $(vStartDate) & ' to ' & $(vEndDate)
                    

                     

                    Make sure the $() returns the date in the required format, otherwise you can use Date(vStartDate) instead. Or if you are using a pivot table, set something like the above in the Total label in the Presentation tab in the chart properties.

                     

                    Is that what you are looking for?

                     

                    Miguel Angel Baeyens

                    BI Consultant

                    Comex Grupo Ibérica

                      • Re: Export to Excel

                        Hi Miguel,

                         

                        Thank you very much for the reply.

                         

                        It is a  helpful solution.But,I have 40 columns in my straight table.So if i could include date range info as header or footer display(After excel exporting)it would be more good.Could you please let me know if any possible way to incorporate this modification?

                         

                        I have checked the option Settings--->User Preferences---->Export--->On BIFF Exports,to include the selection criteria.Is there any way to include the input box value along with this?

                          • Re: Export to Excel
                            Miguel Angel Baeyens de Arce

                            Hi,

                             

                            There's not as far as I know. But you can create a macro to export your chart with the caption and save it to a file. Ir requires more code, but it's worth trying

                             

                            Sub ExcelExpwCaption
                                 'Set the path where the excel will be saved 
                                 filePath = "C:\Test.xls"
                            
                                 'Create the Excel spreadsheet 
                                 Set excelFile = CreateObject("Excel.Application")
                                 excelFile.Visible = true
                                 'Create the WorkBook
                                 Set curWorkBook = excelFile.WorkBooks.Add
                                 'Create the Sheet
                                 Set curSheet = curWorkBook.WorkSheets(1)
                            
                                 'Get the chart we want to export
                                 Set tableToExport = ActiveDocument.GetSheetObject("CH01")
                                 Set chartProperties = tableToExport.GetProperties
                                 tableToExport.CopyTableToClipboard true
                            
                                 'Get the caption
                                 chartCaption = tableToExport.GetCaption.Name.v
                                 'MsgBox chartCaption
                            
                                 'Set the first cell with the caption
                                 curSheet.Range("A1") = chartCaption
                                 'Paste the rest of the chart
                                 curSheet.Paste curSheet.Range("A2")
                                 excelFile.Visible = true
                            
                                 'Save the file and quit excel
                                 curWorkBook.SaveAs filePath
                                 curWorkBook.Close
                                 excelFile.Quit
                            
                                 'Cleanup
                                 Set curWorkBook = nothing
                                 Set excelFile = nothing
                            End Sub
                            

                             

                            Hope that helps.

                             

                            Miguel Angel Baeyens

                            BI Consultant

                            Comex Grupo Ibérica