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

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
Partner - Specialist II
Partner - Specialist II

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

Can add the macrocode here?

Not applicable
Author

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
Partner - Specialist II
Partner - Specialist II

Ok

Do you have Excel on the user machine?

Can you do an Excel export when running Ajax?

Not applicable
Author

Hi,

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

Best regrads,

Not applicable
Author

Hi,

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

It is a known limitation.

...

Ashutosh

Not applicable
Author

Hi,

Thanks all for your reply,

Is a method to run this macro when running Ajax.

Best Regards,