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

    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.

        • Ajax and Macro
          Jerry Svensson

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

           

          Can add the macrocode here?

            • 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

            • Re: Ajax and Macro
              Ashutosh Paliwal

              Hi,

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

              It is a known limitation.

               

               

              ...

              Ashutosh