+ Reply to Thread
Results 1 to 3 of 3

Macro won't stop recreating zipped file

Hybrid View

mduff3 Macro won't stop recreating... 01-28-2014, 10:28 PM
cytop Re: Macro won't stop... 01-29-2014, 02:35 AM
Fotis1991 Re: Macro won't stop... 01-29-2014, 02:40 AM
  1. #1
    Registered User
    Join Date
    01-28-2014
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2010
    Posts
    6

    Exclamation Macro won't stop recreating zipped file

    I have a code that creates a zipped file that saves to the desktop. The macro works fine except it repeats itself and doesn't stop. How can I change the following code so that it only creates one zipped file?

    Sub WhichButton()
    
    Dim strDate As String, SavePath As String, sFName As String
    Dim oApp As Object, iCtr As Long, I As Integer
    Dim vArr, FileNameZip
    Dim FName() As Variant
    
             ' Assign the calling object to a variable.
    ButtonName = Application.Caller
    RowCount = Cells(Cells.Rows.Count, "c").End(xlUp).Row       ' Value being searched is in column c
    
    For I = 2 To RowCount + 1
        Select Case ButtonName          ' Display the name of the button that was clicked.
            Case Range("B" & I)
    
                SavePath = "C:\Users\MDuff3\Desktop\" 'save zip location
                strDate = Format(Now, " dd-mmm-yy h-mm-ss")
                FileNameZip = SavePath & ButtonName & strDate & ".zip"
    
                FName = Array("Y:\Administration\Personnel\Certifications And Identification\CSTP\" & ButtonName & "_CSTP.pdf\")
    
                    If IsArray(FName) = False Then
                            'do nothing
                    Else
                            'Create empty Zip File
                        NewZip (FileNameZip)
                        Set oApp = CreateObject("Shell.Application")
                        I = 0
                        For iCtr = LBound(FName) To UBound(FName)
                        vArr = Split97(FName(iCtr), "\") 'splits raw directory into array at each "/"
                        sFName = vArr(UBound(vArr)) 'picks final part of array which is the filename
                    If bIsBookOpen(sFName) Then
                    MsgBox "You can't zip a file that is open!" & vbLf & _
                           "Please close it and try again: " & FName(iCtr)
                    Else
                    'Copy the file to the compressed folder
                    I = I + 1
                    oApp.Namespace(FileNameZip).CopyHere FName(iCtr)
                    'Keep script waiting until Compressing is done
                    On Error Resume Next
                    Do Until oApp.Namespace(FileNameZip).items.Count = I
                        Application.Wait (Now + TimeValue("0:00:01"))
                    Loop
                    On Error GoTo 0
                End If
            Next iCtr
            MsgBox "You find the zipfile here: " & FileNameZip
        End If
    
             End Select
    Next
          End Sub
    
    Sub NewZip(sPath)
    'Create empty Zip File
        If Len(Dir(sPath)) > 0 Then Kill sPath 'If the zip file name already exists
        
        Open sPath For Output As #1
        Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
        Close #1
    End Sub
    
    Function bIsBookOpen(ByRef szBookName As String) As Boolean
    ' Rob Bovey
        On Error Resume Next
        bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
    End Function
    
    Function Split97(sStr As Variant, sdelim As String) As Variant
    'Tom Ogilvy
        Split97 = Evaluate("{""" & _
                           Application.Substitute(sStr, sdelim, """,""") & """}")
    End Function

  2. #2
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: Macro won't stop recreating zipped file

    Also posted: www.ozgrid.com/forum/showthread.php?t=185726

  3. #3
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Macro won't stop recreating zipped file

    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Hypelink not working with zipped file
    By catchnanan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-17-2013, 06:49 PM
  2. Opening one .dat file from each of several zipped files
    By JP Romano in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-21-2010, 03:29 PM
  3. [SOLVED] Excel '00, 1st file has macros, how to stop macro in 2nd file?
    By kaptandrews in forum Excel General
    Replies: 0
    Last Post: 05-24-2006, 10:15 AM
  4. Replies: 0
    Last Post: 11-16-2005, 01:52 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1