Using 7-ZIP with VBA

Question

I have a simple code that does an export with TransferSpreadsheet. Is there a way I can use VBA to then select that file, along with a text file, and use 7-ZIP to put them into a single compress file?

Private Sub Command0_Click()

    CurrentDb.QueryDefs("qryExports").SQL = "Select*From tblScope Where Plant ='46'"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExports", "C:\Users\Desktop\Scope_46.xlsx", True

End Sub

Thanks

Answer 1

Here's a starting point regarding 7-zip, see: http://www.devhut.net/2017/04/06/vba-zip-unzip-compress-extract/

Answer 2

Well, the article tells it all, but - as you can read - it would be as simple as:

Private Sub Command0_Click()

    Dim Path As String

    Dim Result As Long

    Path = "C:\Users\Desktop\Scope_46.xlsx"

    CurrentDb.QueryDefs("qryExports").SQL = "Select * From tblScope Where Plant ='46'"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExports", Path, True

    Result = Zip(Path)

End Sub

To zip more files, copy them to an empty folder and specify the folder:

Path = "C:\Users\Desktop\ExportFolderName"

Result = Zip(Path)

Answer 3

Only reason I asked about 7-ZIP is, it is the company standard. It is installed on all corporate computers. If Windows has a compression program that does the same thing, then I don't see any reason why I shouldn't be able to use that. 

Yes, I want to be able to select the two files, compress them, and then use Outlook to email the files.

Would you be able to help me with the coding for getting the compression done? I can do the email attachment.

Thank you

Answer 4

Albert is right. 7-Zip is not needed, native tools will do. 

Study my article and code:

Zip and unzip files and folders with VBA the Windows Explorer way

Answer 5

Do you have to use 7-zip, or can you use the windows “built in” zip library?

While you “answer” the above, the answer is yes, you could have one button that does the export, creates the zip file, and then say launches outlook with the zip file attached.

So “how far” you want to go with this is really up to you.

I don’t know if 7-zip supports automation, but it likely supports command line interface. So you need to look up how to zip a file from the “command line” interface. You can then use the shell() command from Access to “execute” the 7-zip utility.

However, since windows has zip file support, then there are more “solutions” hanging around on the internet that work with Access.

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada