Solved: Is it possible for excel to execute a command like add files to a zip archive?

[Idiot]

As per my usual, I have no idea where to begin. What I need to do (when using the attached dummy files) is to provide an option that will zip multiple files after filtering the results within excel. As you can see from the attached xlsm, the names have links to word documents. Those word documents are the files that need to be zipped up. The whole process should be:
1. Filter my results given the criteria that I choose.
2. Make a decision as to whether or not I need to zip the files up, if I do, I click a macro button that takes only the filtered results and zips up the files that are only associated with those results.

OSs it will be used on:
-Mostly Windows XP
-Windows Vista 32 and 64 on the rare occasions.

How to use current filter with attached file:
-Simply Click the filter button that's located to the right of the data (around column H)
-Select a criteria and click "Add to Criteria". For simplicity sake, keep the criteria simple, there are only 7 names. So basically just filter ASA or something :) The real file has a LOT of rows of data.
-Finally click the Filter button that's below the Add to Criteria button

Attachment includes:
-A zip file that includes:
--A directory folder that has blank word documents that represent resumes and cover letters.
--An excel macro worksheet that has data including fake names that link to the files in the directory folder.

If you have any questions I'll responded pretty quickly.

One last final question:
Are there any books that are meant for the dumb and busy people like me? I want to learn, but my focus level is pretty low due to a lot of distractions and family drama. I swear they have a sixth sense in just KNOWING when I'm trying to learn ANYTHING.


