9 Replies Latest reply: Oct 29, 2015 4:40 AM by Marcus Sommer RSS

    export file in dynamic date

      Hi Guys.

      I want a macro by which i can export the data from my qlikview application with custom name and dynamic date.

      For example i have straight table and start date and end date selection criteria in my dashboard.when i export it should export like customerdata_startdate_enddate.xls.

      Can anyone help me in this?

          • Re: export file in dynamic date

            Thanks marcus for your help. I am struggling with one small error. I am trying below macro .

             

             

            Sub ExcelExpwCaption

             

             

             

                 '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

             

             

             

                 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

             

             

            it export to excel but not saving the file in location. Could you please help me in this

              • Re: export file in dynamic date
                Marcus Sommer

                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.

                 

                - Marcus

                  • Re: export file in dynamic date

                    Hi marcus,

                     

                    thanx for your help, it works now, but i have a question, it takes much time to run, first it is exporting and then naming in that new name and saving in the path. Is it possible to rename the file while exporting itself?

                      • Re: export file in dynamic date
                        Marcus Sommer

                        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:

                         

                        dim start

                        start = start = now()

                         

                        ....

                         

                        msgbox "step x: " &  FormatDateTime(now() - start, 0)

                         

                        - Marcus