Amending R DeBruin ZIP code

mohsan_akhtar

New Member
Joined
Jan 9, 2019
Messages
7
Hi all,

I have been looking at RDB ZIP code for a while now, and can get most of the code to work, except for the actual zipping of the existing files into the newly created ZIP folder.
After stepping through the code - it seems to me that - the code is not recognising the ne ZIP folder.

In terms of the code amendments, I have just added some "prints" to the immediate window, and trying to have the file path (origin and output) set from use defined in cell C3 as shown in the screenshots. From the immediate window, all looks good. I am assuming that the dimming as a variable (Filenamezip) is the bit that is falling over, and I just can not seem to see where from...fresh pair of eyes would be appreciated ;

Code:
 ''Browse to a folder and zip all files in it


Sub NewZip(sPath)
'Create empty Zip File
    If Len(Dir(sPath)) > 0 Then Kill sPath
    Open sPath For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
End Sub
Function bIsBookOpen(ByRef szBookName As String) As Boolean
    On Error Resume Next
    bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function
Function Split97(sStr As Variant, sdelim As String) As Variant
    Split97 = Evaluate("{""" & Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

Sub Zip_All_Files_in_Folder_Browse()
    
    Dim FileNameZip As Variant, FolderName, oFolder
    Dim strDate As String, DefPath As String, oPth As Variant
    Dim oApp As Object
    Dim ZIPwb As Workbook
    Dim Control As Worksheet
    Dim DefFPusr As Range, DefOutname As Range
    
    Set ZIPwb = ThisWorkbook
    Set Control = ZIPwb.Sheets("CONTROL")
    Set DefFPusr = Control.Range("C2")
    Set DefOutname = Control.Range("C3")

    DefPath = DefFPusr.Value
                                                                                                        Debug.Print "FROM EXCEL CELL C2: "; Tab(10); DefPath 'for testing only
    
    If Right(DefPath, 1) <> "" Then
        DefPath = DefPath & ""
    End If
                                                                                                        Debug.Print strindent; "FROM EXCEL - CHECKING '\' PRESENT: "; Tab(10); DefPath 'for testing only
        
    strDate = Format(Now, " ddmmyy")
    FileNameZip = DefOutname & strDate & ".zip"
                                                                                                        Debug.Print "FROM EXCEL CELL C3 FORMULA: "; Tab(10); FileNameZip 'for testing only
Set oApp = CreateObject("Shell.Application")
    'Browse to the folder - ************** amended to go to specicif folder
    
    oPth = DefPath
                                                                                                        Debug.Print "Zipping path (oPth)"; Tab(10); oPth 'for testing only
    
Set oFolder = oApp.BrowseForFolder(0, "Please choose a folder...", 512, oPth)
    
    If Not oFolder Is Nothing Then
        'Create empty Zip File
        NewZip (FileNameZip)
        'FolderName
        oPth = oFolder.Self.Path
        
        If Right(FolderName, 1) <> "" Then
            FolderName = FolderName & ""
        End If
    
        'Copy the files to the compressed folder
                                                                                                        Debug.Print "Namespace "; Tab(10); oPth & FileNameZip
'************* the code is breaking here - runtime error 91 - Object variable or with block not set **************************
                            oApp.Namespace(FileNameZip).CopyHere oApp.Namespace(oPth).Items
'************************************************************************************************************
        'Keep script waiting until Compressing is done
        On Error Resume Next
        
        Do Until oApp.Namespace(FileNameZip).Items.Count = oApp.Namespace(FolderName).Items.Count
            Application.Wait (Now + TimeValue("0:00:01"))
        Loop
        On Error GoTo 0

        MsgBox "You can find the zipfile here: " & FileNameZip, vbInformation, "ZIP Processing complete :)"

    End If
End Sub

immediate window results [all as expected];
FROM EXCEL CELL C2:
X:\VARNew\Mohsan\WIP\Misc Macro\ZIP Test
FROM EXCEL - CHECKING '\' PRESENT:
X:\VARNew\Mohsan\WIP\Misc Macro\ZIP Test\
FROM EXCEL CELL C3 FORMULA:
ZIP Test 090119.zip
Zipping path (oPth)
X:\VARNew\Mohsan\WIP\Misc Macro\ZIP Test\
Namespace
X:\VARNew\Mohsan\WIP\Misc Macro\ZIP TestZIP Test 090119.zip

as a side note, I will probably get rid of the browse file part, as FP supplied by user - keeping in for testing.

Excel in WS;
FP to ZIP
X:\VARNew\Mohsan\WIP\Misc Macro
Partial name to save as
Misc Macro

<tbody>
</tbody>

C3 contains formula to get the result =MID(C2,FIND("=",SUBSTITUTE(C2,"","=",LEN(C2)-LEN(SUBSTITUTE(C2,"",""))))+1,256)
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,201
Office Version
  1. 2010
Re: Amending RDBruin ZIP code

You might also try to contact Ron. I'm sure he will try to help
 

mohsan_akhtar

New Member
Joined
Jan 9, 2019
Messages
7
Re: Amending RDBruin ZIP code

Thanks arthurbr,

However, the contact option on his website clearly asks for mails RE improvements or incorrect coding...mine which is neither- seems to be self inflicted error somewhere. Just looking for a fresh pair of eyes to run through it ;)
 

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
1,092
Re: Amending RDBruin ZIP code

