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
