Qlik Community

QlikView Management

Discussion Board for collaboration on QlikView Management.

Not applicable

Create Excel Files via Macro on QlikView Server

Hey all,

I was searching the forum, but couldn't find a related post to my question. I really hope you can help me.

Here is the situation: The user clicks on a button. The button triggers a macro opens an excel file on our server, edits it and after that saves it into a specific folder. This works really fine with QlikView Desktop. But on our QlikView Server the macro does absolutely nothing. It never creates an Excel.Application Object nor does it save an excel file into a folder.

This is a short vbscript code just for testing:

Sub test

Set xls = CreateObject("EXCEL.APPLICATION")

Set wb = xls.Workbooks.Open ("D:\Mediaplanvorlagen\Mediaplan Vorlage_SollIst.xls")
xls.Visible = true

xls.ActiveWorkbook.SaveAs "D:\Mediaplan.xls"
xls.Quit

End Sub

Excel is installed on the server. We use the QlikView server version 10.0 x64. "Allow macro execution on server" and "Allow unsafe macro execution on server" are both enabled.

Can anyone test the code above with some example excel-files? Or is there any reason why it doesn't work?

Greetings

Daniel

1 Solution

Accepted Solutions
Not applicable

Create Excel Files via Macro on QlikView Server

Hi

I use IE Plugin.

Regards

Anders

9 Replies
Not applicable

Create Excel Files via Macro on QlikView Server

Hi

Have you mark

Requested module security = System access

Current local security=Allow system access

in Edit module?

Regards

Anders

Not applicable

Create Excel Files via Macro on QlikView Server

Hi,

yeah, I marked them.

The whole thing works when I use this code to create a simple textfile:


Sub textfile


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("D:\test.txt")


End Sub

When I use this code and click on a button, a textfile is created on the server. I really have no clue why that doesn't work with an excel object...

Not applicable

Create Excel Files via Macro on QlikView Server

Hi

I have this macro that creates a new excel-document and it´s work.

Regards

Anders

Sub SaveToExcel


NewFileName = "C:\QlikView\Test.xlsx"
set obj = ActiveDocument.GetSheetObject("CH18")
set XLApp = CreateObject("Excel.Application")
XLApp.Visible = true
set XLDoc = XLApp.Workbooks.Add
set rngStart = XLDoc.Sheets(1).Range("A1")
set XLSheet = XLDoc.Worksheets("Sheet1")
Const xlCenter = -4108

obj.CopyTableToClipboard true
XLDoc.Sheets("Sheet1").Paste()
XLDoc.WorkSheets("Sheet1").Cells.select
XLDoc.WorkSheets("Sheet1").Cells.EntireRow.RowHeight = 12.75
XLDoc.WorkSheets("Sheet1").Cells.EntireColumn.AutoFit


set Selection = XLSheet.Columns("A:H")
With Selection
.VerticalAlignment = xlCenter
.Borders.ColorIndex = 0 'black border
End With
XLSheet.Name = "Test"

XLDoc.SaveAs NewFileName

XLApp.Quit

end sub

Not applicable

Create Excel Files via Macro on QlikView Server

Hi Anders,

thank you for your code. It also works with QlikView Desktop, but unfortunately, when I try it on AJAX Zero Footprint Client on QlikView Server, the code doesn't work. This is the whole problem. I can create and edit simple textfiles, but not excel files.

This code works with AJAX ZfC:

Sub textfile

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("D:\test.txt")

End Sub

I guess AJAX doesn't allow Excel.Application Objects :/

Not applicable

Create Excel Files via Macro on QlikView Server

Hi

I use IE Plugin.

Regards

Anders

suniljain
Honored Contributor

Create Excel Files via Macro on QlikView Server

It will work only in IE Plug In.

Not applicable

Create Excel Files via Macro on QlikView Server

Hey,

thank you for your help. I understood that you have limited macro functionality with the AJAX ZfC. But I found a solution!

If you want to export your data into a specific Excel file and Excel automatically do something with the data, you can do the following:

In your QlikView Macro export the data by writing them into a txt file with the "FileSystemObject" properties. With the "WScript.Shell" Object you then can open an Excel file. In that Excel file you have to write a macro which is automatically triggered by opening [Workbook Open event]. Then this macro has to open the txt file with the exported data, writes in into a sheet and finally you have successfully exported the data into excel. The excel-macro can now work with that and create charts, overviews, reports or anything else you want to create.

Hope you understood, what I tried to explain. Smile

Daniel

Not applicable

Re: Create Excel Files via Macro on QlikView Server

Thanks. I worked on this same issue for ages and have settled on using the iePlugin. Works now. Actually the iePlugin makes everything look on the web look a lot more like QV desktop ...

Not applicable

Re: Create Excel Files via Macro on QlikView Server

Hi Daniel,

Does this work with the AJAX client? Because I get the error "ActiveX component can't create object" in the VB script editor.

Community Browser