Try removing the brackets from
Code:
NewZip (FileNameZip)
to
Code:
NewZip FileNameZip
 

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
1,092
Re: Amending RDBruin ZIP code

Looking at
Code:
Do Until oApp.Namespace(FileNameZip).Items.Count = oApp.Namespace(FolderName).Items.Count

Does "FolderName" contain the path of the files to be zipped?
 

mohsan_akhtar

New Member
Joined
Jan 9, 2019
Messages
7
Re: Amending RDBruin ZIP code

HI JK,

Just got it working earlier this afternoon.
You are on the right path.

The issue I spotted I the end was with
Code:
oApp.Namespace(FolderName & FileNameZip).CopyHere oApp.Namespace(FolderName1)
compared to my original;
Code:
oApp.Namespace(FolderName).CopyHere oApp.Namespace(FolderName)

the "spath" for zipping wasn't finding the right location and was instead just creating to default mylibrary documents in the system.

Many thanks for reviewing though :)
 

mohsan_akhtar

New Member
Joined
Jan 9, 2019
Messages
7
Re: Amending RDBruin ZIP code

Just as an FYI to anyone looking to do something similar, my complete code [not perfect, but I am sure someone will point out imperfections - maybe]

Code:
''Browse to a folder and zip all files in it
Sub NewZip(sPath)
'Create empty Zip File
    If Len(Dir(sPath)) > 0 Then Kill sPath
    Open sPath For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
End Sub
Function bIsBookOpen(ByRef szBookName As String) As Boolean
    On Error Resume Next
    bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function
