Zip Files using VBA

VBA Code to zip files:

Private Sub ZipFile_FX(ZipFileName As String, fileToBeZipped As String)
Const ZIPEXELOCATION = "c:\program files\winzip\winzip32.exe"
Shell ZIPEXELOCATION & " -a " & Chr(34) & ZipFileName & Chr(34) & _
" " & Chr(34) & fileToBeZipped & Chr(34), vbNormalFocus
End Sub

sub zips()
Call ZipFile_FX("c:\b.zip", "c:\b.xls")
end sub

Zip files in a folder

VBA code to zip files in any folder:


Sub Zip_File_Or_Files()
    Dim FSO As New FileSystemObject
    Dim Fld As Folder, Fle As File
    Dim strDate As String, DefPath As String
    Dim oApp As Object
    Dim FileNameZip

    Sheets("Sheet1").Select
    LR = Range("B2").End(xlDown).Row
    For LV = 3 To LR
        DefPath = Range("B3").Value
        FileNameZip = DefPath & "\TestFile" & ".zip"
        NewZip (FileNameZip)
        Set Fld = FSO.GetFolder(DefPath)
        For Each Fle In Fld.Files
            If InStr(1, Fle.Type, "Excel") <> 0 Then
                Set oApp = CreateObject("Shell.Application")
                oApp.Namespace(FileNameZip).CopyHere Fle.Path
                 'Keep script waiting until Compressing is done
                On Error Resume Next
                Application.Wait (Now + TimeValue("0:00:01"))
                On Error GoTo 0
            End If
        Next Fle
    Next LV
End Sub

Sub NewZip(sPath) 'Create empty Zip File
    If Len(Dir(sPath)) > 0 Then Kill sPath
    Open sPath For Output As #1
    Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
    Close #1
End Sub

List of all Worksheet Names

VBA macro to list down all worksheet names in a workbook:

Sub SheetNames() 

    Columns(1).Insert 

    For i = 1 To Sheets.Count 

        Cells(i, 1) = Sheets(i).Name 

    Next i 

End Sub 

Collapsing and expanding of columns

We work on Pivot Tables where we collapse and expand columns. You might come across with situation where you have two pivot tables. What you want to do is when you expand one same column in other pivot table should also expand.

Below code can be used to do that. Put the following code in a module:


Sub LinkPivotTables_ByFieldItemName_ToShowDetail(pt As PivotTable)  'takes as argument - pt As PivotTable

Dim wkb As Workbook
Set wkb = ThisWorkbook

Dim wks As Worksheet
Set wks = wkb.Sheets(2) 'Mention the sheet name where Pivots are stored

Dim PivotTableIndex As Integer
Dim PivotItemIndex As Integer
Dim PivotFieldIndex As String
Dim BoolValue As Boolean
Dim ItemName As String

Application.ScreenUpdating = False
Application.EnableEvents = False

PivotFieldIndex = "VP" 'Mention column name which you want to collapse/expand

On Error Resume Next


        For PivotItemsIndex = 1 To pt.PivotFields(PivotFieldIndex).PivotItems.Count

        BoolValue = pt.PivotFields(PivotFieldIndex).PivotItems(PivotItemsIndex).ShowDetail
        ItemName = pt.PivotFields(PivotFieldIndex).PivotItems(PivotItemsIndex).Name

            For PivotTableIndex = 1 To wks.PivotTables.Count

                ' This If statement will dramatically increase efficiency - because it takes a long long time to set the value but it doesn't take long to check it.
                If wks.PivotTables(PivotTableIndex).PivotFields(PivotFieldIndex).PivotItems(PivotItemsIndex).ShowDetail <> BoolValue Then
                    wks.PivotTables(PivotTableIndex).PivotFields(PivotFieldIndex).PivotItems(PivotItemsIndex).ShowDetail = BoolValue
                End If

            Next PivotTableIndex

        Next PivotItemsIndex

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Put the below code in the active sheet where the pivots are placed:


Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Call LinkPivotTables_ByFieldItemName_ToShowDetail(Target)

