Resolved - VBA Code to Winzip external File

[holly]

Hi

Does anyone have or possibly point me in the right direction of some VBA
Code that will winzip a file without manual intervention?


Thanks

[si_the_geek]

Thread moved from "Database Development" forum to "Office Development/VBA" forum

[westconn1]

the shell object seems to do this quite well

p = "c:\test"
      z = p & "\mytest.zip"
      Open z For Output As 1
          Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
      Close 1
      Set sh = CreateObject("shell.application")
      sh.namespace(z).copyhere p
      Set sh = Nothing

copies all the files in folder test into new zip file in same folder

[holly]

Hi

I have copied your code as below and am still having problems

    Dim P As String
    Dim Z As String
    Dim ZipFileName As String
    Dim MyAppID As String
    
    P = filename
    
    If Not Right(P, 4) = ".zip" Then
        P = P & ".zip"
    Else
        P = P & ".CSV"
    End If
    
    Z = P
    
    Open Z For Output As 1
        Print #1, filename
    Close Z
    
   ' MyAppID = Shell("C:\Program Files\WinZip\Winzip.exe", 1)   ' Run
  '  AppActivate MyAppID ' Activate Microsoft

    MyAppID = CreateObject("shell.application")
[COLOR="Red"]    MyAppID = NameSpace(Z).copyhere & P[/COLOR]

    
    Set MyAppID = Nothing

End Sub

I get an error on this line as it states 'Invalid qualifier'

Pls help!!

[westconn1]

i can see that you would, is filename a valid string in the scope of this proceedure?
what would the content of filename be when testing?

opening a zip file for output would overwrite it as a text file, even though it has zip extention

no & required in that line

[holly]

This is the full procdure whereby I'm passing in the files I wish to zip!

Public Sub CreateZipFile(filename As String)
    Dim P As String
    Dim Z As String
    Dim ZipFileName As String
    Dim MyAppID As FileSystemObject
    Dim ff As Integer
    
    Set MyAppID = New FileSystemObject
    
    P = filename
    
    If Not Right(P, 4) = ".zip" Then
        P = Right(P, 4) & ".zip"
    Else
        P = P & ".CSV"
    End If
    
    Z = P
    
    ff = FreeFile()
   
    Open Z For Output As #ff
        Print #ff, filename & ","
    Close #ff
        
    MyAppID = CreateObject("shell.application")
  '  MyAppID.NameSpace(Z).copyhere P
    
     Set MyAppID = Nothing

End Sub

I have numerous problems whereby the file is not being zipped?
b) what reference's wouldI need ?

Thanks

[westconn1]

if you pass an existing zipfile to the proceedure, opening it for output then writing text(filename) to it, will convert it to txt file, with .zip
my example created a new zip file by writing the correct bytes to it

in your current code if filename is not zip file you should just exit sub as the code will not do anything for csv or other file type
if you pass a zipfile your else will add.csv to it, so you could have somefile.zip.csv or somefile.***.zip, depending on what is in file name
possibly you want to pass a csv to the sub and zip it to file with same name
as filename is a variable already there is no need for p

z = left(filename, instr(filename, ".")) & "zip"    'truncate filename at . and add zip
    ff = FreeFile()
    Open Z For Output As #ff
        Print #ff, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)   ' makes zip file
    Close #ff
    set MyAppID = CreateObject("shell.application")
    MyAppID.NameSpace(Z).copyhere filename    'add the original file to the created zip

declaring and setting myappid as a filesystemobject is incorrect, if you want to add a reference, which is not required, it should be, i believe, to shell32, otherwise just declare myappid as object
and it has to be
set myappid = createobject("shell.application")

please reread the original example i posted

[holly]

I'm sorry to be a pain but i'm getting an error on this line

    Set MyAppID = CreateObject("shell.application")
    MyAppID.NameSpace(Z).copyhere filename

stating ' Object variable not set' but as you can see it is with the line above

Thanks for your help and patience!!!

[westconn1]

is z a fully qualified filename to a folder or valid zip file, if the zip file is not valid it will not appear as a folder to the shell

the original code was copied from a working example

make sure the the fiel specified in Z is a working zip file, even if you created it here, i should be able to be opened in winzip and files added, or files added in windows explorer

have you changed the dimension for myappid from filesystemobject
check in the locals window if myappid is shell application or some other object

[holly]

Thanks very much....it now works a treat!!

Just a quick question...if I wanted to encrypt the file or place a pwd on the
*.csv file, how easy would it be?

[westconn1]

you can set a password on zip files, but i don't know how to do in code
there are many examples on encrypting strings, to differing strengths, in the vb6 forum, most of which would work in VBA, csv files are just a string (text), so you could encrypt the content of the file before zipping