Export to Excel and Save

Anonymous — Fri, 29 Apr 2016 19:59:22 GMT

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

Re: Export to Excel and Save

Thu, 19 May 2016 22:54:38 GMT

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

topic Re: Export to Excel and Save in QlikView

Export to Excel and Save

Re: Export to Excel and Save