Categories
Common Macros and Automatisation QlikView /en Tips n Tricks /en

Export a Chart or Tablebox via VBScript

As sometimes it makes sense to export data with a loop using VBScript. This little toolset should do the job for you:

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

Be aware that you need some helpers:

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

and finally you can export the object like this:

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

Take care that you’ve added the variables for the path in you enviroment variables or in your load script:

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

 

Leave a Reply