Excel

TheSmallman.com

an XL ideas Lab

Dashboards VBA
  • Home
  • Dashboards
    • Tips & Tricks
    • Charts
    • Modelling
    • Infographics
    • VBA
  • Shop Dashboards
    • Power Pivot a User Guide
    • Excel Dashboard Course
    • Advanced Dashboard Course
    • Financial Modelling Course
    • Excel VBA Course
  • Blog
  • About
Menu

Excel Dashboards VBA

Street Address
City, State, Zip
Phone Number
an XL ideas Lab

Your Custom Text Here

Excel Dashboards VBA

  • Home
  • Dashboards
  • Excel Tips
    • Tips & Tricks
    • Charts
    • Modelling
    • Infographics
    • VBA
  • Shop Dashboards
  • PowerPivot
    • Power Pivot a User Guide
  • Courses
    • Excel Dashboard Course
    • Advanced Dashboard Course
    • Financial Modelling Course
    • Excel VBA Course
  • Blog
  • About

Excel VBA Send Files to Zip Drive

June 1, 2016 Marcus Small

Zipping up Excel files on the fly can be a most useful activity especially if working with outlook.  You may wish to generate a set of files with Excel VBA then zip those files and send them on to a list of people for review or as part of a monthly reporting procedure.  I have seen plenty of these type of procedures.  The most famous of which is on Ron De Bruin's site.

Ron De Bruin Zipping

The idea behind the concept is to have a file path with files inside it.  The zip procedure runs and sends all of the files to a compressed zip file and saves the file in a designated folder.

 The source is the location of the files and the Destination is where you want the Zip file saved to.  Note to the wize - these can not be the same folder.

This is my attempt at the same procedure.  Using my knowledge of VBA I think I can simplify the procedure quite a bit.

Option Explicit

Sub CreateZip()
Dim FileNameZip
Dim FolderName
Dim strDate As String
Dim oApp As Object

   FolderName = [C10] 'Files to ZIP
   strDate = Format(Now, " dd-mmm-yy h-mm-ss")
   FileNameZip = [C11] & "MyFilesZip " & strDate & ".zip"

   NewZip (FileNameZip) 'New Zip File
   Set oApp = CreateObject("Shell.Application")
   oApp.Namespace(FileNameZip).CopyHere oApp.Namespace(FolderName).items 'Copy

   On Error Resume Next 'Pause till Compress is done
   Do Until oApp.Namespace(FileNameZip).items.Count = oApp.Namespace(FolderName).items.Count
      Application.Wait (Now + TimeValue("0:00:01"))
   Loop
   On Error GoTo 0
End Sub


'Second procedure - this ia required.
Sub NewZip(sPath)
   Open sPath For Output As #1
   Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
   Close #1
End Sub

The following Excel file runs the zip procedure.  The VBA runs very smoothly and very quickly.  I was quite pleased with the final outcome.

ExcelZip.xls

Tags Excel, VBA, Create, ZIP, Files, Folder
← Excel Copying Variable Columns with ArrayExcel Create Child Sheets From Master →

Featured Posts

Excel Dashboards: Tracking a Crisis

Excel Dashboards: Tracking a Crisis
April 14, 2020

Recent Posts

Populating an Excel Table from a Range of Cells with VBA

Populating an Excel Table from a Range of Cells with VBA June 12, 2025

Fuzzy Distribution with Randbetween

Fuzzy Distribution with Randbetween May 21, 2025

Add Minimum and Maximum for Chart in Cells

Add Minimum and Maximum for Chart in Cells March 12, 2025

Inflation Over Multiple Years in a Single Cell

Inflation Over Multiple Years in a Single Cell January 10, 2025

Hubspot Dashboard

Hubspot Dashboard October 3, 2024

Monthly Dashboard With Supporting Metrics

Monthly Dashboard With Supporting Metrics September 25, 2024

Excel Show Missing Sheet Tabs

Excel Show Missing Sheet Tabs July 29, 2024

Run Macro Overnight Automatically

Run Macro Overnight Automatically June 24, 2024

Split File into Parts and Save to Directory

Split File into Parts and Save to Directory April 20, 2024

Most Popular Author

Most Popular Author December 14, 2023

 

Follow US:

 
 

MarcusSmall@thesmallman.com

 

TheSmallman.com - Making your small systems hum...
© Copyright 2013-2024 theSmallman.com All Rights Reserved.