1 Reply Latest reply: May 19, 2016 6:54 PM by Melba Guadalupe Morales RSS

    Export to Excel and Save

    Robert Templeton

      Just wanted to share a macro function I built for sending objects to excel and then saving them. A lot of the code comes from other similar functions, but I don't know who to give credit to.

       

       

      Sub Export_To_Excel
      ActiveDocument.ClearAll true 'This will set a clear all for qlikview set to false if not needed
      Dim qvDocName(1) 'Tick this up based on how many qvDoc's you need to bring in

      qvDocName(0) = "QlikviewObjectIDHere"
      qvDocName(1) = "QlikviewObjectIDHere"
      'qvDocName(2) = "NewNameHere"

      Call ExportExcel(qvDocName)

      End Sub


      Private Function ExportExcel(aryExportDefinition) 'as Excel.Workbook
      Dim i, objExcelApp, objExcelDoc, qvObjectId, path, strFilePath, exportPath

      'This snipit of code gets the relative path for the file
      Set objFSO = CreateObject("Scripting.FileSystemObject")
      strFilePath = objFSO.GetAbsolutePathName(".") 'This is the Qlikview path location
      exportPath = "\DataGeneration\Export\" 'This is the path after the relative path
      path = strFilePath & exportPath


      for i = 0 to UBOUND(aryExportDefinition)
      qvObjectId = aryExportDefinition(i)

      set obj = ActiveDocument.getSheetObject(qvObjectId)
      obj.sendToExcel

      set objExcel = GetObject(, "Excel.Application")
      objExcel.Visible = True 
      objExcel.DisplayAlerts = False 'Get rid of Alerts
      objExcel.EnableEvents = False 'Get rid of Events

      objExcel.WorkSheets("Sheet1").Name = qvObjectId 
      WorkbookName = objExcel.ActiveWorkbook.Name

      objExcel.ActiveWorkbook.SaveAs path & WorkbookName 

      ActiveDocument.GetApplication.WaitForIdle

      ActiveDocument.GetApplication.Sleep 1000 'Wait 1 second

      objExcel.ActiveWorkbook.close  'Close the current saved workbook

       

      next 
      end function

       

       

      If the absolute path is causing problems replace this

                 Set objFSO = CreateObject("Scripting.FileSystemObject")

                  strFilePath = objFSO.GetAbsolutePathName(".") 'This is the Qlikview path location


      With this and set a variable in your application with the commented code below

       

                'Set variable in Qlikview application as "vAppPath = Mid(DocumentPath(), 1, FindOneOf(DocumentPath(), '\', -1))
                  strFilePath = ActiveDocument.Variables("vAppPath").getcontent.string