topic Re: Macro Export To Excel doesn't work on local version in QlikView

Macro Export To Excel doesn't work on local version

amber2000 — Wed, 25 Nov 2020 16:16:04 GMT

Hello Everyone,

I created a macro that Exports Document objects to Excel (I used the code from Stefan Walther as example)

Sub ExportToExcel_Evaluations

Dim aryExport(3,3)

aryExport(0,0) = "CS09" '//ID of the Qlikview object to copy from

aryExport(0,1) = "Evaluations" '//Name of the Sheet in Excel where the object should be copied to

aryExport(0,2) = "A1" '//Range in Excel where the object should be pasted to

aryExport(0,3) = "data" '//Copy & paste Mode (data, Image)

aryExport(1,0) = "CH31"

aryExport(1,1) = "Evaluations"

aryExport(1,2) = "A15"

aryExport(1,3) = "data"

aryExport(2,0) = "CH11"

aryExport(2,1) = "Jobs by date"

aryExport(2,2) = "A1"

aryExport(2,3) = "data"

Dim objExcelWorkbook 'as Excel.Workbook

Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

End Sub

Then on the Button Properties --> Actions I chose Run Macro and in Macro Name I placed the name of the macro: ExportToExcel_Evaluations

When I klik the button the Edit Macro window opens and that's all.

Does anyone know what I've done wrong?

Monique

Re: Macro Export To Excel doesn't work on local version

m_woolf — Fri, 17 Mar 2017 16:17:24 GMT

Since the debugger no longer works in QlikView, you might use a msgbox to troubleshoot.

Insert a msgbox "hi" command (say before the Set objExcelWorkbook line).

If you see a msgbox when you run the macro, all lines are OK before the msgbox line. Continue to move the line down until the macro fails withlut displaying the msgbox. The line above the msgbox line in the one that is failing.

If you don't see a msgbox when you run the macro, move the msgbox up a line until the msgbox appears when you run the macro. The line below the msgbox line is the one that is faling.

You aren't showing the code for the copyObjectsToExcelSheet function. Is it there?

Re: Macro Export To Excel doesn't work on local version

amber2000 — Fri, 17 Mar 2017 16:41:16 GMT

Hi,

I used the macro from the forum because it's the second time I'm trying to use a macro.

Sorry but I don't know if there should be additional code for the copyObjectsToExcelSheet function.

I used the msgbox to see where it's faling and the msgbox doesn't appear anymore after the Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport).

Kind regards,Monique

Re: Macro Export To Excel doesn't work on local version

sasiparupudi1 — Fri, 17 Mar 2017 19:29:28 GMT

It looks like you are missing a sub routine in your macro editor?

copyObjectsToExcelSheet

Re: Macro Export To Excel doesn't work on local version

sasiparupudi1 — Fri, 17 Mar 2017 19:40:37 GMT

Here is the code From qlikblog

'// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

'// YOU DO NOT NEED TO CHANGE THE CODE BELOW !!!!!!!!!!!!!!!!!!!!!!!

'// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

'// ****************************************************************

'// copyObjectsToExcel

'// ~~

'// Parameters:

'// qvDoc - Reference to the QlikView document (normally just use

'// "ActiveDocument", but you can also use copyObjectsToExcel

'// outside of QlikView ...

'// aryExportDefinition - array of settings

'// ~~

'// Version 1.02

'// ~~

'// The aryExportDefinition is used to pass the following properties to

'// copyObjectsToExcelSheet:

'//

'// Index Description

'// ------------------------

'// 0 - Id of the QlikView object to copy from

'// 1 - Name of the sheet (in Excel) where the object should be copied to

'//

'// (If a sheet with the same name already exists no new

'// sheet will be created, instead the existing sheet will

'// be used for pasting the object)

'//

'// Note: the sheetName can be max 31 characters long

'//

'// 2 - Range in Excel where the object should be pasted to

'// 3 - PasteMode ["data", "image"]

'// Defines if the objects underlaying data should be

'// pasted ("data") or the the image representing the object

'// should be used

'// ****************************************************************

Private Function copyObjectsToExcelSheet(qvDoc, aryExportDefinition) 'as Excel.Workbook

Dim i 'as Integer

Dim objExcelApp 'as Excel.Application

Dim objExcelDoc 'as Excel.Workbook

Set objExcelApp = CreateObject("Excel.Application")

objExcelApp.Visible = true 'false if you want to hide Excel

objExcelApp.DisplayAlerts = false

Set objExcelDoc = objExcelApp.Workbooks.Add

Dim strSourceObject

Dim qvObjectId 'as String

Dim sheetName

Dim sheetRange

Dim pasteMode

Dim objSource

Dim objCurrentSheet

Dim objExcelSheet

for i = 0 to UBOUND(aryExportDefinition)

'// Get the properties of the exportDefinition array

qvObjectId = aryExportDefinition(i,0)

sheetName = aryExportDefinition(i,1)

sheetRange = aryExportDefinition(i,2)

pasteMode = aryExportDefinition(i,3)

Set objExcelSheet = Excel_GetSheetByName(objExcelDoc, sheetName)

if (objExcelSheet is nothing) then

Set objExcelSheet = Excel_AddSheet(objExcelApp, sheetName)

if (objExcelSheet is nothing) then

msgbox("No sheet could be created, this should not occur!!!")

end if

objExcelSheet.Select

set objSource = qvDoc.GetSheetObject(qvObjectId)

Call objSource.GetSheet().Activate()

objSource.Maximize

qvDoc.GetApplication.WaitForIdle

if (not objSource is nothing) then

if (pasteMode = "image") then

Call objSource.CopyBitmapToClipboard()

else

Call objSource.CopyTableToClipboard(true) '// default & fallback

end if

Set objCurrentSheet = objExcelDoc.Sheets(sheetName)

