Archive

Author Archive

Shrinking Excel 2010/2007 Files by about 25%

September 9th, 2011 admin No comments

We know the compression technology Excel uses to create and save the files is not optimum.
So after reading a post on BaconBits about it, I thought I would write a macro who creates (small) copies of selected excel files.
The files I produced were all around 25% smaller than the originals.
Worth the effort I would say….

Bare in mind if you save the created files they will grow again….

you can download a sample workbook here Shrink Excel Files

Sub ShrinkExcelFiles()

    Dim Fname
    Dim i As Long
    Dim sFileFolder As String

    Fname = Application.GetOpenFilename(filefilter:="Excel (*.xls*), *.xls*", _
            MultiSelect:=True, Title:="Select the Excel files you want to shrink")  

    If IsArray(Fname) = False Then
    Else

       For i = LBound(Fname) To UBound(Fname)

           sUnzipFolder = Left(Fname(i), InStrRev(Fname(i), "\")) & "unzip\"
           ShrinkXlsX Fname(i), sUnzipFolder

       Next i

    End If

End Sub
Sub ShrinkXlsX(sFileName, sTempFolder)

    Dim objApp As Object
    Dim vFileName As Variant
    Dim sFileExtension As String
    Dim i As Long

    sFileExtension = Right(sFileName, Len(sFileName) - InStrRev(sFileName, "."))

    Name sFileName As sFileName & ".zip"

    CreateFolder sTempFolder

    Set oApp = CreateObject("Shell.Application")

    For Each ItemInZip In oApp.Namespace(sFileName & ".zip").items

        oApp.Namespace(sTempFolder).CopyHere (ItemInZip)

    Next

    Open sFileName & "_Small.zip" For Output As #1
    Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
    Close #1

    i = 1

    For Each ItemInFolder In oApp.Namespace(sTempFolder).items

        oApp.Namespace(sFileName & "_Small.zip").CopyHere (ItemInFolder)

        Do Until oApp.Namespace(sFileName & "_Small.zip").items.Count = i

            Application.Wait (Now + TimeValue("0:00:01"))

        Loop

        i = i + 1

    Next

    Name sFileName & "_Small.zip" As sFileName & "_Small." & sFileExtension
    Name sFileName & ".zip" As sFileName

    DeleteFolder sTempFolder

End Sub
Sub DeleteFolder(MyPath)

    Dim FSO As Object

    Set FSO = CreateObject("scripting.filesystemobject")

    If Right(MyPath, 1) = "\" Then MyPath = Left(MyPath, Len(MyPath) - 1)

    If FSO.FolderExists(MyPath) = False Then Exit Sub

    FSO.DeleteFolder MyPath

End Sub

Sub CreateFolder(MyPath)

    Dim FSO As Object

    Set FSO = CreateObject("scripting.filesystemobject")

    If Right(MyPath, 1) = "\" Then MyPath = Left(MyPath, Len(MyPath) - 1)

    If FSO.FolderExists(MyPath) = True Then DeleteFolder MyPath

    FSO.CreateFolder MyPath

End Sub
Categories: Excel, VBA Tags: ,

Average and Standard Deviation without outliers in Excel

September 2nd, 2011 admin No comments

Sometimes you need the average and standard deviation of a dataset without a certain amount of outliers.

For example you want the average or standard deviation without the top 2.5% datapoints

{=AVERAGE(IF((DataRng<LARGE(DataRng,ROUNDUP((COUNT(DataRng)*0.025),0))),DataRng))}

{=STDEV.P(IF((DataRng<LARGE(DataRng,ROUNDUP((COUNT(DataRng)*0.025),0))),DataRng))}

If you want the average without the top 10 and bottom 10 datapoints this would be your solution

{=AVERAGE(IF((DataRng<LARGE(DataRng,10))*(DataRng>SMALL(DataRng,10)),DataRng))}

happy averaging or standard deviating

Categories: Excel, Formulas Tags: ,

Unprotect Sheet in Excel

August 30th, 2011 admin No comments

I’m back from my holiday in the land of no internet and cellphone coverage.
The up side was ( or one of the up sides ) I had time finally post this piece of code to unprotect a sheet with a forgotten password.
The nice thing is you can protect the sheet with the found password and the old password still works …. lol

Sub PasswordBreaker()

   Dim i0 As Integer, i1 As Integer, i2 As Integer, i3 As Integer
   Dim i4 As Integer, i5 As Integer, i6 As Integer, i7 As Integer
   Dim i8 As Integer, i9 As Integer, m1 As Integer, m2 As Integer

   Dim sPassword As String

   On Error Resume Next

   For i0 = 65 To 66: For i1 = 65 To 66: For i2 = 65 To 66: For i3 = 65 To 66
   For i4 = 65 To 66: For i5 = 65 To 66: For i6 = 65 To 66: For i7 = 65 To 66
   For i8 = 65 To 66: For i9 = 65 To 66: For m1 = 65 To 66: For m2 = 32 To 126

   sPassword = ""
   sPassword = sPassword & Chr(i0) & Chr(i1) & Chr(i2) & Chr(i3) & Chr(i4) & Chr(i5)
   sPassword = sPassword & Chr(i6) & Chr(i7) & Chr(i8) & Chr(i9) & Chr(m1) & Chr(m2)

   ActiveSheet.Unprotect sPassword

   Application.StatusBar = False

   If ActiveSheet.ProtectContents = False Then

      MsgBox "Password used to unprotect this sheet is: " & sPassword

      Exit Sub

   End If

   Next: Next: Next: Next: Next: Next: Next: Next: Next: Next: Next: Next

End Sub

regards

Categories: Excel, VBA Tags: ,

Demo for SQL Server Project Crescent

July 20th, 2011 admin No comments

L.S.

Found a great short Demo for SQL Server Project Crescent on Youtube.

enjoy

Categories: Cresent, Denali Tags: ,

Excel 2010 Ctrl combination shortcut keys

July 20th, 2011 admin No comments

L.S.

Found this great pdf with Excel 2010 Ctrl combination shortcut keys.

enjoy

Categories: Excel Tags:

Strange VBA Code

July 15th, 2011 admin No comments

L.S.

I recently came upon some vba code in a workbook from the Hong Kong branch.
There were a lot of construction in there like:

Range(ThisWorkbook.Names("RANGE_NAME").RefersTo).ListObject.QueryTable.Refresh
Range(ThisWorkbook.Names("RANGE_NAME").RefersTo).Cells(1, 1).Value

Does anybody know why someone would code like this….?

regards..

Categories: Excel, VBA Tags: ,

Denali CTP3 Downloads

July 12th, 2011 admin No comments

L.S.

I got a link from a tweet from @marcorus for downloading Denali CTP3 but it didn’t work.
Figured out what the URL’s should be….

64 bit files

Core.box
Install.exe
Lang.box

32 bit files

Core.box
Install.exe
Lang.box

enjoy ( for now …. ? )

Categories: Denali Tags:

Great article about using MDX in OLAP Pivottables (PowerPivot)

July 12th, 2011 admin No comments

L.S.

I found this great article Quartile, Percentile and Median in PowerPivot. It’s about using MDX in OLAP Pivottables and the ability to perform COM interop calls through the ExcelMDX assembly.
It’s not to long but I’m sure I will spend a lot of time putting it to use…

thnx Javier Guillen

Microsoft PowerPivot Code-Named Denali CTP3 Release Notes

July 11th, 2011 admin No comments

As noticed by @dhExcel Microsoft published this but removed it from there site shortly after, but google cached it here.

1.0 What’s New

The Microsoft SQL Server Code-Named “Denali”, Community Technology Preview 3 (CTP 3) release of PowerPivot for Excel includes the following new features:

Diagram View. The Diagram View is available on the Home tab of the PowerPivot window, and it enables you to view tables in a visually organized way and to easily add and change relationships and hierarchies. For more information, see the PowerPivot Window: Diagram View Help topic.

Hierarchies. A hierarchy is a list of child nodes that you can create from columns and place into any order you want, making it easier for reporting client users to select and navigate the common paths of data. For more information about hierarchies, see the Hierarchies in PowerPivot and Create a Hierarchy in a Table (Tutorial) Help topics.

Relationships in Diagram View. In Diagram View, you can easily create relationships between columns in separate tables. The relationships appear visually, which enables you to quickly see how all the tables relate to each other. For more information about Relationships in Diagram View, see the Create Relationships Between Tables (Tutorial) Help topic.

Multiple Relationships. We added the ability to import multiple relationships. The first relationship is active, and the other relationships are inactive and appear as dotted lines in Diagram View. For more information, see the PowerPivot Window: Diagram View and Create Relationships Between Tables (Tutorial) Help topics.

Measure Grid. The Measure Grid enables you to view measures in a grid pattern and to easily create, edit, and manage measures and Key Performance Indicators (KPIs) within the model. For more information, see the PowerPivot Window: Measure Grid and Create a Measure and KPI (Tutorial) Help topics.

Advanced Tab. The advanced features are now available in a separate tab. The features include the ability to create or edit perspectives, summarize a numeric column by an aggregation function, and set reporting properties for a reporting client tool, such as Project Crescent. For more information, see the PowerPivot Window: Advanced Tab Help topic.

Reporting Properties. In the Reporting Properties area of the Advanced tab, you can set the table identifier, group values based on a table identifier, add table details, set the representative column, set an image URL, and set the representative image for reporting client tools, such as Project Crescent. For more information, see the PowerPivot Window: Advanced Tab Help topic.

New DAX Function. We added a new function to enrich the relationship feature:

USERELATIONSHIP Function. Specifies one or more relationships to be used in a specific calculation. For more information, see the USERELATIONSHIP Function (DAX) Help topic.

Sort by Other Column. If you do not want to sort your column alphabetically, you can now sort your column by another column. For example, you can sort by a month number column that assigns each month its number in order to sort the column in a natural way.

Add Values to Rows and Columns. You can now add values to rows and columns.

Blob Support. You can now import images and blobs. Blob data is now automatically detected and accepted as a binary data type.

Other Tab Changes. We moved the ribbon features around in the Home and Design tabs slightly, based on customer feedback and usability studies.

Home Tab. The Freeze and Column Width buttons were added to the Design tab. The View area was added with the Data View, Diagram View, Show Hidden, and Measure Grid buttons. For more information about the new Home tab, see the PowerPivot Window: Home Tab, PowerPivot Window: Diagram View, and PowerPivot Window: Measure Grid Help topics.

Design Tab. The Hide and Unhide button was removed and became the new Show Hidden button that was added to the Home tab. The Mark as Date Table button was added. When you switch to Advanced Mode, the Advanced area is no longer added to the Design tab (the Advanced tab is added instead). For more information about the new Design tab, see the PowerPivot Window: Design Tab Help topic.

In addition, the following features were added to previous SQL Server Code-Named “Denali” releases of PowerPivot:

Perspectives. With the Advanced Mode’s Perspectives feature, you can add, edit, delete, copy, and view perspectives. Perspectives are metadata layers that track different slices or sets of data. Perspectives are typically defined for a particular user group or business scenario and make it easier to navigate large data sets. For more information, see the Perspectives in PowerPivot and Perspectives Dialog Box Help topics.

Key Performance Indicators. A KPI is based on a specific measure and is designed to help evaluate the current value and status of a metric. For more information, see the Key Performance Indicators (KPIs) in PowerPivot and Key Performance Indicator Dialog Box Help topics.

Date Table Settings. You can mark a table as date table, which will enable you to leverage date filtering in Excel. For more information, see the Date Table Settings Dialog Box Help topic.

Show Details. Right-click a cell in a PivotTable in Excel, and then click Show Details. A new worksheet opens with the underlying data that contribute to the value of the specified cell. For more information, see “Show Details” in the Create a PivotTable or PivotChart Report Help topic.

New DAX Functions. We added several new functions to extend the language in areas that were requested the most by our customers:

Statistical Functions. For more information, see the Statistical Functions (DAX) Help topic.

DISTINCTCOUNT. Return a distinct count of cells (DISTINCTCOUNT()). For more information, see the DISTINCTCOUNT Function (DAX) Help topic.

Generate Cross Content. Statistical table functions to generate cross content (CROSSJOIN(), GENERATE(), GENERATEALL()). For more information, see the CROSSJOIN Function (DAX), GENERATE Function (DAX), and GENERATEALL Function (DAX) Help topics.

Rank. Rank a set of values (RANK.EQ(), RANKX()). For more information, see the RANK.EQ Function (DAX) and RANKX Function (DAX) Help topics.

Standard Deviation. These functions were added to calculate the standard deviation of the entire population or a sample (STDEV.S(), STDEV.P(), STDEVX.S(), STDEVX.P()). For more information, see the STDEV.S Function (DAX), STDEV.P Function (DAX), STDEVX.S Function (DAX), and STDEVX.P Function (DAX) Help topics.

Statistical Table Functions. These functions enable you to create aggregated table content (SUMMARIZE(), ROW()) or to temporarily add columns to existing tables (ADDCOLUMNS()), without having to create calculated columns. For more information, see the SUMMARIZE Function (DAX), ROW Function (DAX), and ADDCOLUMNS Function (DAX) Help topics.

TOPN. Select the top elements of a set (TOPN()). For more information, see the TOPN Function (DAX) Help topic.

Variance. Calculate the variance of the entire population or a sample (VAR.S(), VAR.P(), VARX.S(), VARX.P()). For more information, see the VAR.S Function (DAX), VAR.P Function (DAX), VARX.S Function (DAX), and VARX.P Function (DAX) Help topics.

Information Functions. For more information, see the Information Functions (DAX) Help topic.

LOOKUPVALUE. Informational search functions to look up a certain value from a table (LOOKUPVALUE()) or to assess whether a value or combination of values in a row exist in a table (CONTAINS()). For more information, see the LOOKUPVALUE Function (DAX) and CONTAINS Function (DAX) Help topics.

Parent-Child Relationships. Informational parent-child functions (PATH(), PATHCONTAINS(), PATHITEM(), PATHITEMREVERSE(), PATHLENGTH()). For more information, see the PATH Function (DAX), PATHCONTAINS Function (DAX), PATHITEM Function (DAX), PATHITEMREVERSE Function (DAX), and PATHLENGTH Function (DAX) Help topics.

SWITCH. Logical function to provide a multiple-choice selection of an option and action (SWITCH()). For more information, see the SWITCH Function (DAX) Help topic.

Filter Functions. For more information, see the Filter Functions (DAX) Help topic.

ALLSELECTED. Filter removal from columns and rows to have visual totals (ALLSELECTED()). For more information, see the ALLSELECTED Function (DAX) Help topic.

Context-Examination Functions. Filter context-examination functions (ISCROSSFILTERED(), ISFILTERED(), HASONEVALUE(), HASONEFILTER(), FILTERS()). For more information, see the ISCROSSFILTERED Function (DAX), ISFILTERED Function (DAX), HASONEVALUE Function (DAX), HASONEFILTER Function (DAX), and FILTERS Function (DAX) Help topics.

HASONEVALUE. Tells you what columns contain only one distinct value (HASONEVALUE()). For more information, see the HASONEVALUE Function (DAX) Help topic.

CURRENCY. Currency-casting math function (CURRENCY()). For more information, see the CURRENCY Function (DAX) Help topic.

Change Data Types. You can change the data type for a calculated column in the same way you can change the data type for all non-calculated columns. For more information, see “Changing the Data Type” in the Calculated Columns Help topic.

Number Formats for Measures. You can set the type of number format (such as Currency), enter how many decimal places you want to display, select a symbol you want to display with the numbers, and use a digit-grouping symbol (such as a comma) to separate the thousand place. For more information, see “Formatting Options” in the Measure Settings Dialog Box (Custom Aggregation) Help topic.

Formatting Persistence. Now when you apply formatting to columns in the modeling environment, the formatting persists as you add fields to value areas of a PivotTable.

Field List: Descriptions. Add descriptions to tables, measures, and key performance indicators (KPIs). When the user hovers over those tables, measures, and KPIs in the Field List, tooltips appear that feature your descriptions of the context of each field. For more information about the Field List, see the PowerPivot Field List Help topic.

Field List: Display Order of Tables and Fields. The Field List is now sorted alphabetically. For more information about the Field List, see the PowerPivot Field List Help topic.

Categories: Denali, Excel, PowerPivot Tags: , ,

Using MDX for ranking in a PowerPivot solution

June 29th, 2011 admin No comments

L.S.

I found this great article about using a combination of MDX and DAX in a pivottable based on a PowerPivot model. It explains a real world Ranking problem using this approach. It is definitely worth reading…

PowerPivot is ‘Rank’

enjoy