26 Replies Latest reply: Nov 8, 2016 12:12 AM by Tamil Nagaraj RSS

    Saving Excel file created by macro with current date and formatting

    Karen Mujeyi

      Hello QlikView Community,

       

      I am new to working with macros and have a macro I found in this community that creates an Excel file with multiple tabs (Thanks QlikCommunity!).

       

      I would like to do three (3) additional things with this file and not sure how.

       

      1. My priority is: what can I add to this macro script to save the file (have multiple drives so will not save to the usual C-drive, not sure if that is an issue)?

      2. Priority 2: When saving the file, what do I need to add to have the current date added to the file name?

      3. Not priority, but very helpful: What can I add to format each sheet to have the filter option on each sheet?  I am not trying to actually filter anything, just want to format the sheet to have the filter on:

       

      I found this thread in the community, but do not know how to get this to work for what I have.

      How to save excel file with current date using macro

       

      Thank you for any help!

       

      This is my macro script thus far (again, used base script from this community, so not mine as I did not know how to do this):

       

      sub exportToExcel_Variant6

      '// Array for export definitions
      Dim aryExport(2,3)

      aryExport(0,0) = "objPended"
      aryExport(0,1) = "Pending"
      aryExport(0,2) = "A1"
      aryExport(0,3) = "data"

      aryExport(1,0) = "objApproved"
      aryExport(1,1) = "Approved"
      aryExport(1,2) = "A1"
      aryExport(1,3) = "data"

      Dim objExcelWorkbook 'as Excel.Workbook
      Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)


      '// Now either just leave Excel open or do some other stuff here
      '// like saving the excel, some formatting stuff, ...

      end sub

        • Re: Saving Excel file created by macro with current date and formatting
          Frank Hartmann

          For the first two things have a look at this code.

          Just adapt the path where you want to save the Excelfile (-->filepath)

           

          sub exportToExcel_Variant6
          Dim strFilePath
          Dim strFileName
          timestamp =  date()&"-"&time()
          strFilePath = ActiveDocument.GetProperties.FileName
          strFileName = (InStrRev(strFilePath,"\",len(strFilePath)))
          strFileName = Mid(strFilePath, InStrRev(strFilePath,"\") + 1)
          'msgbox(strFileName)
          '// Array for export definitions
          Dim aryExport(2,3)
          aryExport(0,0) = "objPended"
          aryExport(0,1) = "Pending"
          aryExport(0,2) = "A1"
          aryExport(0,3) = "data"
          aryExport(1,0) = "objApproved"
          aryExport(1,1) = "Approved"
          aryExport(1,2) = "A1"
          aryExport(1,3) = "data"
          Dim objExcelWorkbook 'as Excel.Workbook
          Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)
          filePath = "C:\Users\Admin\Desktop\"&strFileName&"-"&timestamp&".xlsx"
          objExcelWorkbook.SaveAs filePath
          end sub 
          

           

          hope this helps!

            • Re: Saving Excel file created by macro with current date and formatting
              Karen Mujeyi

              Thank you for the script.  As this is new to me, where in this script do I set the file's name?  Not really sure what is being done in lines 6 and 7 or 21 and 22, so did not want to edit.

               

              Thank you again.

                • Re: Saving Excel file created by macro with current date and formatting
                  Frank Hartmann

                  The setup of the macro was to use the QV-Documenttitle as the Filename.

                  But if you want to use a self created name then use this code to only append the timestamp to the title:

                   

                  sub exportToExcel_Variant6
                  timestamp =  date()&"-"&time()
                  '// Array for export definitions
                  Dim aryExport(2,3)
                  aryExport(0,0) = "objPended"
                  aryExport(0,1) = "Pending"
                  aryExport(0,2) = "A1"
                  aryExport(0,3) = "data"
                  aryExport(1,0) = "objApproved"
                  aryExport(1,1) = "Approved"
                  aryExport(1,2) = "A1"
                  aryExport(1,3) = "data"
                  Dim objExcelWorkbook 'as Excel.Workbook
                  Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)
                  filePath = "C:\Users\Admin\Desktop\TITEL HERE"&"-"&timestamp&".xlsx"
                  objExcelWorkbook.SaveAs filePath
                  end sub
                  
                  
                  

                   

                  Just adapt C:\Users\Admin\Desktop\TITEL HERE with your path & titel and the macro will add the timestamp to it and saves the document.

                   

                  hope this helps

                    • Re: Saving Excel file created by macro with current date and formatting
                      Karen Mujeyi

                      Oddly enough, it is not working.  The file gets created, but nothing is saved and keeps "Book1" as title.

                       

                      This is what I've used.  Can you possibly see what I've done wrong?

                       

                       

                      sub exportToExcel_Variant6

                        timestamp = date()&"-"&time() 
                       
                      '// Array for export definitions
                      Dim aryExport(2,3)

                      aryExport(0,0) = "objPended"
                      aryExport(0,1) = "Pending"
                      aryExport(0,2) = "A1"
                      aryExport(0,3) = "data"

                      aryExport(1,0) = "objApproved"
                      aryExport(1,1) = "Approved"
                      aryExport(1,2) = "A1"
                      aryExport(1,3) = "data"

                      Dim objExcelWorkbook 'as Excel.Workbook
                      Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

                      ' Save and quit 

                      filePath = "V:\Senior Products - Finance\SER Analysts\Clinical Analyst\Daily Leadership Reports\New MOIC Daily Census Report\cgx census\CGX Daily Census Report 2 "&"-"&timestamp&".xlsx"
                      objExcelWorkbook.SaveAs filePath

                      end sub

                        • Re: Saving Excel file created by macro with current date and formatting
                          Frank Hartmann

                          this is working for me:

                           

                          sub exportToExcel_Variant6
                          timestamp = date()
                          '// Array for export definitions
                          Dim aryExport(1,3)
                          aryExport(0,0) = "objPended"
                          aryExport(0,1) = "Pending"
                          aryExport(0,2) = "A1"
                          aryExport(0,3) = "data"
                          aryExport(1,0) = "objApproved"
                          aryExport(1,1) = "Approved"
                          aryExport(1,2) = "A1"
                          aryExport(1,3) = "data"
                          Dim objExcelWorkbook 'as Excel.Workbook
                          Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)
                          filePath = "V:\Senior Products - Finance\SER Analysts\Clinical Analyst\Daily Leadership Reports\New MOIC Daily Census Report\cgx census\CGX Daily Census Report 2 "&"-"&timestamp&".xlsx"
                          objExcelWorkbook.SaveAs filePath
                          end sub
                          

                           

                           

                          see attached file

                          (make sure that the functions like copyObjectsToExcelSheet are available in Module --> see module of attached app)

                           

                          hope this helps

                  • Re: Saving Excel file created by macro with current date and formatting
                    Frank Hartmann

                    Is there an error message showing in module?

                    • Re: Saving Excel file created by macro with current date and formatting
                      Tamil Nagaraj

                      Hi Karen,


                      I have attached a sample which fulfill all your three requirments. Kindly let me know If you come across any issues.

                        • Re: Saving Excel file created by macro with current date and formatting
                          Karen Mujeyi

                          Thank you for the suggestion, unfortunately, I am quite a novice at this and not sure how to implement this or incorporate it into my module.

                           

                          Do I add this to my current script in the module?  If so, where?

                            • Re: Saving Excel file created by macro with current date and formatting
                              Tamil Nagaraj

                              I have a couple of questions to ask you before explaining the procedure.

                               

                              ● Do you really want to hard-code the path into the macro? Or do you want an input box? (Which allows you to paste the file path in front end directly and the path can be changed later).

                               

                              ● If you wish we can include browse folder window which allows you to browse the folder whenever you press the export button. File will be saved in the selected path.

                               

                              ● What is the date format for timestamp? Do you want to see your file name like "CGS Daily Census Report 2- 03Nov2016"?


                              Once you answered these questions, i'll provide the code along with the procedure by tomorrow morning.

                              • Re: Saving Excel file created by macro with current date and formatting
                                Karen Mujeyi

                                Thank you.  I will take anything!

                                 

                                First, I would like it hard-coded into the macro as I have an automation process that runs every day and gets these files where they need to be without much interaction.

                                 

                                Currently, the date format is set to MM.DD.YYYY, but am willing to change it as long as I can get this to work.

                                  • Re: Saving Excel file created by macro with current date and formatting
                                    Tamil Nagaraj

                                    Fine. I have updated the code with the above mentioned path and chart id. Just copy and paste the below code in your application macro window and run it. I have also attaced a sample file which saves the excel in the above mentioned path.

                                     

                                    Sub Export
                                    
                                      Dim oXLDoc, i
                                      set oXL = CreateObject("Excel.Application") 
                                    
                                      oXL.DisplayAlerts = False
                                      oXL.visible = True 'change to False to turn screen updating off
                                    
                                      Set oXLDoc = oXL.Workbooks.Add
                                    
                                      FilePath =  "V:\Senior Products - Finance\SER Analysts\Clinical Analyst\Daily Leadership Reports\New MOIC Daily Census Report\cgx census\"    ' File path can be changed later.
                                      FileName = "CGX Daily Census Report 2 - " & ActiveDocument.Evaluate("date(Now(), 'MM.DD.YYYY')") 'Date format can be changed later
                                    
                                      '---------------------------------------
                                      SheetObj=Array("objPended","objApproved")   'Chart ID's
                                      '---------------------------------------
                                    
                                    for i=0 to UBound(SheetObj)
                                    
                                      oXL.Sheets.Add
                                      oXL.ActiveSheet.Move ,oXL.Sheets( oXL.Sheets.Count )
                                    
                                      Set oSH = oXL.ActiveSheet
                                      oSH.Range("A1").Select
                                      
                                      Set obj = ActiveDocument.GetSheetObject(SheetObj(i))
                                      obj.CopyTableToClipboard True
                                      oSH.Paste
                                      sCaption=obj.GetCaption.Name.v
                                    
                                      oSH.Rows("1:1").Select
                                      oXL.Selection.Font.Bold = True
                                      oXL.Selection.AutoFilter
                                    
                                      oSH.Cells.Select
                                      oXL.Selection.Columns.AutoFit
                                    
                                    
                                      oSH.Range("A1").Select    
                                      oSH.Name=left(sCaption,30)
                                      
                                      Set obj=Nothing
                                      Set oSH=Nothing
                                    
                                    Next
                                    '---------------------------------------
                                      Call DeleteBlankSheets(oXLDoc)
                                    '---------------------------------------
                                        oXLDoc.Sheets(1).Select
                                        oXLDoc.SaveAs FilePath & FileName & ".xlsx"
                                        oXL.DisplayAlerts = True
                                        oXLDoc.Close True
                                        oXL.Quit
                                    
                                      Set oXL    = Nothing
                                      Set oXLDoc = Nothing
                                    
                                      Msgbox "Export successfully completed."
                                    
                                    End Sub 
                                    
                                    
                                    '--------------------------------------------------------------------
                                    Private Sub DeleteBlankSheets(ByRef oXLDoc)
                                      
                                    For Each ws In oXLDoc.Worksheets
                                      If oXLDoc.Application.WorksheetFunction.CountA(ws.Cells) = 0 Then
                                         On Error Resume Next
                                         Call ws.Delete()
                                      End If
                                    Next
                                    
                                    End Sub
                                    '--------------------------------------------------------------------
                                    
                                    
                                    

                                     

                                    Let me know.

                              • Re: Saving Excel file created by macro with current date and formatting
                                Frank Hartmann

                                and if you delete the filePath row and use the below row like that:

                                 

                                objExcelWorkbook.SaveAs "V:\Senior Products - Finance\SER Analysts\Clinical Analyst\Daily Leadership Reports\New MOIC Daily Census Report\cgx census\CGX Daily Census Report 2 "&"-"&timestamp&".xlsx"

                                 

                                the same error appearing?

                                  • Re: Saving Excel file created by macro with current date and formatting
                                    Karen Mujeyi

                                    Tried it and same error message.  Could it have something to do with the fact that this is a server I connect to using a VPN?

                                     

                                    I also tried saving to C-drive using the following and get this error message:

                                     

                                    New FilePath:

                                     

                                    objExcelWorkbook.SaveAs "C:\Users\KSM9972\Desktop\CGX Daily Census Report 2 "&"-"&timestamp&".xlsx"

                                     

                                    New Error Message:

                                     

                                    Microsoft Excel cannot access the file 'C:\Users\KSM9972\Desktop\CGX Daily Census Report 2 -11\3\7C448000'. There are several possible reasons:

                                      • Re: Saving Excel file created by macro with current date and formatting
                                        Frank Hartmann

                                        as long as the V disk is mounted in your system that should not be the problem!

                                         

                                        is it possible that you recently updated your excel version from an old to a newer one?

                                        If yes then probably the SaveAs method wont work in this way und you will haveto use either this command:

                                        objExcelWorkbook.SaveAs ("V:\Senior Products - Finance\SER Analysts\Clinical Analyst\Daily Leadership Reports\New MOIC Daily Census Report\cgx census\CGX Daily Census Report 2 "&"-"&timestamp&".xlsx"), 56

                                        or this command:

                                        objExcelWorkbook.SaveAs ("V:\Senior Products - Finance\SER Analysts\Clinical Analyst\Daily Leadership Reports\New MOIC Daily Census Report\cgx census\CGX Daily Census Report 2 "&"-"&timestamp&".xlsx"), 43

                                        in order to save the document according the version you are using!

                                         

                                        for reference have a look here:

                                        petri.com/forums/forum/windows-scripting/general-scripting/28303-saveas-method-of-workbook-class-failed

                                         

                                        hope this helps