0 Replies Latest reply: May 12, 2012 12:37 PM by Przemysław Jedynowicz RSS

    Macro to backup many qvw files...

    Przemysław Jedynowicz

      Hi,

       

      This discussion is connected with my earlier one (http://community.qlik.com/thread/53227)

       

      I'm looking for a way to backup many files, secured with Section Access. We are migrating to a new Domain\user names and the problem is that those files cannot be updated (to apply new security) any more because the data sources are not there any more. When we change domain we will not be able to access those files.

       

      Maybe you will have an idea how to do it - my only thought right now is to export all data visible in those files to XLS files and place them in separate folders per file.

       

      What I came to now (but what is not working yet) is below macro. Could you please help me fix it?

       

      It is using a list of file paths like this one:

       

      c:\QLIKVIEW_SERVER\file1.qvw

      c:\QLIKVIEW_SERVER\dir\file.qvw

      c:\QLIKVIEW_SERVER\xfile.qvw

       

      loaded in script to one field "files"

       

      The macro should open each file from "files" and sheet by sheet, object by object export everything to a separate filder/file in XLS format (for now I just try to put it to different files, folders later...)

       

       

      I have three problems with this macro:

      - it doesn't works when run from a button with assigned macro (than it only opens the Macro view). For now I run it from Macro view with Test button...

      - I got an error "Object doesn't support this property or method: 'xx.ExportBiff'" but according to ApiGuide example it should work, because xx is an "set xx=ActiveDocument.GetSheetObject(....)" object

      - when I comment export line I get messages when qv tries to open file that I have no access to or opens Macro security window for files that use advanced macros... how to hide those notifications to have the export process automatic?

       

       

      ---- 8=< -----------------------------------------------------------------------------------

       

      sub run

                set App = ActiveDocument.GetApplication

       

                set val=ActiveDocument.Fields("files").GetPossibleValues

                for i=0 to val.Count-1

       

       

                          Set MyApp = CreateObject("QlikTech.QlikView")

                          Set MyDoc = MyApp.OpenDoc (val.Item(i).Text,"","")

       

                          for a=0 to ActiveDocument.NoOfSheets-1

                                    set ss=ActiveDocument.GetSheet(a)

                                    ActiveDocument.Sheets(ss.GetProperties.Name).Activate

                                    Objects=ActiveDocument.ActiveSheet.GetSheetObjects

                                    for b=lBound(Objects) to uBound(Objects)

                                              set xx=ActiveDocument.GetSheetObject(mid(Objects(b).GetObjectID,10,len(Objects(b).GetObjectID)-9))'Objects(b)

                                              'msgbox(replace(replace(replace(replace("c:\TMP\export\"&val.Item(i).Text&"__"&Objects(b).GetObjectId&".xls",":","_"),"\","_"),"[","_"),"]","_"))

                                              xx.ExportBiff ""&replace(replace(replace(replace("c:\TMP\export\"&val.Item(i).Text&"__"&Objects(b).GetObjectId&".xls",":","_"),"\","_"),"[","_"),"]","_")

                                    next

                          next

                          Set MyDoc = Nothing

                          Set MyApp = Nothing

                next

       

       

      end sub

       

      ---- 8=< -----------------------------------------------------------------------------------

       

       

      BR