I PRAISE THESE FORUMS AND ALL WHO CONTRIBUTE
sigh, might be years before I can contribute to these forums :(

[Imdabaum]

Yes it is possible to zip files from VBA. I haven't done it in a few years, but I'll try and figure it out. I've never zipped multiple files though.

[Bob Phillips]

See http://www.rondebruin.nl/zip.htm

[Idiot]

Yes it is possible to zip files from VBA. I haven't done it in a few years, but I'll try and figure it out. I've never zipped multiple files though.

Thank you, I appreciate it. I'm just always having trouble understanding it all and remembering.

[Idiot]

See http://www.rondebruin.nl/zip.htm

*Brain explodes* I don't know how to make it do what I need to do :(

I appreciate the insight though - I'm just... really really slow.

[Idiot]

Not trying to rush anybody, but am curious if anyone is closer. I just cant understand this :(

[Idiot]

A big Thank You to XLD for providing the link, but I could only go as far as doing the browse and zip example. Which, like i said, is incredibly helpful, but I don't know how to make it automatically zip up files that are linked in the excel file.

I've attached what I did, but it probably won't help. Sorry.

[Kenneth Hobs]

What is the folder name to save the zip file to? What is the filename to zip to? If it exists, should it be overwritten or prompt to overwrite?

You will probably want Windows to compress the file rather than 7Zip or WinZip. As such, parts of Ron's code would be used. http://www.rondebruin.nl/windowsxpzip.htm

The other part of the project is to get the filtered list and then zip.

You also need to detail what makes up the filename of the docx to zip. You have First and Last names but some docx files have 3 words in the filename. I am guessing that it is in the hypertext links but the last row of your first post shows two separate hyperlinks.

[Idiot]

What is the folder name to save the zip file to? What is the filename to zip to? If it exists, should it be overwritten or prompt to overwrite?

You will probably want Windows to compress the file rather than 7Zip or WinZip. As such, parts of Ron's code would be used. http://www.rondebruin.nl/windowsxpzip.htm

The other part of the project is to get the filtered list and then zip.

You also need to detail what makes up the filename of the docx to zip. You have First and Last names but some docx files have 3 words in the filename. I am guessing that it is in the hypertext links but the last row of your first post shows two separate hyperlinks.


Idealy, the user will choose the directory to save the zip file to. But if it's too problematic, saving to the desktop I think would be fine. The filename of the zip can be something simple like "FilteredResults.zip" but it might me smarter to have it like "FilteredResultsX.zip" where X is a number that increments per execution to avoid overwriting, but I suppose the best way is to allow the user to name it. (I'm just feeling guilty making anyone helping me do all this work).

I don't think compression is going to be necessary as the file sizes aren't that large. But the way i have it now is exactly the link you posted which is good. Just have no idea how to progress from there.

Unfortunately, since this excel sheet will be used just to filter out people's resume's, the resume's themselves don't have a standard naming convention. And you're exactly right, I'm hoping there will be away to get the file names through the hyperlinks. The reason why the first name and last name has separate hyperlinks is because the first name represents the link to the resume, and the last name represents the link to the cover letter. Some people don't have cover letters and thus don't have a link.

This must sound ridiculous, but this is how it was set up. If it's impossible to do it this way, I'll understand, but if there is a way, I'll buy you a beer....via virtual smilies. Or if you live in the Philadelphia area, then maybe for real :beerchug:

Any help is very very appreciated.

[Kenneth Hobs]

Since you will use XP, you need to zip with 7zip or winzip.

If it were just Vista or Win7, we could use the Windows method. I have it completed. When I get time, I will see if it works on Vista.

Once you decide how you will zip, we can work from there. It is possible to determine what file association ZIP files use.

If you don't care about file compression, another tool to splice/unsplice the files might suffice.

The code below is what I have for Vista or Win7. You will need to get my kb article's module to use the sequential file naming routine.

'http://www.vbaexpress.com/kb/getarticle.php?kb_id=1041
Sub ZipFilteredList()
Dim r As Range, h As Hyperlink
Dim folder
Dim oApp As Object
Dim zipFilename
Dim i As Long
Dim s As String

'Set the workspace object
Set oApp = CreateObject("Shell.Application")

'Find all visible cells in column A.
Set r = Worksheets("Foglio1").Range("A2", Range("A1048576").End(xlUp)).SpecialCells(xlCellTypeVisible)

'Exit if no data was filtered.
If r Is Nothing Then
MsgBox "No filtered data was found.", vbCritical, "Macro Ending"
GoTo TheEnd
End If

'Set an initial folder to contain zip files.
folder = ThisWorkbook.Path 'No trailing backslash.

'Let the user select a folder for zip files.
folder = GetFolder("Select a Folder for Zip Files", folder & "\")

'If no folder was chosen, use this workbook's path.
If folder = "" Then folder = ThisWorkbook.Path

'Add trailing backslash if needed.
If Right(folder, 1) = "\" Then folder = Left(folder, Len(folder) - 1)

'Set the next sequential zip filename.
zipFilename = MakeAnotherUnique(folder & "\" & "FilteredResults.zip")

'Trim zipFilename
zipFilename = Left(zipFilename, InStrRev(zipFilename, ".zip") + 3)

'Create an empty zip file.
NewZip (zipFilename)

'Add each address from visible column A cell with a hyperlink to the zip file.
For Each h In r.Hyperlinks
s = h.Address
If Left(s, 2) = ".." Then s = ThisWorkbook.Path & Right(s, Len(s) - 2)
'Replace "/" with "\"
s = Replace(s, "/", "\")
'Replace "%20" with " "
s = Replace(s, "%20", " ")
If Dir(s) <> "" Then
'Copy the file to the compressed folder
i = i + 1

oApp.Namespace(zipFilename).CopyHere s

'Keep script waiting until Compressing is done
On Error Resume Next
Do Until oApp.Namespace(zipFilename).Items.Count = i
Application.Wait (Now + TimeValue("0:00:01"))
Loop
On Error GoTo 0
End If
Next h

TheEnd:
Set oApp = Nothing
End Sub

Function GetFolder(Optional sTitle As String = "Select Folder", _
Optional sInitialFilename As String)
Dim myFolder As String
With Application.FileDialog(msoFileDialogFolderPicker)
If sInitialFilename = "" Then sInitialFilename = ThisWorkbook.Path

.InitialFileName = sInitialFilename
.Title = "Greetings"
If .Show = -1 Then
GetFolder = .SelectedItems(1)
If Right(GetFolder, 1) <> "\" Then
GetFolder = GetFolder & "\"
End If
Else: GetFolder = ""
End If
End With
End Function

[Idiot]

Thank you so much for all your help. I was so sure that XP had the ability to zip files through windows. The alternative is that I know everyone uses PKZip. If it's impossible to use this program with excel, I'll try to encourage them to use 7zip.

Seriously, thank you for helping me.

[Kenneth Hobs]

The code below that uses the Windows compression is similar to what I posted earlier. The main difference is in the use of the Constant for two lines. The sample file that you posted with my additions is attached. You will notice that I do not use your filter methods or userform. I just used a simple filter.

You had some other issues in the address for the hypertext addresses. I replaced some parts but other characters may need to be replaced.

You will need the GetFolder() routine and the module from my sequential naming kb article too. I also used the routine by Ron de Bruin to create the blank zip file.

Const FOF_NOCONFIRMATION = &H14

Sub ZipFilteredListByWindows()
Dim r As Range, h As Hyperlink
Dim folder
Dim oApp As Object
Dim zipFilename
Dim i As Long
Dim s As String

'Set the workspace object
Set oApp = CreateObject("Shell.Application")

'Find all visible cells in column A.
Set r = Worksheets("Foglio1").Range("A2", Range("A1048576").End(xlUp)).SpecialCells(xlCellTypeVisible)

'Exit if no data was filtered.
If r Is Nothing Then
MsgBox "No filtered data was found.", vbCritical, "Macro Ending"
GoTo TheEnd
End If

'Set an initial folder to contain zip files.
folder = ThisWorkbook.Path 'No trailing backslash.

'Let the user select a folder for zip files.
folder = GetFolder("Select a Folder for Zip Files", folder & "\")

'If no folder was chosen, use this workbook's path.
If folder = "" Then folder = ThisWorkbook.Path

'Add trailing backslash if needed.
If Right(folder, 1) = "\" Then folder = Left(folder, Len(folder) - 1)

'Set the next sequential zip filename.
zipFilename = MakeAnotherUnique(folder & "\" & "FilteredResults.zip")

'Trim zipFilename
zipFilename = Left(zipFilename, InStrRev(zipFilename, ".zip") + 3)

'Create an empty zip file.
NewZip (zipFilename)

'Add each address from visible column A cell with a hyperlink to the zip file.
For Each h In r.Hyperlinks
s = h.Address
If Left(s, 2) = ".." Then s = ThisWorkbook.Path & Right(s, Len(s) - 2)
'Replace "/" with "\"
s = Replace(s, "/", "\")
'Replace "%20" with " "
s = Replace(s, "%20", " ")

'Copy the file(s) to the compressed folder
If Dir(s) <> "" Then
i = i + 1

oApp.Namespace("" & zipFilename).CopyHere "" & s, FOF_NOCONFIRMATION

'Keep script waiting until Compressing is done
On Error Resume Next
Do Until oApp.Namespace(zipFilename).Items.Count = i
Application.Wait (Now + TimeValue("0:00:01"))
Loop
On Error GoTo 0
End If
Next h

'Show the zipfile if it was created.
If Dir(zipFilename) <> "" Then
MsgBox "You will find the zip file here: " & zipFilename
End If

TheEnd:
Set oApp = Nothing
End Sub

[Idiot]

THANK YOU SOOOO MUCH!!!! WHY ARE YOU SO AWESOME!

Works well with Vista, only thing I can't figure out is that it doesn't zip up the files that are linked in column B (the cover letters). I'll test out XP once i get that machine back later today.

Seriously, thank you so much. I'll be off studying your code in hopes I can solve the column B thing, but i suspect I won't figure out, BUT I SHALL TRY! :)

[Idiot]

I figured it out haha, Just a simple addition of:

Set r = Worksheets("Foglio1").Range("B2", Range("A1048576").End(xlUp)).SpecialCells(xlCellTypeVisible)

and it works great.

Still need to test XP though.

YAY I'M SO HAPPY.

[Kenneth Hobs]

Please try to quote only parts of previous posts as needed. Since the posts are threaded, we know what was said in the previous and past posts.

All you need to do is to decide what logic you want to apply. For the column B, simply change the A in the code that follows to B. Obviously, I don't know your logic for why you would want the address in B or A or both. The only hard part is knowing your logic parameters.

'Find all visible cells in column A.
Set r = Worksheets("Foglio1").Range("A2", Range("A1048576").End(xlUp)).SpecialCells(xlCellTypeVisible) For column B:
'Find all visible cells in column B.
Set r = Worksheets("Foglio1").Range("B2", Range("B1048576").End(xlUp)).SpecialCells(xlCellTypeVisible)
So, changing that line of code gives you the B column to iterate in the For Each loop. This is then an all Or solution. Either iterate all of column A as I posted or all of column B with the one line of code changed as above. An And solution requires another change or two.

You should probably add the 2nd line below to get the full set of filtered rows and skip row 1.
'Find all visible cells in column A and skip row 1.
Set r = Worksheets("Foglio1").Range("A1", Range("A" & Cells.Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
Set r = Intersect(Range("A2:A" & Rows.Count), r)

You will need to use the winzip method for xp. The main thing is to run as Ron de Bruin detailed. He shows how to make the process wait until it completes its task. Other than that, passing the command line parameters to winzip is the last part. He hard coded the path the the winzip32.exe file. Of course if winzip32.exe is associated to zip files, the path can be determined by a Windows API method.

If you have a significant solution, please mark this solved. You can still ask questions to refine a solution in the same thread.

[Idiot]

Ok, well, as I said before, Vista works perfectly.

Unfortunately, I'm unable to stray away from PKZip and completely unsure why this was the chosen application to use. In any case, I thought by changing:



shellStr = PathZipProgram & "winzip32.exe -min -a"

to



shellStr = PathZipProgram & "pkzipw.exe"


it would work, but sadly it had not. I even tried the pkzipc with again the same result. And I'm sorry to ask more of you, but what should I look for. I've been browsing the web for help and the product page advertises that it can zip files from excel, but provides no info on it besides clicking on "save copy as" through its menu system (which actually isn't existent, but could be the company computer).

If it's not possible, I'll understand, maybe I'll make some people upgrade. Sorry I'm so slow, but I didn't call myself "Idiot" for no reason ;)

[Kenneth Hobs]

pkzipw.exe is not one that Ron explained how to use. However, if it allowed command line parameters like winzip and 7zip, it could be used in a like manner. As I understand it, you have to add a package to allow command line parameters. You probably need to check into that first.

[Idiot]

I'll definitely look into it. And, I really appreciate all your help.

I also just want to make sure, this is really the only line I should have to modify:


shellStr = PathZipProgram & "winzip32.exe -min -a" _


right?


Thank you again and again!!

[Kenneth Hobs]

That line has a continue indicator but yes.

Try just sending a line like that in a Start > Run dialog or from the DOS prompt by Start > Run > cmd > Enter. You can also put the whole line in a BAT file to test. e.g.

c:\winzippath\winzip32.exe -min c:\test\test.xls .. where .. is other command line parameters. In some cases, you may need to enclose the strings in quotes.

Some programs that accept command line parameters allow a string value for a parameter that can handle many at once if delimited. e.g. "c:\test\ken1.xls,c:\test\ken2.xls,c:\test\tom*.xls". Noticed how I used "*" as a wildcard character. The syntax varies so check the program's help file.