I
IgorM
Hi
I have an Excel xlsx file that has many cube formulas (connected to local
cube built in Excel 2003). I had had to send the file to someone but before
that I had to convert all cube formulas to static values and save the file
as XLS (2003).
In order to convert the values I used the following macro:
Option Explicit
Sub CubeFormulasToStaticValues()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim mwksSheetItem As Excel.Worksheet
Dim mrngCell As Excel.Range
Dim msCellValue As String, msSheetName As String
On Error GoTo ErrHandler
For Each mwksSheetItem In ActiveWorkbook.Sheets
For Each mrngCell In Worksheets(mwksSheetItem.Name).UsedRange
If InStr(mrngCell.Formula, "CUBE") > 0 Then
mrngCell.Copy
mrngCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
Next mrngCell
Next mwksSheetItem
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End
ErrHandler:
MsgBox "Error converting formula.", vbCritical + vbOKOnly, "Macro error"
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
I run the macro and it successfully converted all formulas using cube
formulas to static values.
I then removed the link to the cube and saved the file (still in xlsx). I
then reopened the file and saved in in XLS format. The compatibility
verifier found some formatting that will not be compatible with 2003 file
format but it also found some Analysis Services formatting that, according
to verifier, will not be compatible.
The file was saved without any problems. I was able to open it but whenever
I try to save it under some different name it crashes Excel. I tried saving
it in Excel 2007 and 2010 TP - same problem. On the other hand, it can be
opened in excel 2003 without any problems and saved as any file without any
problems too.
Any suggestion. Can it be the macro that causes the problem?
Kind regards
Igor Muryjas
I have an Excel xlsx file that has many cube formulas (connected to local
cube built in Excel 2003). I had had to send the file to someone but before
that I had to convert all cube formulas to static values and save the file
as XLS (2003).
In order to convert the values I used the following macro:
Option Explicit
Sub CubeFormulasToStaticValues()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim mwksSheetItem As Excel.Worksheet
Dim mrngCell As Excel.Range
Dim msCellValue As String, msSheetName As String
On Error GoTo ErrHandler
For Each mwksSheetItem In ActiveWorkbook.Sheets
For Each mrngCell In Worksheets(mwksSheetItem.Name).UsedRange
If InStr(mrngCell.Formula, "CUBE") > 0 Then
mrngCell.Copy
mrngCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
Next mrngCell
Next mwksSheetItem
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End
ErrHandler:
MsgBox "Error converting formula.", vbCritical + vbOKOnly, "Macro error"
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
I run the macro and it successfully converted all formulas using cube
formulas to static values.
I then removed the link to the cube and saved the file (still in xlsx). I
then reopened the file and saved in in XLS format. The compatibility
verifier found some formatting that will not be compatible with 2003 file
format but it also found some Analysis Services formatting that, according
to verifier, will not be compatible.
The file was saved without any problems. I was able to open it but whenever
I try to save it under some different name it crashes Excel. I tried saving
it in Excel 2007 and 2010 TP - same problem. On the other hand, it can be
opened in excel 2003 without any problems and saved as any file without any
problems too.
Any suggestion. Can it be the macro that causes the problem?
Kind regards
Igor Muryjas