Copy this macro code into the module area of your toolkit document and create a button to execute it. When the macro is executed it will ask for the pathname to the source document containing the cycle group to be copied. Then it will ask for the cycle group name (remember that upper and lower case is important), and then the pathname to the document that the cycle group will be copied into. If either document requires a username or password QlikView will ask for those as the macro opens the documents. As always, make copies of your documents before testing a macro like this.
Here's the macro code you can copy and paste:
Sub Copy_Cycle_Group_Doc_to_Doc
'------------------------------
' /* Copy Cycle Group From One QV Document to Another */
' /* QlikView Maven, March 2013 */
Dim objQV, objSource, objDest, objSourceVar, objDestVar
Dim objVars, varcontent, objTempVar, varname, i, varctr
'initialize
fn_source=inputbox("Enter pathname to source document containing the Cycle Group you want to copy:","Enter Source Document Pathname")
if trim(fn_source)="" then 'no entry or cancel
exit sub
end if
CGName=inputbox("Enter name of Cycle Group to copy:","Enter Cycle Group Name")
if trim(CGName)="" then 'no entry or cancel
exit sub
end if
fn_dest=inputbox("Enter pathname to destination document where the Cycle Group is to be inserted:","Enter Destination Document Pathname")
if trim(fn_dest)="" then 'no entry or cancel
exit sub
end if
Set objSource=Application.OpenDoc(fn_source)
Set objDest=Application.OpenDoc(fn_dest)
Set SrcGrp = objSource.GetGroup(CGName)
Set SrcProps = SrcGrp.GetProperties
Set NewGrp = objDest.GetGroup(CGName)
If NewGrp Is Nothing Then 'check if cycle group exists in dest document
Set NewGrp = objDest.CreateGroup(CGName) 'doesn't exist so create it
Else
Set NewProps = NewGrp.GetProperties 'does exist so remove old definition
for i = 1 to NewProps.FieldDefs.Count
NewGrp.RemoveField 0
Next
End If
Set NewProps = NewGrp.GetProperties
'copy elements of cycle group from source to dest
NewProps.FieldDefs.CopyFrom SrcProps.FieldDefs
NewProps.IsCyclic = SrcProps.IsCyclic
NewProps.Labels = SrcProps.Labels
NewProps.Present = SrcProps.Present
NewProps.SortCriterias.CopyFrom SrcProps.SortCriterias
NewGrp.SetProperties NewProps
'we're done, show a message and close down
x=msgbox(CGName&" copied from "&fn_source&" to "&fn_dest&"."&chr(13)&"OK to Save?",vbOKCancel,"Copy_Cycle_Group_Doc_to_Doc")
if x=vbOK then
ObjDest.Save
End if
objDest.CloseDoc 'comment out this line if you'd like dest document to stay open for examination
objSource.CloseDoc
set objSource=nothing
set objDest=nothing
set SrcGrp=nothing
set SrcProps=nothing
set NewGrp=nothing
set NewProps=nothing
End Sub 'end of Copy_Cycle_Group_Doc_to_Doc
★★★
2 comments:
Hi TB,
Thanks for your Valuable post.
This post save my lot works .
For anyone interested, here is the code for the macro with browse-for-file functionality. This only works if you have MS Office installed or MS Excel for Windows. You will also need to set Module Security and Local Security to Allow System Access (on the left side of your Edit Module window).
Sub Copy_Cycle_Group_Doc_to_Doc
'------------------------------
' /* Copy Cycle Group From One QV Document to Another */
' /* Requested Module Security and Current Local Security must be set to Allow System Access */
' /* QlikView Maven, March 2013 */
Dim objQV, objSource, objDest, objSourceVar, objDestVar
Dim objVars, varcontent, objTempVar, varname, i, varctr
'initialize
Set objXL=CreateObject("Excel.Application")
fn_source=objXL.GetOpenFilename("All Files (*.qvw),*.qvw",,"Select file containing the Cycle Group you want to copy",False)
If fn_source="False" then
'user cancelled out of dialog box
Set oXL=nothing
Exit sub
End If
'fn_source=inputbox("Enter pathname to source document containing the Cycle Group you want to copy:","Enter Source Document Pathname")
'if trim(fn_source)="" then 'no entry or cancel
' exit sub
' end if
CGName=trim(inputbox("Enter name of Cycle Group to copy:","Enter Cycle Group Name"))
if CGName="" then 'no entry or cancel
exit sub
end if
fn_dest=objXL.GetOpenFilename("All Files (*.qvw),*.qvw",,"Select document where the Cycle Group is to be inserted",False)
If fn_dest="False" then
'user cancelled out of dialog box
Set oXL=nothing
Exit sub
End If
'fn_dest=inputbox("Enter pathname to destination document where the Cycle Group is to be inserted:","Enter Destination Document Pathname")
'if trim(fn_dest)="" then 'no entry or cancel
' exit sub
' end if
Set objSource=Application.OpenDoc(fn_source)
Set objDest=Application.OpenDoc(fn_dest)
Set SrcGrp = objSource.GetGroup(CGName)
Set SrcProps = SrcGrp.GetProperties
Set NewGrp = objDest.GetGroup(CGName)
If NewGrp Is Nothing Then 'check if cycle group exists in dest document
Set NewGrp = objDest.CreateGroup(CGName) 'doesn't exist so create it
Else
Set NewProps = NewGrp.GetProperties 'does exist so remove old definition
for i = 1 to NewProps.FieldDefs.Count
NewGrp.RemoveField 0
Next
End If
Set NewProps = NewGrp.GetProperties
'copy elements of cycle group from source to dest
NewProps.FieldDefs.CopyFrom SrcProps.FieldDefs
NewProps.IsCyclic = SrcProps.IsCyclic
NewProps.Labels = SrcProps.Labels
NewProps.Present = SrcProps.Present
NewProps.SortCriterias.CopyFrom SrcProps.SortCriterias
NewGrp.SetProperties NewProps
'we're done, show a message and close down
x=msgbox(CGName&" copied from "&fn_source&" to "&fn_dest&"."&chr(13)&"OK to Save?",vbOKCancel,"Copy_Cycle_Group_Doc_to_Doc")
if x=vbOK then
ObjDest.Save
End if
objDest.CloseDoc 'comment out this line if you'd like dest document to stay open for examination
objSource.CloseDoc
set objSource=nothing
set objDest=nothing
set SrcGrp=nothing
set SrcProps=nothing
set NewGrp=nothing
set NewProps=nothing
End Sub 'end of Copy_Cycle_Group_Doc_to_Doc
Post a Comment