Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

Ajax and Macro

Hi,

I am using now Ajax but the Macro dosen't working knowing it's work well with IE Plugin.

For information i used version 10SR3.

Have you same problem as me and how you solved this?

Many thanks for your help.

Labels (1)
6 Replies
jerrysvensson
Valued Contributor II

Ajax and Macro

Have you checked the box "Allow unsafe Macros" on the server?

Can add the macrocode here?

Not applicable

Ajax and Macro

Hi,

Yes, i checked the box " Allow unsafe Macros".

Here is macro code :

sub exportToExcel

Dim aryExport(7,3)

aryExport(0,0) = "IndustryDetail"

aryExport(0,1) = "Analysis Chart"

aryExport(0,2) = "A1"                

aryExport(0,3) = "image"

Dim objExcelWorkbook 'as Excel.Workbook

Set objExcelWorkbook = copyObjectsToExcelSheet(ActiveDocument, aryExport)

end sub

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

    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

              

              

next   

Call Excel_DeleteBlankSheets(objExcelDoc)  

objExcelDoc.Sheets(1).Select

Set copyObjectsToExcelSheet = objExcelDoc

end function

________________________________________________________________

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

Next

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

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

________________________________________________________________

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 If

Next

   

End Sub

________________________________________________________________

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

jerrysvensson
Valued Contributor II

Ajax and Macro

Ok

Do you have Excel on the user machine?

Can you do an Excel export when running Ajax?

Not applicable

Ajax and Macro

Hi,

Yes i have Excel on the machine and i can export when running Ajax.

Best regrads,

Not applicable

Re: Ajax and Macro

Hi,

Some Macros will not work on Ajax while it will work in IE.

It is a known limitation.

...

Ashutosh

Not applicable

Ajax and Macro

Hi,

Thanks all for your reply,

Is a method to run this macro when running Ajax.

Best Regards,

Community Browser