14 Replies Latest reply: Jan 25, 2010 4:42 AM by philryan RSS

    Deleting a Sheet with a macro

      Hello,

      How do i remove a sheet in a qlik document using a macro?

      Please help me.

      Best regards and thanks in advance.

        • Deleting a Sheet with a macro

          Hi Onixdiana,

          What do you mean with remove? delete? hide?

          Please, let us know what exactly you want.

          Best regards.

            • Deleting a Sheet with a macro

              Hi Miguel,

              Both of the deleting or hiding represents a solution for want i need.

              A have a document which has a macro for saving the document somewhere else, to reduce some data and then send that information via email.In this document, in the last sheet i put a button to activate the macro, but the person who will get the email is just a user and for preventing the push of that button, i want to hide/remove the entire sheet.

              Thank you very much and i hope you will help me.

              Best Regards.

                • Deleting a Sheet with a macro

                  Hi OnixDiana,

                  here you are my solution, see the follow aspect:

                  • Variable definition
                  • 'ToHide' sheet properties (conditional showing)
                  • Macro editor

                  I hope this help you.

                  Best regards

                    • Deleting a Sheet with a macro

                      I have Qlikview 8.1 installed on my pc and i can't open your file.

                      Can you try to send me again pls.

                        • Deleting a Sheet with a macro

                          Hi OnixDiana,

                          I don't have any problem sending it for you again, but it's very strange you can't open it, I've saved it for Qlikvie 6 to 8 version.

                          I hope you can download and open it correctly.

                          I've decided to describe the steps for get it:

                          • Create a variable named v_hide and its start value will be 0
                          • Copy the following code in macro editor
                            • sub hide
                              set v = activedocument.Variables("v_hide")
                              if v.getContent.string = "1" then
                              v.SetContent "0",true
                              else
                              v.SetContent "1",true
                              end if
                              end sub
                          • In the sheet properties for the sheet to hide, put the following sentence in conditional showing: v_hide = 1
                          • Link the button with the macro procedure
                          • Try it !!

                          Best regards.

                           

                            • Deleting a Sheet with a macro

                              This is the error the second time.

                              Ok thanks i will try and i will get back afterwards.

                              • Deleting a Sheet with a macro

                                I try it but i need to hide it before the macro sends the mail to the users. Sad

                                Anyway thank you very much.

                                Best Regards.

                                  • Deleting a Sheet with a macro

                                    Hi OnixDiana,

                                    There isn't problem with that, you could call the procedure for sending mail in the same procedure like as following:

                                    sub hide
                                    set v = activedocument.Variables("v_hide")
                                    if v.getContent.string = "1" then
                                    v.SetContent "0",true
                                    else
                                    v.SetContent "1",true
                                    end if
                                    call send_mail
                                    end sub

                                    sub send_mail
                                    msgbox "Sending mail"
                                    end sub

                                    I hope this help you.

                                    Best regards.

                                  • Deleting a Sheet with a macro
                                    joseph.thoppil

                                    Your application works correctly in my system. It hides the tab" To Hide " when the button is clicked and it again reappears when it is clicked the second time.

                                      • Deleting a Sheet with a macro

                                        Sub Activate_Discounturi
                                        '---------------------
                                        ACTIVEDOCUMENT.CLEARALL
                                        ActiveDocument.Sheets("Discounturi").Activate
                                        End sub


                                        sub CRISTI
                                        ActiveDocument.clearall
                                        ActiveDocument.Fields("DISTRIBUITOR").Select "DECORA"
                                        ActiveDocument.Fields("DISTRIBUITOR").ToggleSelect "DECORV"
                                        ActiveDocument.Fields("DISTRIBUITOR").ToggleSelect "LIPOTE"
                                        ActiveDocument.Fields("DISTRIBUITOR").ToggleSelect "RAVALE"
                                        ActiveDocument.Fields("DISTRIBUITOR").ToggleSelect "DURABI"
                                        ActiveDocument.Fields("DISTRIBUITOR").ToggleSelect "MURAEX"
                                        ActiveDocument.reducedata
                                        ActiveDocument.saveas "C:\DE TRIMIS LA RSM\CRISTIM.qvw"
                                        call sendmail("name@domain.ro","name_1@domain.ro","raport_vanzari","hello","C:\DE TRIMIS LA RSM\CRISTIM.qvw")
                                        end sub

                                        Public Function SendMail(ByVal From, ByVal SendTo, ByVal Subject, ByVal Body, ByVal Attachment)

                                        Dim objOutlook
                                        Dim objMessage
                                        set objOutlook = CreateObject("Outlook.Application")
                                        set objMessage = objOutlook.CreateItem(0)
                                        objMessage.Subject = Subject

                                        objMessage.To = SendTo
                                        objMessage.Body = Body
                                        If (Not "" = Attachment) Then
                                        objMessage.Attachments.Add(Attachment)
                                        End If

                                        objMessage.send

                                        End Function

                                        this are my macros for now. For macro CRISTI i have a button. In this macro i need to put a function to hide the sheet where is this button, before saving i think.

                                          • Deleting a Sheet with a macro

                                            Ok,

                                            You just have to do the following:

                                            sub CRISTI

                                            call hide <- for hidding the sheet


                                            ActiveDocument.clearall
                                            ActiveDocument.Fields("DISTRIBUITOR").Select "DECORA"
                                            ActiveDocument.Fields("DISTRIBUITOR").ToggleSelect "DECORV"
                                            ActiveDocument.Fields("DISTRIBUITOR").ToggleSelect "LIPOTE"
                                            ActiveDocument.Fields("DISTRIBUITOR").ToggleSelect "RAVALE"
                                            ActiveDocument.Fields("DISTRIBUITOR").ToggleSelect "DURABI"
                                            ActiveDocument.Fields("DISTRIBUITOR").ToggleSelect "MURAEX"
                                            ActiveDocument.reducedata
                                            ActiveDocument.saveas "C:\DE TRIMIS LA RSM\CRISTIM.qvw"
                                            call sendmail("name@domain.ro","name_1@domain.ro","raport_vanzari","hello","C:\DE TRIMIS LA RSM\CRISTIM.qvw")

                                            call hide <- again for showing the sheet
                                            ActiveDocument.saveas "C:\DE TRIMIS LA RSM\CRISTIM.qvw"
                                            end sub

                                            that's all!!

                                              • Deleting a Sheet with a macro

                                                Hi Miguel,

                                                Nice solution for hiding/showing sheets!

                                                I can get your example to work ok on a new qvw but not on an existing one I'm working on that was created by another user.

                                                Is there some setting somewhere that overides the conditional show sheet option?

                                                Many thanks,
                                                Phil

                                                [edited by: philryan at 6:06 (GMT -5) on to, jan 21 2010] I had to uncheck the 'show all sheets and objects' option in the document security tab!