Extract a gzip encoded file in code using VBA in MS Access

I am using MS Access 2013 with Windows 8.1.  I am looking for a way to extract a qzip encoded file in VBA rather than "shelling out" to an external program. A little background:  I am receiving and importing an XML file that contains a FedEx/UPS shipping label in .zpl format for a Zebra Printer.  The xml file contains the contents for the label in a Base64-encoded string that is then "gzipped".

I am able to successfully read in the xml file and extract the label contents value.  Then I decode the string, so far so good. Now I need to extract the gzip decoded string. I am able to to do this manually using an external program and it works; the label prints fine. I am hoping to avoid having to shell to an external program.

I have heard of a free .dll called "zlib.dll" that I could create a reference to and call the extract routine in code, but I am having problems find the actual file (all links are dead) and then finding any documentation for actual procedure calls.

Any help would be greatly appreciated.

Thank you,

Ira
Ira SaundersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
You should be able to use 7-zip for this:

Getting the 7Z zip dll to work with MS Access
Daniel PineaultPresident / Owner CARDA Consultants Inc.Commented:
This can easily be done using various tools.  I line 7-Zip's command line executable.  I wrote an article on the subject with sample code, see: http://www.devhut.net/2017/04/06/vba-zip-unzip-compress-extract/
Ira SaundersAuthor Commented:
Will this work on a Gzipped file?  Is there anyway to do it all in VBA without Shelling out?
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Gustav BrockCIOCommented:
Seems like you didn't follow my link above to VzLib
Daniel PineaultPresident / Owner CARDA Consultants Inc.Commented:
It worked fine when I last tested it.
Ira SaundersAuthor Commented:
Gustav, I did follow your link above, but it says right in the link "I think you can't use 7zip dlls from vba, but you can add some intelligence to command line".  Isn't this shelling out? Exactly what I was trying to avoid.  Maybe I am not understanding...
Daniel PineaultPresident / Owner CARDA Consultants Inc.Commented:
You have to use the command line executable, which implies you need to perform a shell command in some shape or form.
Ira SaundersAuthor Commented:
Ok, that means I have to distribute that program and make sure it's installed correctly for all my users.  That can make for tough support issues.  I was just hoping for a more seamless solution.
Daniel PineaultPresident / Owner CARDA Consultants Inc.Commented:
The programs doesn't need to actually be installed.  The command line executable file just needs to be present so you can refer to it in your shell.  Just make a subfolder with the file in it and call it from there.  If you review my sample code, that is exactly what I do (sExePath).
Gustav BrockCIOCommented:
I assumed you would read more than a few lines.
A link was included by John for the VszLib which seems to do exactly what you are asking for.
Jim Dettman (EE MVE)President / OwnerCommented:
To add a bit, the link gustav is pointing you to is:

https://github.com/wqweto/VszLib

which is a lib with a .dll, not an .exe with the shell command.  You will be able to call this directly from VBA.   However in this case, it seems that this is the type of .DLL that needs to be registered:

Using component
Register Bin\VszLib.dll with regsvr32.exe (or VB6 IDE) and add a reference (Project | References...) in your project to 7-zip VB6 Helper 1.0. You only need to redistribute Bin\VszLib.dll with your application, pdb files are needed only for debugging purposes.

 The last part is confusing though and I don't believe accurate (not for VBA anyway).

 There are two different types of .DLLs; one's you simply call via a DECLARE and others that you register, which then exposes their interface.   This seems to be the type where you need to set a reference in VBA (I didn't go through the docs in detail).

 If you do that (set a reference), then the .DLL must be on the end users system, and registered.

 I have some code to register a DLL if you need it.   But for the moment, try using the .DLL and see if it does what you need it to do.

 Also look over the docs....it may be possible to work with the .DLL by not setting a reference and just using DELCARE's in VBA, like you would do with a Windows API call.

HTH,
Jim.
Ira SaundersAuthor Commented:
Jim/Gustuv,

Thanks for your continued help and understanding guys.
I finally got the VszLib.dll  to register under references in MS Access and the code below does compile.

With New cVszArchive
    .AddFile "c:\temp\test.txt"
    .CompressArchive "c:\temp\test.7z"
End With

With New cVszArchive
    .OpenArchive "c:\temp\test.7z"
    .Extract "c:\temp\testextracted.tzt"
End With

But the code throws an error on the first line. "ActiveX component can't create object"

Then, I tried to register vszlib.dll using
regsvr32 "C:\Users\ISaunders\Desktop\VszLib-master\VszLib-master\Bin\VszLib.dll"
and that will not work. Says file cannot be registered.  I know I'm getting close because the code does compile.

Any suggestions?