Call LinkPivotTables_ByFieldItemName_ToShowDetail_Executive_Manager(Target)

Call LinkPivotTables_ByFieldItemName_ToShowDetail_Regional_Manager(Target)

End Sub

Using Tables – Formulas and Pivot Table Summaries

Use the Subtotal() formula to get totals for columns

•Now, just above the table you can add a Subtotal formula for the value in the example to the right
•Type or Select Subtotal(function_num,ref1…)
•function_num will be 9 for Sum
•Press “,” or keyboard or select Ref1 box in the formula editor
•Now select the column of the Table that you want to Subtotal (there should be a small down arrow at the top of this field)

-------------------------------
Adding a formula in a Table:
•If you have a table with a bunch of Data and want to add a field for Quarter so that we can summarize by quarter the total $.
•Right click the table > Insert > Table Columns to the Left

•Using the Quarter formula you can select the date in the same row and hit Enter on your keyboard
•The formulas will auto fill to the bottom of the table

-------------------------------
Adding a Pivot Table:
•Select any 1 cell inside the Table
•Insert Ribbon > Pivot Table button
•You will see that the Range of the Pivot Table is the Name of your Table.  By using your Table, if you add any new Fields or Rows to your Table you can hit •Refresh on the Pivot table without having to change the Range if you used a fixed range.
•You now have a Blank Pivot Table
•Field List (right Picture)

-------------------------------

Customizing your Pivot
•Choose Fields to add to report – List of fields available to be added to the Pivot table
•Report Filter – Filters  to be applied to the total table
•Column Labels – Fields that you will see in the columns (generally I use for Quarters or categories
•Row Labels – Fields that you will see in the columns (generally I use for Locations or People)
•Values – fields that will have some math applied to them (Sum of, Count of, Average of, etc)

-------------------------------

Value Field Settings
•You can summarize Values in a number of different ways, Sum, Count, Average, Max, Min, etc.
•If the Field you placed into Values section of the Pivot table is a number then you can use any option here.  You can place a text type field into Values as well, but will need to use Count
•The Show Value As tab has more advanced options.  Some very difficult to understand.  We will look at % of Column Total and % of Row Total as two of the more basic options.

-------------------------------

Calculated Fields
•Calculated Fields are fields that go into the Values section of the Pivot Table
•Calculated Item are fields that can be added to the Filters, Row Labels, Column Labels
•Add Calculated Field (Right Bottom).  With Pivot table selected, PivotTable Tools Ribbon > Options Ribbon > Fields, Items & Sets Button

-------------------------------

Make your PivotTable Pretty
•In the PivotTable Tools Ribbon > Design Ribbon you will see a Layout Section and a Styles Section.  New Color Style (right)
•Maybe you like a Tabular Report Layout button (below)

-------------------------------

What if you wanted to turn a Pivot Table into a Table but there are a lot of Blanks in the Sub-Region and Quarter columns

If you have Excel 2010 you can do this through PivotTable Tools Ribbon > Design Ribbon > Report Layout Button > Repeat All Item Label Button
•To do it otherwise you can copy the PivotTable and paste the value into a new sheet or empty cells
•Highlight the Sub-Region and Quarter columns and rows with (don’t just highlight Column A:B)
•Press Ctrl+G on your keyboard
•Press Alt+S or click the Special Button
•Press K or click on the Blanks bullet
•Press Enter or click Ok button

•You will see that only the blank cells are now selected

•Press = sign and the UP arrow on your keyboard

•Press Ctrl+Enter on your Keyboard.  This copies the formula to all the blank cells in the range.

•No copy/paste special values and you filled in all the blanks successfully

Insert a Table vs. Range of data

It is best to use a table for several reasons

•Easier to reference

•Easy to add to without having to adjust formula ranges or pivot tables

•Easier to view data because of the auto alternating line colors

•Any formulas added will auto fill to the end of the column

To Convert a range to a Table

•Select Insert Ribbon

•Click the Table Button

•Select your Range if it didn’t do it automatically

Keyboard shortcuts

Sharing some keyboard shortcuts: 

1. Arrow Keys: Move one cell up, down, left, or right in a worksheet.
2. Page Down/Page Up: Move one screen down / one screen up in a worksheet.
3. Alt+Page Down/Alt+Page Up: Move one screen to the right / to the left in a worksheet.
4. Tab/Shift+Tab: Move one cell to the right / to the left in a worksheet.
5. Ctrl+Arrow Keys: Move to the edge of next data region (cells that contains data)
6. Home : Move to the beginning of a row in a worksheet.
7. Ctrl+Home: Move to the beginning of a worksheet.
8. Ctrl+End: Move to the last cell with content on a worksheet.
9. Ctrl+f: Display the Find and Replace dialog box (with Find selected).
10. Ctrl+h: Display the Find and Replace dialog box (with Replace selected).
11. Shift+F4: Repeat last find.
12. Ctrl+g (or f5): Display the 'Go To' dialog box.
13. Ctrl+Arrow Left/Ctrl+Arrow Right: Inside a cell: Move one word to the left / to the right.
14. Home/End: Inside a cell: Move to the beginning / to the end of a cell entry.
15. Alt+Arrow Down: Display the AutoComplete list e.g. in cell with dropdowns or autofilter.
16. End: Turn 'End' mode on. In End mode, press arrow keys to move to the next nonblank cell in the same column or row as the active cell. From here use arrow keys to move by blocks of data, home to move to last cell, or enter to move to the last cell to the right.

More to come ..... 

List files in a folder and sub-folders

Most of the people working in corporate environment deal with large amount data and are aware of the term 'Automation'. In the following discussion am sharing some automation tips and techniques which can be used in day to day work.

People working  on VBA coding in Excel will find this very useful and easy to understand. Below code can be used to list files in a folder and sub-folders.

Dim iRow

Sub ListFiles()
    iRow = 11
    Call ListMyFiles(Range("C7"), Range("C8"))
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)
    On Error Resume Next
    For Each myFile In mySource.Files
        iCol = 2
        Cells(iRow, iCol).Value = myFile.Path
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Name
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Size
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.DateLastModified
        iRow = iRow + 1
    Next
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
        Next
    End If
