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

                              • Re: Export to Excel

                                Hi Miguel,

                                 

                                I am getting error like

                                 

                                ActiveX component can't create object: 'Excel.Application' .

                                 

                                There is no Microsoft Excel installed in my machine.Only Open Office is there. Is it because of that?

                                Thanks,

                                Soumya

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                  • Re: Export to Excel
                                    Miguel Angel Baeyens de Arce

                                    Yes, you need to have Excel in order to make the macro work. I don't know if there are any ActiveX controls to create OpenOffice Calc spreadsheets, and if they do exist, what is their API. I'd try to install Excel if that is the format you want for your documents.

                                     

                                    You may change the macro so it saves on a text file comma separated that will be easily imported into Calc, where the first line of that file has the caption and from the second onwards, all your data. However, quotes and other special characters may cause issues when importing.

                                     

                                    Regards,

                                     

                                    Miguel Angel Baeyens

                                    BI Consultant

                                    Comex Grupo Ibérica

                                     

                                    EDIT: Actually, saving to CSV instead of XLS should be as easy as changing this line in the macro above

                                     

                                    filePath = "C:\Test.xls"
                                    

                                     

                                    to

                                     

                                    filePath = "C:\Test.csv"
                                    

                                     

                                    Although you need to have Excel installed in order to create the workbook.

                                  • Re: Export to Excel

                                    Hi Miguel,

                                    Thank you very much...The problem has been solved..

                                     

                                    Could you please let me know one more thing? Is there any way to split up the caption? Now My captiion looks like

                                     

                                    "Daily Summary Report     From  1/1/2007  To  8/5/2011     Product Type : HTL"

                                     

                                    If I could include "Product Type : HTL" in the second cell,it would be better..Please suggest

                                     

                                    Thanks,

                                    Soumya

                                    • Re: Export to Excel
                                      Saurabh Pandit

                                      Hi Miguel,

                                       

                                      I used your above code and it works fine.

                                       

                                      Now what I want to do is to export some charts to excel one below the other:

                                       

                                      I tried following

                                      dim a,text,tm

                                      a=Array("CH02","CH04","CH06","CH07")


                                      for i = 0 to 4

                                      b=a(i)

                                      i=i+1

                                       

                                      But I am not able to add it accurately in the code.

                                       

                                      Moreover I want the charts below the previous charts.

                                      Where to add

                                      Range("A" & Rows.Count).End(xlUp).Offset(1).Select

                                      curSheet.Paste

                                       

                                      Or


                                      usedRows=curSheet.UsedRange.Rows.Count

                                       

                                      Sorry all confused. I am trying at my end.

                                       

                                      Thanks for helping out.