objExcelDoc.Sheets(sheetName).Range(sheetRange).Select

objExcelDoc.Sheets(sheetName).Paste

if (pasteMode <> "image") then

With objExcelApp.Selection

.WrapText = False

.ShrinkToFit = False

End With

end if

objCurrentSheet.Range("A1").Select

end if

Call Excel_DeleteBlankSheets(objExcelDoc)

'// Finally select the first sheet

objExcelDoc.Sheets(1).Select

'// Return value

Set copyObjectsToExcelSheet = objExcelDoc

end function

'// ________________________________________________________________

'// ****************************************************************

'// Internal function for getting the Excel sheet by sheetName

'// ****************************************************************

Private Function Excel_GetSheetByName(ByRef objExcelDoc, sheetName) 'as Excel.Sheet

For Each ws In objExcelDoc.Worksheets

If (trim(ws.Name) = Excel_GetSafeSheetName(sheetName)) then

Set Excel_GetSheetByName = ws

exit function

End If

'// default return value

Set Excel_GetSheetByName = nothing

End Function

'// ________________________________________________________________

Private Function Excel_GetSafeSheetName(sheetName)

'// can be max 31 characters long

retVal = trim(left(sheetName, 31))

Excel_GetSafeSheetName = retVal

End Function

'// ****************************************************************

'// Internal function for adding a new sheet

'// ****************************************************************

Private Function Excel_AddSheet(objExcelApplication, sheetName) ' as Excel.Sheet

'// add a sheet to the last position

objExcelApplication.Sheets.Add , objExcelApplication.Sheets(objExcelApplication.Sheets.Count)

Dim objNewSheet

Set objNewSheet = objExcelApplication.Sheets(objExcelApplication.Sheets.Count)

objNewSheet.Name = left(sheetName,31)

'// return the newly created sheet

Set Excel_AddSheet = objNewSheet

End function

'// ________________________________________________________________

'// ****************************************************************

'// Delete all empty sheets

'// ****************************************************************

Private Sub Excel_DeleteBlankSheets(ByRef objExcelDoc)

For Each ws In objExcelDoc.Worksheets

If (not HasOtherObjects(ws)) then

If objExcelDoc.Application.WorksheetFunction.CountA(ws.Cells) = 0 Then

On Error Resume Next

Call ws.Delete()

End If

End Sub

'// ________________________________________________________________

'// ****************************************************************

'// Helper function to determine if there are other objects placed

'// on the sheet ...

'// ****************************************************************

Public Function HasOtherObjects(ByRef objSheet) 'As Boolean

Dim c

If (objSheet.ChartObjects.Count > 0) Then

HasOtherObjects = true

Exit function

End If

If (objSheet.Pictures.Count > 0) Then

HasOtherObjects = true

Exit function

End If

If (objSheet.Shapes.Count > 0) Then

HasOtherObjects = true

Exit function

End If

HasOtherObjects = false

End Function

'//__________________________________________________________________

Re: Macro Export To Excel doesn't work on local version

amber2000 — Tue, 21 Mar 2017 14:14:01 GMT

Hi Sasidhar,

Thank you for the information but the macro stops at

qvDoc.GetApplication.WaitForIdle 1000

I've read a post that setting a delay in seconds could help but in my case it's doing nothing.

Further information on what to do next then isn't available (or I don't see it)

Do you know how to fix this?

Kind regards,

Monique

Re: Macro Export To Excel doesn't work on local version

sasiparupudi1 — Tue, 21 Mar 2017 15:40:06 GMT

‌ccould you attach a sample to investigate further into your problem?

Re: Macro Export To Excel doesn't work on local version

amber2000 — Tue, 21 Mar 2017 17:47:47 GMT

Sasidhar,

I'm so happy to say that It's working (I didn't wait long enough for the result to show up).

The only thing that's bothering me is that in my workbook is also showing the standardsheets 'Sheet1', 'Sheet2' and 'Sheet3'.

How can I integrate tot delete them in the macro?

Kind regards,

Monique

Re: Macro Export To Excel doesn't work on local version

MK9885 — Tue, 21 Mar 2017 18:10:11 GMT

Why do want a Macro?

When you can do it by default export to xl within Qlik.

Are you selecting only particular rows/columns to be exported and rest to be ignored?

Re: Macro Export To Excel doesn't work on local version

amber2000 — Wed, 22 Mar 2017 09:04:49 GMT

Hi Aehman,

I want to make it easy for end Users.

Instead of exporting table by table (because of different tabs and the need of different tables) I want them to click on the button so that they have all the needed tables in one Excel workbook instead of a workbook per table.

Kind regards,

Monique

Re: Macro Export To Excel doesn't work on local version

sasiparupudi1 — Wed, 22 Mar 2017 10:09:39 GMT

You could try something like

Sub x()
RemoveSheet "Sheet"
End Sub

Sub RemoveSheet(SheetPrefix)

For Each oSheet In ActiveWorkbook.Sheets
'MsgBox oSheet.Name
If Left(oSheet.Name, 5) = SheetPrefix Then
oSheet.Delete
End If
Next
End Sub

Re: Macro Export To Excel doesn't work on local version

MK9885 — Wed, 22 Mar 2017 12:32:52 GMT

Are you aware that many Macros don't run on server or Access Point?

If your users will access the application in Access Point then you should first test it on server, cus it didn't work for me.

Sometimes Macros don't run in Safe Mode...

Change it to below and try again?

Re: Macro Export To Excel doesn't work on local version

amber2000 — Wed, 22 Mar 2017 14:43:59 GMT

Hi Aehman,

This didn't work either, even opening in IE instead of Ajax environment didn't help.

I think I have to accept that I can't use Macro's for End Users at all

Your help was much appreciated.

Monique