Function Split97(sStr As Variant, sdelim As String) As Variant
    Split97 = Evaluate("{""" & Application.Substitute(sStr, sdelim, """,""") & """}")
End Function
Sub Zip_All_Files_in_Folder_Browse()
                                                                                                        Application.ScreenUpdating = False
'****************** DECLARING VARIABLES AS KNOWN FROM START *****************************************
    Dim FileNameZip, FolderName, oFolder
        Dim strDate As Variant, DefPath, oPth, DefPth2
            Dim oApp As Object
                Dim ZIPwb As Workbook
                Dim Control As Worksheet
                    Dim DefFPusr As Range, DefOutname As Range, Hlink As Range, DefFPusr2 As Range
'****************************************************************************************************
'******************** CREATING NON-CHANGING LINKAGES AS KNOWN FROM START ****************************
                                            Set ZIPwb = ThisWorkbook
                                                Set Control = ZIPwb.Sheets("CONTROL")
                                                    Set DefFPusr = Control.Range("C2")
                                                        Set DefFPusr2 = Control.Range("C4")
                                                            Set DefOutname = Control.Range("C3")
                                                                Set Hlink = Control.Range("C5")
'****************************************************************************************************
                                                DefPath = DefFPusr
                                                    DefPth2 = DefFPusr2
                                                                                                        Debug.Print "FP from CELL C2: "; Tab(10); DefPath 'for testing only
            If Right(DefPath, 1) <> "" Then 'ensuring needed \ is present in FP given by user
                DefPath = DefPath & ""
            End If
            
                If Right(DefPth2, 1) <> "" Then 'ensuring needed \ is present in FP given by user
                    DefPth2 = DefPth2 & ""
                End If
                                                                                                        Debug.Print strindent; "CHECKING FP2 has '\' PRESENT: "; Tab(10); DefPth2 'for testing only
        
                                        strDate = Format(Now, " ddmmyy")
                                        FileNameZip = DefOutname.Text & strDate & ".zip"
                                                                                                        Debug.Print "Proposed 'ZIP' File Name: "; Tab(10); FileNameZip 'for testing only
                                        Set oApp = CreateObject("Shell.Application")
                                        FolderName = DefPth2
    '*************************** TESTING CHANGE TO DROP BACK A FILE!!!
                                                                                                        Debug.Print "Redefined ZIP FP (where ZIP folder to create)"; Tab(10); FolderName 'for testing only
'?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?
'not used- as given the code a specific location- can use this if you want to allow user to pick from pop up box
'Set oFolder = oApp.BrowseForFolder(0, "Please choose a folder...", 512, FolderName)
    'Browse to the folder - ************** amended to go to specicif folder ****** NOT USED, AS LOCATION AS PER "CONTROL SHEET"
'    If Not oFolder Is Nothing Then
'?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?|?
            
            'Create empty Zip File
            NewZip (FolderName & FileNameZip)
                                        'foldername1 - where we are zipping from
                                        foldername1 = DefPath
        
            If Right(foldername1, 1) <> "" Then
                foldername1 = foldername1 & ""
            End If
                                        'FolderName2 - where we are zipping to
                                        FolderName2 = DefFPusr2.Text
            
            If Right(FolderName2, 1) <> "" Then
                FolderName2 = FolderName2 & ""
            End If
                                                                                                    '******************for testing only****************************
                                                                                                        Debug.Print "FNZ "; Tab(10); FileNameZip
                                                                                                        Debug.Print "Namespace "; Tab(10); FolderName & FileNameZip
                                                                                                        Debug.Print "FN1 "; Tab(10); foldername1
                                                                                                        Debug.Print "FN2 "; Tab(10); FolderName2
                                                                                                    '***************************************************************
        'Copy the files to the compressed folder
         oApp.namespace(FolderName & FileNameZip).CopyHere oApp.namespace(foldername1).Items
            'Keep script waiting until Compressing is done
                                                                                                        On Error Resume Next
                Do Until oApp.namespace(FolderName & FileNameZip).Items.Count = oApp.namespace(foldername1).Items.Count
                    Application.Wait (Now + TimeValue("0:00:01"))
                Loop
                                                                                                        On Error GoTo 0
                    'needing to move the ZIP folder to agreed path now (tidy up)
                                        Set FSO = CreateObject("Scripting.FileSystemObject")
                                            Dim FromPath As String, ToPath As String
                                        FromPath = FolderName & FileNameZip
                                        ToPath = foldername1 & FileNameZip
                        'create a copy if ZIP in agreed path as above
                            FileCopy FromPath, ToPath
                                'Delete created ZIP from above path 1
                                    If Len(Dir$(FromPath)) > 0 Then
                                        Kill FromPath
                                    End If
                                        
                'this is to delete all original files from the destination folder of the ZIP file
                                        Set FSO2 = CreateObject("Scripting.FileSystemObject")
                
                If Right(foldername1, 1) = "" Then
                    ToPath = Left(ToPath, Len(ToPath) - 1)
                End If
                    If foldername1 = foldername1 & "\.zip" Then 'if ZIP folder, ignore
                        
                        Else 'delete all others
                            FSO2.deletefolder foldername1 & "\*.*", True
                    End If
                                        'create a hyperlink in the spreadsheet
                                        With Control
                                            Hlink.Hyperlinks.Add anchor:=Hlink, Address:=ToPath
                                        End With
'dump set variables
'***********************************************
    Set FSO = Nothing
        Set FSO2 = Nothing
            Set myfiles = Nothing
                Set myfolder = Nothing
                    Set ZIPwb = Nothing
                        Set Control = Nothing
                    Set DefFPusr = Nothing
                Set DefOutname = Nothing
            Set Hlink = Nothing
        Set oApp = Nothing
'************************************************
                                                                                                                Application.ScreenUpdating = True
                                MsgBox "Zipping has been complete, thanks for your patience!", vbInformation, "ZIP Processing complete :)"

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,594
Messages
5,625,696
Members
416,127
Latest member
MALEPINZON

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top