6 Replies Latest reply: Jan 26, 2012 8:28 AM by jeremyqv RSS

    Ajax and Macro



      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.

        • Ajax and Macro
          Jerry Svensson

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


          Can add the macrocode here?

            • Ajax and Macro



              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




                  set objSource = qvDoc.GetSheetObject(qvObjectId)

                  Call objSource.GetSheet().Activate()





                  if (not objSource is nothing) then


                      if (pasteMode = "image") then

                          Call objSource.CopyBitmapToClipboard()


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

                      end if


                      Set objCurrentSheet = objExcelDoc.Sheets(sheetName)




                      if (pasteMode <> "image") then

                      With objExcelApp.Selection

                          .WrapText = False

                          .ShrinkToFit = False

                      End With                    

                      end if       



                  end if






              Call Excel_DeleteBlankSheets(objExcelDoc)  






              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




              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



              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

            • Re: Ajax and Macro
              Ashutosh Paliwal


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

              It is a known limitation.