topic Re: Re: Help in Macro in QlikView

Help in Macro

thomas_skariah — Wed, 19 Mar 2014 07:19:07 GMT

Hi All,

I have a qlikview application which export certain charts (not image) to excel in different sheets through button (i.e creating a new excel sheet to a specified location), now i have put a macro inside the stored excel file and execute the macro from qlikview.

Can anyone suggest how to put the macro inside the excel file exported from Qlikview and how to run that macro from qlikview.

or how to export those charts to an existing excel file (In which it will delete the previous sheet or value and create the new sheets ) where the macro is already stored.

Thanks.

Regards,

Tom

Re: Help in Macro

Wed, 19 Mar 2014 07:34:00 GMT

Not quite sure I follow, however you have the external macro in a batch script, then call that from an Action command within QV - ie. Add Action/External/Open URL (URL to the batch script).

Re: Help in Macro

Wed, 19 Mar 2014 08:40:27 GMT

Hi Tony,

write your excel macro code inside a sheet event OnOpen.So it will execute if any one opens that excel file.

Now from qlikview side we can make it open,save and close automatically using vbscript/macro through any trigger.

Please try this,

Sub ExecuteExcel

Dim objXLApp, objXLWb, objXLWs

Set objXLApp = CreateObject("Excel.Application")
Set objXLWb = objXLApp.Workbooks.Open("E:\Docs\Invoice.csv")

objXLWb.Save
objXLWb.Close (False)

Set objXLWs = Nothing
Set objXLWb = Nothing

objXLApp.Quit
Set objXLApp = Nothing

End Sub

Regards

Kumar

Re: Re: Help in Macro

thomas_skariah — Wed, 19 Mar 2014 09:44:43 GMT

Hi Ravi,

Thanks for your comment.

Can you give the steps for sheet event OnOpen.

Regards,

Tom

Re: Re: Re: Help in Macro

Wed, 19 Mar 2014 09:59:23 GMT

Hi,

Please look at this example.

Another way to automatically run a macro when you open a workbook is to write a VBA procedure in the Open event of the workbook by using the Visual Basic Editor. The Open event is a built-in workbook event that runs its macro code each time you open the workbook.

The following simple example uses the Open event to run a macro when you open the workbook.

Save and close all open workbooks.
Open the workbook you want add the macro to, or create a new workbook.
On the Tools menu, point to Macro, and then click Visual Basic Editor.
In the Project Explorer window, right-click the ThisWorkbook object, and then click View Code on the shortcut menu.
In the Object list above the Code window, select Workbook. This will automatically create an empty procedure for the Open event like this:

Private Sub Workbook_Open() End Sub

Add the following lines of code to the procedure:

Private Sub Workbook_Open()  MsgBox Date  Worksheets("Sheet1").Range("A1").Value = Date  End Sub

Switch to Microsoft Excel and save the workbook.
Close and reopen the workbook. When you open the file again, Excel runs the Workbook_Open procedure, which displays today's date in a message box.
Click OK in the message box.
Note that cell A1 on Sheet1 also contains the date as a result of the Workbook_Open procedure.

Regards

Kumar