End Sub

Importing data into Microsoft Excel using web query

MS Excel provides the facility to import data from excel sources. One way is by using Web Query. It is a very useful feature that Excel has.

To create the Web Query:

1. Select the first cell in which you want results to appear.
2. Choose Data | Import External Data | New Web Query to open the dialog box shown in image below.
3. Enter the URL in Address box and click Go.

Select the table you want to use by using the check mark and click Import button.

It will show the numbers in excel spreadsheet.

Delete number and retain formulas

I have to delete all the number and retain all the formulas in an excel sheet. How to do it?

A. Just follow the below steps:

Excel 2007 version
1. Select the data range; or Press Ctrl+A to select the entire sheet
2. Home tab >> Editing section >> Find & Select >> Constants >> Choose the constants you want to select >> example Numbers, Text etc.
3. Click Ok
4. Press Delete (to remove the selected constants)

Filling blank cells

I want to fill the blank values with the cell value in the above cell, eg. A1 contains IBM, A12 contains HP, A24 contains Cisco etc. So, I want to fill cell values A2:A11 with IBM, A13:A23 with HP etc. This will help me create a flat file from a locked pivot shared by the syndicate research provider. Is there a way to do it without using macros?

A. Yes, it can be done even without using macros. Just follow the below steps:

Select the data range (ex: A1:A24)
Home Tab >> Editing section >> Find & Select >> Go To Special
Choose “Blanks”
Click Ok
Now, the selected cell will be the first blank cell (in this example: A2)
Type “=A1” (i.e. we are linking cell A2 with value in cell A1)
Press Ctrl+ Enter