Unzip and renamed compressed csv

[jamsta]

Hi all,

I would like some code that extracts a file from a zip folder, renames and saves to a specified location. I've tried the code posted by Darth Vodka http://www.access-programmers.co.uk/...d.php?t=144356 but it gets stuck at the point of checking for the existence of Winzip.

Any fresh thoughts on this?

thanks

 

[stopher]

What do you mean by "it gets stuck"? Maybe you could post your code and indicate which line is creating an error and what the error message is.

I assume you have winzip installed on your computer and that you have set the path in the code accordingly?

Chris

 

[jamsta]

Sorry Stopher I'll be more specific. Yes I have winzip installed but it's not in the default location in Darth Vodka's code:

If Dir(PATHWINZIP & "winzip32.exe") = "" Then         
MsgBox "Please find your copy of winzip32.exe and try again"         
Exit Sub     
End If

so I get the error message. I suppose what I really want is a code way of finding where Winzip is. I want to do that rather than 'hard' code it, as this mechanism may be run on a number of different PCs (or a thin client set-up), with Winzip installed in various ways.

 

[DJkarl]

I found this years ago, it will return the associated Executable with the file type, it won't work if there is no association though. Pass it the name of your zip file, and the path to that files location.

Option Compare Database
Private Const cMAX_PATH = 260
Private Const ERROR_NOASSOC = 31
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&
Private Const ERROR_OUT_OF_MEM = 0
Private Declare Function apiFindExecutable Lib "shell32.dll" _
    Alias "FindExecutableA" _
    (ByVal lpFile As String, _
    ByVal lpDirectory As String, _
    ByVal lpResult As String) _
    As Long
    
Function fFindEXE(stFile As String, _
                    stDir As String) _
                    As String
'Usage Example:
'   ?fFindEXE("test.zip","c:\temp")
'
Dim lpResult As String
Dim lngRet As Long
    lpResult = Space(cMAX_PATH)
    lngRet = apiFindExecutable(stFile, stDir, lpResult)
    
    If lngRet > 32 Then
        fFindEXE = lpResult
    Else
        Select Case lngRet:
            Case ERROR_NOASSOC: fFindEXE = "Error: No Association"
            Case ERROR_FILE_NOT_FOUND: fFindEXE = "Error: File Not Found"
            Case ERROR_PATH_NOT_FOUND: fFindEXE = "Error: Path Not Found"
            Case ERROR_BAD_FORMAT:  fFindEXE = "Error: Bad File Format"
            Case ERROR_OUT_OF_MEM:  fFindEXE = "Error: Out of Memory"
        End Select
    End If
End Function

 

[jamsta]

Thanks DJ,

I gave this a go. Zip folders are associated with Windows Explorer on my PC. So when I put fFindExe back into the UnZip_ZipFile sub, it just opens the window.

Now if you open a zip folder you can copy the contents and paste elsewhere, and that extracts the files. So I also tried just copying and pasting in code, as if the folder PBL.zip was just a normal folder. The folder location is not recognised when I try that though. But maybe that's a route to explore?

 

[DJkarl]

There is another route to think about, since Windows can now unzip and zip files without the need for a third party app you might look into using the native functionality. I wrote this some time ago as a previous company I worked for forbid us to have Winzip installed due to licensing concerns. This is to Unzip a file, I use a similar method to zip files using Windows only. You need to reference the Windows Shell Controls and Automation library, or change the code to a late binding model.

Const DecompressZIP As Long = &H214
 
Public Function UnZipFile(ByVal ZipFile As String, ByVal DestFolder As String, _
Optional AllFiles As Boolean = False, Optional fName As String = "") As Boolean
Dim fld As Shell32.Folder
Dim fld2 As Shell32.Folder
Dim objShell As Shell32.Shell
Dim oItems As Shell32.FolderItems
Dim oItem As Shell32.FolderItem
 
If Left(DestFolder, 4) = ".zip" Then
    GoTo EXITME
End If
Set objShell = New Shell32.Shell
Set fld = objShell.NameSpace(ZipFile)
Set fld2 = objShell.NameSpace(DestFolder)
If AllFiles Then
    Set oItems = fld.Items
    fld2.CopyHere oItems, DecompressZIP
Else
    If fName = "" Then 'If filename is blank and AllFiles not selected don't know what to extract
        GoTo EXITME
    End If
    
    For Each oItem In fld.Items
        If oItem.name Like fName Then
            fld2.CopyHere oItem, DecompressZIP
        End If
    Next oItem
    
End If
UnZipFile = True
EXITME:
Set oItem = Nothing
Set oItems = Nothing
Set fld = Nothing
Set fld2 = Nothing
Set objShell = Nothing
End Function

 

[jamsta]

DJKarl,

Spot on - I've got this working perfectly! Thanks very much for your help, you've helped make my life that little bit easier.

 

[LadyDi]

I would like to include this module in my database as well however, I have a couple questions. What needs to be filled in for the variable ZipFile? I'm assuming the variable DestFolder is the path to the zip file, or is it where the file is to be sent? I am also assuming that the variable fName is the file name, if only one file is to be unzipped. Is that correct?

I added the Windows Shell Control and Automation as a reference. When I try to run the code, I receive an error message stating "Object Variable or With Block Variable not set". When I click on Debug, it highlights the line of code that reads "Set oItems = fld.Items". How can I fix this?

 

[DJkarl]

ZipFile is the complete path and name to your zipped archive i.e. C:\Temp\MyZip.zip, DestFolder is the path of the folder where you want the contents of the ZipFile place, i.e. C:\Temp\UnzippedFiles\ fName is an optional argument, if you provide that it will only extract a file with that name from the archive, otherwise specify the AllFiles arguement as True and all the files in the archive will be unzipped.

 

[LadyDi]

Is there a way to loop this code, until all zip files in a given folder are unzipped to a new folder?

ZipFile = C:\Log Parser
DestFolder = C:\DA Data

 

[DJkarl]

Well I would suspect you could put a Do...Loop using something like a filescriptingobject or the Dir command on the folder in question, get the Zip file name, then call the unzip function inside that loop for each file that's found.

 

[LadyDi]

I'm afraid, I'm not familiar with the filescriptingobject or Dir commands. Could you give me an example of how the loop should look?

 

[boblarson]

Maybe this will help:

http://www.allenbrowne.com/ser-59.html

 

[LadyDi]

That works great. Thank you for the information.

I'm a little unsure how to get this loop to work.

Also, I was wondering if it is possible for the ListFiles function to pass the list of file names to the UnzipFile function until the loop is finished?

This is what I have tried:

Do
UnZipFile (ListFiles("C:\Report_Data\Log Parser 6-25", "*.zip")), "C:\Report_Data\DA Data", True
Exit Do
Loop Until ListFiles("C:\Report_Data\Log Parser 6-25", "*.zip") Is Null

However, this is not working. When I try it, I get an error message from the UnZipFile function that reads "Object Variable or with block variable not set". When I click on debug, it highlights the line of code that reads "Set oItems = fld.Items". What do I need to do to get this to work?

 

[DJkarl]

As it stands the UnZipFile function takes a single file name as an argument, you would need to modify the function significantly to make it take an array or collection of file names. You would typically get an error like that if the datatype is invalid, in this case I suspect either the Zip file name is bad, there are no files in the zip file, or the file was compressed using WinZip and something other than normal compression.

 

[LadyDi]

Would it be possible to pass the file name to the UnZipFile function from a table within the database?

In other words, if I can get the ListFiles function to update a table in the database and then add some kind of column in the table to increment (i.e. an autonumber). Could the loop go through that table and pass a file name to the UnZipFile function for each record in the table, one at a time? There will always be about 52 files that need unzipped. So I would need it to start on record 1 and loop until it got to record 52.

Could the file name in the UnZipFile function read something like [Tables]![TempTable]![FileName]?

 

[DJkarl]

Sure, you could probably just open up a recordset and loop through that then.

Dim rs as DAO.Recordset
Set rs = Currentdb.OpenRecordset("TableName")
Do until rs.eof
UnZipFile(rs("FileName"))
rs.movenext
loop

set rs = nothing

 

[LadyDi]

That is perfect. Thank you so much!

I just have one more question. I am going to use the UnZipFiles function first with a main file and then run it again using the loop you provided in the previous post. For some reason the people providing the raw data like to send zip files within zip files. On the first zip file, the name will change every day, because the file name contains today's date. Is there a way for this function to either automatically update the month and day for today's date or ask the user for the month and day and then use that in the file name? What I tried was something like this "C:\Data\Log " &Date()&".zip". However, that didn't work. Is this possible?

 

[boblarson]

What I tried was something like this "C:\Data\Log " &Date()&".zip". However, that didn't work. Is this possible?

You need to format the date to be Windows compliant because it doesn't like the / separators.

"C:\Data\Log " & FormatDate(Date(), "yyyymmdd") & ".zip"

 

[LadyDi]

Is there a way to only get the month and day from this? The file names I am getting do not include the year (they can't make anything too easy). The file names will always look something like this "Log Parser 6-26.zip". My goal is to get this automated to the point, that the everyday users will not have to go into the code and update the month and day manually. If it would be easier, I could add a text field to my form for the user to enter the month and day for today. However, I'm not sure how to get that to transfer through the function either. What do you suggest?