Home » Category » Microsoft Excel

Microsoft Excel: Zipping Excel files

205| Fri, 23 May 2008 09:15:00 GMT| anonymous| Comments (2)
Hi There,

I am trying to create a vba macro to automatically zip my
all excel file with use of SHELL command in vba but I am
unsuccessful. Can anyone check my code and tell me what's
wrong with it. What it does I am trying to use the
current date as my zip filename. When I run it seems
nothing happened and gives no error.

Sub ZIPMYFILE()
TempoFileDIR = Format(now, "yyyymmdd_hhmmss")

Dim TodaysArchive As String
Dim aBatchCopy As String
TodaysArchive = iArchivePath & TempoFileDIR & ".zip"
& " " & cWorkingPath
aBatchCopy = ("cmd /c PKZIPC -add -
directories=relative " & TodaysArchive)
Call Shell(aBatchCopy, vbHide)

End sub

TIA
Eric

Keywords & Tags: zipping, excel, files, microsoft

URL: http://www.developertags.com/microsoft-excel/495147/
 
«« Prev - Next »» 2 helpful answers below.
See a example on my site Eric
I will release my new SendMail Add-in soon (this can zip also)

http://www.rondebruin.nl/sendmail.htm#Zip
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl

"Eric" <ago45...hotmail.com> wrote in message news:0ba101c3810a$a80d3600$a101280a...phx.gbl...
> Hi There,
> I am trying to create a vba macro to automatically zip my
> all excel file with use of SHELL command in vba but I am
> unsuccessful. Can anyone check my code and tell me what's
> wrong with it. What it does I am trying to use the
> current date as my zip filename. When I run it seems
> nothing happened and gives no error.
> Sub ZIPMYFILE()
> TempoFileDIR = Format(now, "yyyymmdd_hhmmss")
> Dim TodaysArchive As String
> Dim aBatchCopy As String
> TodaysArchive = iArchivePath & TempoFileDIR & ".zip"
> & " " & cWorkingPath
> aBatchCopy = ("cmd /c PKZIPC -add -
> directories=relative " & TodaysArchive)
> Call Shell(aBatchCopy, vbHide)
> End sub
> TIA
> Eric
>

ron | Fri, 23 May 2008 09:16:00 GMT |

Oops

You are using PKZIPC
I am not familiar with this program
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl

"Ron de Bruin" <rondebruin...kabelfoon.nl> wrote in message news:%23S$tbGRgDHA.164...tk2msftngp13.phx.gbl...
> See a example on my site Eric
> I will release my new SendMail Add-in soon (this can zip also)
> http://www.rondebruin.nl/sendmail.htm#Zip
>
> --
> Regards Ron de Bruin
> (Win XP Pro SP-1 XL2002 SP-2)
> www.rondebruin.nl
>
> "Eric" <ago45...hotmail.com> wrote in message news:0ba101c3810a$a80d3600$a101280a...phx.gbl...
> > Hi There,
> >
> > I am trying to create a vba macro to automatically zip my
> > all excel file with use of SHELL command in vba but I am
> > unsuccessful. Can anyone check my code and tell me what's
> > wrong with it. What it does I am trying to use the
> > current date as my zip filename. When I run it seems
> > nothing happened and gives no error.
> >
> > Sub ZIPMYFILE()
> > TempoFileDIR = Format(now, "yyyymmdd_hhmmss")
> >
> > Dim TodaysArchive As String
> > Dim aBatchCopy As String
> > TodaysArchive = iArchivePath & TempoFileDIR & ".zip"
> > & " " & cWorkingPath
> > aBatchCopy = ("cmd /c PKZIPC -add -
> > directories=relative " & TodaysArchive)
> > Call Shell(aBatchCopy, vbHide)
> >
> > End sub
> >
> > TIA
> > Eric
> >
> >
>

ron | Fri, 23 May 2008 09:17:00 GMT |

Microsoft Excel Hot Answers

Microsoft Excel New questions

Microsoft Excel Related Categories