Wednesday, July 22, 2015

Convert Excel file into Excel Services compliant variant

Excel Services does not support the entire Excel feature set. A.o following aspects are not supported:
  • VBA code,
  • Macros,
  • Comments,
  • Shapes,
  • External Links, to external workbooks
  • (Formula) Names that refer to external workbooks,
  • Data validations,
  • space(s) in name of worksheet connected as Range Name
(see Differences between using a workbook in Excel and Excel Services)
If your 'source' Excel workbook contains any of the above, trying to use it in Excel Services - e.g. via Chart WebPart - results in the generic error 'Exception has been thrown by the target of an invocation'. Required step before using a 'source' Excel workbook in Excel Services is to convert it to a compliant variant. Excel itself does not include such a functionality. But you can facilitate the user (typical functional data management) via a VBA macro in the excel sheet.

VBA Code

Sub SaveWorkbookAsNewFile() Dim ActSheet As Worksheet Dim ActBook As Workbook Dim CurrentFile As String Dim NewFileName As String Dim NewFileType As String Dim NewFile As String Dim ws As Worksheet Application.ScreenUpdating = False CurrentFile = ThisWorkbook.FullName ThisWorkbook.Save RemoveDataValidations ActBook:=ActiveWorkbook RemoveComments ActBook:=ActiveWorkbook RemoveShapes ActBook:=ActiveWorkbook BreakLinks ActBook:=ActiveWorkbook RemoveNamesToExternalReferences ActBook:=ActiveWorkbook RemoveConditionalFormatting ActBook:=ActiveWorkbook RemoveSpacesFromWorksheets ActBook:=ActiveWorkbook RemoveVBA ActBook:=ActiveWorkbook NewFileType = "Excel Workbook (*.xlsx), *.xlsx," & _ "All files (*.*), *.*" NewFile = Application.GetSaveAsFilename( _ InitialFileName:=NewFileName, _ fileFilter:=NewFileType) If NewFile <> "" And NewFile <> "False" Then ActiveWorkbook.SaveAs Filename:=NewFile, _ FileFormat:=xlOpenXMLWorkbook, _ Password:="", _ WriteResPassword:="", _ ReadOnlyRecommended:=False, _ CreateBackup:=False End If ThisWorkbook.Close False Workbooks.Open CurrentFile, False Application.ScreenUpdating = True End Sub Sub RemoveVBA(ActBook As Workbook) On Error Resume Next Dim Element As Object With ActBook.VBProject For Each Element In .VBComponents .VBComponents.Remove Element Next For x = .VBComponents.Count To 1 Step -1 .VBComponents(x).CodeModule.DeleteLines 1, .VBComponents(x).CodeModule.CountOfLines Next x End With End Sub Sub RemoveDataValidations(ActBook As Workbook) Dim ws As Worksheet For Each ws In ActBook.Worksheets ws.Cells.Validation.Delete Next ws End Sub Sub RemoveComments(ActBook As Workbook) Dim ws As Worksheet Dim xComment As Comment For Each ws In ActBook.Worksheets For Each xComment In ws.Comments xComment.Delete Next Next ws End Sub Sub RemoveShapes(ActBook As Workbook) Dim ws As Worksheet Dim sh As Shape For Each ws In ActBook.Worksheets For Each sh In ws.Shapes sh.Delete Next sh Next ws End Sub Sub BreakLinks(ActBook As Workbook) Dim Links As Variant Links = ActBook.LinkSources(Type:=xlLinkTypeExcelLinks) For i = 1 To UBound(Links) ActBook.BreakLink _ Name:=Links(i), _ Type:=xlLinkTypeExcelLinks Next i End Sub Sub RemoveNamesToExternalReferences(ActBook As Workbook) Dim nm As Name For Each nm In ActBook.Names If InStr(nm.RefersTo, "[") <> 0 Then nm.Delete End If Next End Sub Sub RemoveConditionalFormatting(ActBook As Workbook) Dim ws As Worksheet For Each ws In ActBook.Worksheets ws.Cells.FormatConditions.Delete Next ws End Sub Sub RemoveSpacesFromWorksheets(ActBook As Workbook) Dim ws As Worksheet For Each ws In ActBook.Worksheets ws.Name = Replace(ws.Name, " ", "_") Next ws End Sub

No comments:

Post a Comment