Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Exoprt to excel by authorized users

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.

17 Replies
ali_hijazi
Partner - Master II
Partner - Master II

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

I can walk on water when it freezes
Not applicable
Author

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?

vardhancse
Specialist III
Specialist III

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.

ali_hijazi
Partner - Master II
Partner - Master II

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

I can walk on water when it freezes
Not applicable
Author

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

ali_hijazi
Partner - Master II
Partner - Master II

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

I can walk on water when it freezes
amit_saini
Master III
Master III

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

OR

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

Not applicable
Author

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

see attached

ali_hijazi
Partner - Master II
Partner - Master II

I read the error message

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

I can walk on water when it freezes