Shell Function

[Firemansam]

G'day All,

I'm trying to fumble my way thru this....

I have managed to get VBA to creat a foler with todays date in aserial number type format as the folder name using this code....

Public Function CreateFolder()

Set CF = CreateObject("Scripting.FileSystemObject")
Set a = CF.CreateFolder("M:\QOLData\Sent" & Format(Date, "ddmmyyyy"))


End Function

I am now trying to get the shell command to try and get winzip to try and put a zip file in the folder that is created by the first function. I have start something like this but can't get it to go any further it keeps bring up an error saying the file does no exist.

Function ExportData()

Dim RetVal, MyValue As String

MyValue = ("M:\QOLData\Sent" & Format(Date, "ddmmyyyy"))
RetValue = Shell("C:\program files\Winzip\winzip32.exe -min -a M:\QOLData\&MyValue\Test.zip M:\QOLData\QOLTb1.txt")

End Function


Is this possible and if so can some one steer me in the right direction.

Cheers in advance!!!

Andrew

 

[Calvin]

your not concatenating your MyValue string correctly.
Try:

MyValue = ("M:\QOLData\Sent" & Format(Date, "ddmmyyyy"))   'this is ok
RetValue = Shell("C:\program files\Winzip\winzip32.exe -min -a " & MyValue & "\Test.zip M:\QOLData\QOLTb1.txt")  'this line needed fixed

-cheerz yourself

 

[Firemansam]

Thanks heaps for your help!!! It worked like a charm.