Kategorien
Makros and Automatisation Qlik QlikView Workarounds Tips n Tricks

QlikView Export ChartTo XLS

This script enables you to export a ChartObject to xls by looping over a field, defined within a variable:

 //Variables for the ExportObjectMakro
Let pstrFieldName = "Standortname";
Let pstrExpObject = "CH526";
Let pstrExpPath   = "C:\ttemp";

REM ** MAKRO TO EXPORT OBJECT TO EXCEL **
rem Developed by Frenzel GmbH
rem Author Philipp Frenzel
Sub ExecByFieldValues

    SET Doc = ActiveDocument
    SET Field = Doc.Fields(getVariable("pstrFieldName"))
    SET Values = Field.GetPossibleValues

    FOR i = 0 TO Values.Count-1        
        ExportObject Values.item(i).Text
    NEXT

End Sub

Function ExportObject (pstrFieldFilter)

    SET Doc = ActiveDocument
    SET Field = Doc.Fields(getVariable("pstrFieldName"))

    Dim vstrExportPath
    Dim vstrExportFileName
    Dim vstrCompleteFile

    'Lets create a temporary bookmark, to store the previous selection
    Doc.CreateUserBookmark "MakroStorage"

    'Select the Current fieldvalue
    Field.Select pstrFieldFilter

    SET obj = Doc.GetSheetObject(getVariable("pstrExpObject"))
    vstrExportPath = getVariable("pstrExpPath") & "\"
    'will check if the folder exists already or should be created if not so
    'MyCheckFlolder vstrExportPath

    vstrExportFileName = pstrFieldFilter & ".xls"
    vstrCompleteFile = vstrExportPath & vstrExportFileName
    'will Delete an old existing version
    deleteReport vstrCompleteFile
    obj.ExportEx  vstrCompleteFile , 5

    'Wait until object is created then release the objects
    Doc.GetApplication.Sleep 5000

    'reset the start selections
    Doc.RecallUserBookmark "MakroStorage"
    set obj = Nothing

    Doc.RemoveUserBookmark "MakroStorage"       

End Function

rem ** will read and return the content of a variable defined in script or document **
rem Developed by Frenzel GmbH
rem Author Philipp Frenzel

function getVariable(varName)
 Set v = ActiveDocument.Variables(varName)
 IF v Is Nothing THEN
     getVariable = ""
 ELSE
     getVariable = v.GetContent.String
 END IF
end function

rem ** will check if the folder exists, if not, will build it **
rem Developed by Frenzel GmbH
rem Author Philipp Frenzel
function MyCheckFolder (pdfPath)
    Dim objFSO
    Dim objFolder
    Dim Overwrite

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Overwrite = objFSO.FolderExists(pdfPath)

    Select Case Overwrite
    Case False: Set objFolder = objFSO.CreateFolder(pdfPath)
    Case Else
    End Select

    Set objFSO = Nothing: Set objFolder = Nothing
end function

rem ** Function to delete existing documents, before printing/export the new one **
rem Developed by Frenzel GmbH
rem Author Philipp Frenzel
function deleteReport(rFile)
    set oFile = createObject("Scripting.FileSystemObject")

    currentStatus = oFile.FileExists(rFile)

    if currentStatus = true then
        oFile.DeleteFile(rFile)
    end if
    set oFile = Nothing
end function
''END MAKRO TO EXPORT OBJECT TO EXCEL ------------------------------

Schreibe einen Kommentar