Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

rtempleton
New Contributor II

Export to Excel and Save

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 

Tags (4)
1 Reply
Not applicable

Re: Export to Excel and Save

Does this macro work in WebView?

I was using macros that work perfectly in Desktop View, but do not work in Web View.

If you know a way to do it, please tell me.

Thanks

Community Browser