topic Re: Exoprt to excel by authorized users in QlikView

Exoprt to excel by authorized users

Wed, 12 Nov 2014 14:26:32 GMT

Hi all!!

I'm not familiar with macros at all! (unfortunatlly, not even the basics..)

I need a button that will export a table to excel. The table is stored in a container (if it is possible to the macro to recognaize which table is activated and should be exported, else the table will stand alone).

In addition to the export, I need the excel to be saved in a specific folder (NOT just opend).

And last, I want that only authorized users will be able to activate the button, OR that only authorized users will be able to export ALL fileds, and others will be able to export just specific fields.

I attached a QV where I tried to export a table wuth a macro (button) but somehow I couldn't make it to work.

Any help is welcomed!!

Thank you in advance.

Re: Exoprt to excel by authorized users

ali_hijazi — Wed, 12 Nov 2014 14:33:18 GMT

sub ExportQcRegToExcel(QcRegressionExcelFilePath)

'check if the excel file already exists or not

'if not exists then create a new one otherwise edit the existing one

' Starts Excel

set XLApp = CreateObject("Excel.Application")

' Makes it run in background

XLApp.Visible = false

XLApp.DisplayAlerts = false

Set fso = CreateObject("Scripting.FileSystemObject")

If (fso.FileExists(QcRegressionExcelFilePath)) Then

Set XLDoc = XLApp.Workbooks.Open (QcRegressionExcelFilePath)

Else

set XLDoc = XLApp.Workbooks.Add()

' Save the excel-file with the dynamic path and filename

XLDoc.SaveAs QcRegressionExcelFilePath

XLDoc.Sheets.Clear()

XLDoc.Sheets.Add()

XLDoc.Sheets(1).Name = "sheet_name"

End If

ActiveDocument.GetSheetObject("your_chart_id").CopyTableToClipboard true

XLDoc.Sheets("DevGroup").Paste()

XLDoc.Sheets("DevGroup").Rows("1:3000").EntireRow.AutoFit

' Save the excel-file with the dynamic path and filename

XLDoc.SaveAs QcRegressionExcelFilePath

' Exits the current running Excel

XLApp.Quit

end sub

Re: Exoprt to excel by authorized users

Thu, 13 Nov 2014 07:27:47 GMT

Hi Ali and Thank you for your help!

As I mentioned before, I have no prior knowledge in macros... and I couldn't make it work with the script u sent.

can you pls try to upload an example?

Re: Exoprt to excel by authorized users

vardhancse — Thu, 13 Nov 2014 07:30:31 GMT

We can create one button(Action Export to excel)->in layout we can give condition as OSUSER(NTNAME_1,NTNAME_2).

So that only give users can see button in dashboard.

Re: Exoprt to excel by authorized users

ali_hijazi — Thu, 13 Nov 2014 07:54:51 GMT

check attached file

go to sheet 3 and click on the blue button

it exports the pivot table to an excel file near your qvw file

Re: Exoprt to excel by authorized users

Thu, 13 Nov 2014 09:01:00 GMT

I don't know why, but when I click the button it opens the window "edit module" instead of exporting to excel

Re: Exoprt to excel by authorized users

ali_hijazi — Thu, 13 Nov 2014 09:09:49 GMT

do you have write privilege to write the excel in the folder where your qvw exists?

Re: Exoprt to excel by authorized users

amit_saini — Thu, 13 Nov 2014 09:15:03 GMT

Hagit,

Follow below:

Use this macro first:

Sub ExportExcel1

vFile = ActiveDocument.Variables("vInput1").GetContent.String

'XLDoc.SaveAs "c:\Qlikview\Claro"& vFile &".xls"

set obj = ActiveDocument.GetSheetObject("Your_object ID")

obj.ExportEx "\Path where you want to export this file"& vFile &".xls",5

End sub

FUNCTION ExcelExport(objID)

set obj = ActiveDocument.GetSheetObject( objID )

w = obj.GetColumnCount

if obj.GetRowCount>1001 then

h=1000

else h=obj.GetRowCount

end if

Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Add

Create a button -->assign action with this macro name and go to the button properties under condition -->>assign the name of OS user ,whom you want to give access for export.

Thanks,
AS

Re: Exoprt to excel by authorized users

Thu, 13 Nov 2014 09:41:21 GMT

I'm an administrator, So shoul'd have promission.

see attached

Re: Exoprt to excel by authorized users

ali_hijazi — Thu, 13 Nov 2014 11:12:47 GMT

I read the error message

is Microsoft office installed on the machine you are working on?

Re: Exoprt to excel by authorized users

Thu, 13 Nov 2014 12:28:32 GMT

It's working! I had to changed the security properties.. Thank you so much!!!!

Now I have a question about the visability of the button.

Any chance you can help also with that issue?

Re: Exoprt to excel by authorized users

ali_hijazi — Thu, 13 Nov 2014 13:20:37 GMT

yes of course

Re: Exoprt to excel by authorized users

Sun, 16 Nov 2014 08:27:49 GMT

I need the button (that activatets the macro) to be clickable/visible only for some users.

I don't want to use the expression Osuser(), I want to use premission from an existing Active Directory group.

Any chance you know how?

Re: Exoprt to excel by authorized users

Sun, 16 Nov 2014 08:32:17 GMT

Thank you guys, But I need the button (that activatetsthe macro) to be clickable/visable only for some users.

I don't want to use the expression Osuser(), I want to use the premmision from an existing Active Directory group.

Any chance you know how?

Re: Exoprt to excel by authorized users

ali_hijazi — Mon, 17 Nov 2014 07:42:34 GMT

you need to add Section Access to your document

then have an excel sheet for example in which you specify the NTNAME and a Flag (1 or 0) 1 means the button is visible and 0 means the button is not visible and this is controlled in the Layout Tab of the button (conditional visibility)

Re: Exoprt to excel by authorized users

Mon, 17 Nov 2014 10:16:49 GMT

I'm sorry, I think I wasn't that clear.

I'm looking to add to the macro a script that will check if a user is in a specific Active Directory Group - If so, the macro will continue and the user will be able to export the object to excel (with the script you sent me). If not - the user won't be able to export (If we can through an error message to the user it will be great).

I'm trying to do that instead of making the button invisible for a non authorized users.

Re: Exoprt to excel by authorized users

ali_hijazi — Mon, 17 Nov 2014 10:22:33 GMT

you can get the group to which each user belongs to and add a flag of 1 or 0 next to each group and depending on whether the flag is 1 or zero that macro is executed or not

Re: Exoprt to excel by authorized users

Anonymous — Mon, 14 Nov 2016 20:06:04 GMT

Hi Hagit,

I'm having the same issue when I click the button to export to Excel it opens the window "edit module" instead of exporting to excel. Which security setting did you change to get this to work? Thanks in advance.