How to compress an XML file in ZIP in VBA Excel?

Question

How to compress an XML file in ZIP in VBA Excel?

Navigation

#1 by (1 votes)

1

I'm trying to compress an XML file into a ZIP file and I'm using the code below, but I'm running into the following error

Indicate the error on this line

Shell.Namespace(zipFile).CopyHere (xmlFile)

Code:

 Sub CreateZipFile(BalanzaComprobacion As String)

    Dim zipFile As String           
    Dim xmlFile  As String         
    Dim Shell As Shell32.Shell

    zipFile = "C:\XML\" & BalanzaComprobacion & ".zip"
    xmlFile = "C:\XML\" & BalanzaComprobacion & ".xml"

    NewZip (zipFile)

    Shell.Namespace(zipFile).CopyHere (xmlFile)

End Sub

Sub NewZip(zipFile)

    Dim oFSO, arrHex, sBin, i, Zip
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    arrHex = Array(80, 75, 5, 6, 0, 0, 0, _
                   0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
    For i = 0 To UBound(arrHex)
        sBin = sBin & Chr(arrHex(i))
    Next
    With oFSO.CreateTextFile(zipFile, True)
        .Write sBin
        .Close
    End With
End Sub

vba xml zip excel

asked by Arturo Carrillo Fernández 07.03.2018 в 19:19

source

1 answer

Modify SQL query to get response with desired structure sql query returns incorrect result

score 1 · Accepted Answer

The error is that you want to use a variable type string when what you should use in variant for the code to work correctly

 Sub CreateZipFile(BalanzaComprobacion As String)

    Dim zipFile As variant           
    Dim xmlFile  As variant         
    Dim Shell As Shell32.Shell

    zipFile = "C:\XML\" & BalanzaComprobacion & ".zip"
    xmlFile = "C:\XML\" & BalanzaComprobacion & ".xml"

    NewZip (zipFile)

    Shell.Namespace(zipFile).CopyHere (xmlFile)

End Sub

Likewise you must remember that if you leave the variables in this way

    Dim zipFile 
    Dim xmlFile

It's the same as above.

NOTE : I tested that code on a computer with 64, so I want to leave the 64-bit version.

  Sub CreateZipFile(BalanzaComprobacion As String)

        Dim zipFile          
        Dim xmlFile     
        Dim myShell As Object

        zipFile = "C:\XML\" & BalanzaComprobacion & ".zip"
        xmlFile = "C:\XML\" & BalanzaComprobacion & ".xml"

        NewZip (zipFile)

        Set myShell = CreateObject("Shell.Application")
        myShell.Namespace(zipFile).CopyHere (xmlFile)

    End Sub