Thank you,
Jim Dettman (EE MVE)President / OwnerCommented:
<<regsvr32 "C:\Users\ISaunders\Desktop\VszLib-master\VszLib-master\Bin\VszLib.dll"
and that will not work. Says file cannot be registered.>>

 There are two regsvr32.exe's under a 64 bit OS.

 The one for 32 bit is in C:\Windows\SysWOW64\

 and for 64 bit  C:\Windows\System32.

 Make sure you register it for the bitness of Office/Access that you are using.  So if your using 32 bit Access, you'll need to use the one in C:\Windows\sysWOW64.

Jim.
Daniel PineaultPresident / Owner CARDA Consultants Inc.Commented:
I haven't spent a lot of time, but I couldn't get it functional.  I fix one issue and another pops up.  

Why are you against shelling the command directly to the 7-zip command line executable?  Nothing to install, nothing to register, simple, straightforward and it works.
Jim Dettman (EE MVE)President / OwnerCommented:
Daniel has a good point...nothing wrong with Shell other than possibly not getting back error information.  The only other issue is that it executes asynchronously, so you'll need a "wait" routine (can help you with that).

But if it's just that your worried about it being visible, you can specify vbMinimizedNoFocus or vbHide when you call shell() and take care of that.

Jim.
Ira SaundersAuthor Commented:
Ok, I'm going the shell route and I downloaded the 7-zip package from the website. I don't see 7za.exe, just 7z.exe. So I changed the code to 7z.exe, but I still keep getting errors like type mismatch, invalid command line. Before I start messing with the code any further. Is there a 7za.exe?

Thanks,
Jim Dettman (EE MVE)President / OwnerCommented:
I just went back and looked and I don't see where the .exe has been pointed to that you can shell to, but the latest is available here:

https://sourceforge.net/projects/sevenzip/files/7-Zip/18.05/

Jim.
Jim Dettman (EE MVE)President / OwnerCommented:
Also, you will need the following code, which is similar to the code in the thread that gustav pointed you to.   You call it like this:

          ' Write command file
160       intFileNum = FreeFile
170       Open strFTPCommandFile For Output As #intFileNum
180       Print #intFileNum, "@ftp -i -s:" & strFTPScriptFile & " " & strFTPSiteName & " > " & strFTPLogfile
190       Close #intFileNum

          ' Execute
200       lngHWnd = Shell(strFTPCommandFile, vbHide)
210       WaitWhileRunning (lngHWnd)

Open in new window


Jim.

' Used for wait check.
Const STILL_ACTIVE = &H103
Const PROCESS_QUERY_INFORMATION = &H400


Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long


Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long


Public Sub WaitWhileRunning(lngHWnd As Long)

          Dim lngExitCode As Long
          Dim lnghProcess As Long

10        lngExitCode = STILL_ACTIVE
20        lnghProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, lngHWnd)

30        If lnghProcess > 0 Then
40            Do While lngExitCode = STILL_ACTIVE
50                Call GetExitCodeProcess(lnghProcess, lngExitCode)
60                DoEvents
70            Loop
80        End If

90        CloseHandle lnghProcess

End Sub

Open in new window

Gustav BrockCIOCommented:
You can study my articles on similar tasks, indeed for the use of ShellWait and Sleep:

Zip and unzip files and folders with VBA the Windows Explorer way
Handle cabinet files and folders with VBA the Windows Explorer way
Ira SaundersAuthor Commented:
Gustav,

Can I use your UnZip routine to extract a Gzip file. This this:
Result = UnZip("C:\QCCLocal\TrackingNumbers\IraLabelTest.gzip", "C:\QCCLocal\TrackingNumbers\IraLabelTest.zpl", False)
Daniel PineaultPresident / Owner CARDA Consultants Inc.Commented:
Did you look at my original link, the functions are right there, ready to go.
Ira SaundersAuthor Commented:
Okay, I've gotten it to work in cmd mode at the DOS prompt.

    sShellCmd = "7z.exe x" & _
                " " & Chr(34) & sZipFile & Chr(34) & _
                " -o " & Chr(34) & sDestDir & Chr(34)

Seems like the -o is causing some issue with the new version.  I there anyway to specify the output directory and output filename?
Jim Dettman (EE MVE)President / OwnerCommented:
Just a note that I cam going to step out of this.   I only wanted to add a point or two to clarify a bit what had already been suggested, and I have no direct experience with this lib.

Sounds like your getting close and will be there before you know it!

Jim.
Daniel PineaultPresident / Owner CARDA Consultants Inc.Commented:
I there anyway to specify the output directory and output filename?
That's what sDestDir is for.

Did you create a module and add the code from the link?
Did you adjust the path of the s7ZipDir module level const?
If that is done, please post you code for call the function, normally it should look like
Zip_UnZipFile "C:\Databases\Testing\Database7.zip", "C:\Databases\Testing\"

Open in new window

(check the format)
Ira SaundersAuthor Commented:
Can I specify the output FILENAME as well?
Daniel PineaultPresident / Owner CARDA Consultants Inc.Commented:
Never tried it.  Id' probably just use the Name statement after unzipping the file.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
gzip

From novice to tech pro — start learning today.