Running a batch file

[Sam]

Hi,
I need to create a zip file for access database thru
code. I tired using Shell(""). But the command is not
being executed. The file name changes dynamically. So I
created a DOS batch file with a parameter. I tried
running the code Shell(Environ$("ComSpec") " /c
batchfilename " & Parameter1)
Still no luck. So I tried using RunApp Action command in
Macros. But there is a CommandLine argument. I cannot
put the command line, because the file name changes
dynamically.

Any Ideas is greatly appreciated. Or is there a better
way of doing this.

TIA
Sam

 

[Sam]

Hi,
Thanks for your response. I tried for couple of hrs but
no luck. I tried taking out "/c" out in the code to see
whats going on. I see that the dos window opened. No
command is being issued, in our case it should have been
Test.bat.

To test the other way I tried entering the following in
the Run Window

cmd C:\Test.bat "MyFile.txt" but I see the dos window
open and nothing more. I think this is the problem, its
just taking the command "cmd" thats it.

I tried this on both Windows XP and Office XP Developer
Edition and also on Windows 2K. No luck. I can send the
sample code if you would like to see.

Is there any other way of creating compressed files?


Thanks again,
Sam

-----Original Message-----
I called the following sub from the immediate window:

Public Sub temptest2(Parameter1 As String)
Shell Environ$("ComSpec") & " /c C:\Test.bat " & Parameter1
End Sub

Using the command
temptest2 "test"

It worked as expected. You would enclose the Shell

command line in () if it is to return a

value.
Reply=Shell(....)

If there are spaces in the path/name of the batch file,

you will need to double up some

 

[PC Datasheet]

Sam,

From my files - see below my signature line.
--
PC Datasheet
A Resource for Access, Excel and Word Applications
(e-mail address removed)
www.pcdatasheet.com

· Design and basic development for new applications
· Additions, Modifications and "Fixes" for existing applications
· Mentoring for do-it-yourselfers who want guidance
· Complete application design and development
· Applications Using Palm Pilot To Collect Data And
Synchronize The Data Back To Access Or Excel


Programatically Zip And Unzip A MDB File



You can use the WinZip command line extensions to
silently/programmatically zip / unzip your .mdb. For instance, you can unzip
your file to a known location with wzunzip.exe, process it, and then rezip
it by shelling to wzzip.exe:

wkFile = Me.usFTPDest & Me.usISACTempFile
wkTemp = "C:/Progra~1/WinZip/wzzip.exe -s" & Me.usISACFtpPwd & " " &
Me.usFTPDest & Me.usISACFilename & " " & wkFile
OK = Shell(wkTemp, vbMinimizedNoFocus)

the winzip command line package contains full documentation on caommand line
parameters.

Just for fun, here's the full working function. It takes a text file,
compresses it using wzzip, protects the file with a password, and then FTP's
it out to a production server using the MSInet control. Hopefully the
variable names are self-documenting.

<compiled code>

Private Sub btnISACFileSend_Click()
Dim OK As Double
Dim wkSrc As String
Dim wkCmd As String
Dim wkVal As String
Dim wkTemp As String
Dim wkFile As String
Dim wkPath As String
Dim SQ As String

On Error GoTo ISACFileSendErr
SQ = "This will post MAP requests to the ISAC FTP server" & vbCrLf & _
"Do you wish to continue?"

If (MsgBox(SQ, vbYesNo, "Post MAP to ISAC") <> vbYes) Then GoTo
ISACFileSendExit

wkFile = Me.usFTPDest & Me.usISACTempFile
wkTemp = "C:/Progra~1/WinZip/wzzip.exe -s" & Me.usISACFtpPwd & " " &
Me.usFTPDest & Me.usISACFilename & " " & wkFile
OK = Shell(wkTemp, vbMinimizedNoFocus)

wkSrc = "ftp://" & Me.usISACFtpUser & ":" & Me.usISACFtpPwd & "@" &
Me.usISACFtpIP
wkCmd = "SEND " & Me.usFTPDest & Me.usISACFilename & " " &
Me.usISACFilename

Me.FT.Execute wkSrc, wkCmd
While (Me.FT.StillExecuting = True)
DoEvents
Wend
Me.FT.Execute , "CLOSE"

OK = MsgBox("MAP Billing File Successfully Posted to ISAC", vbOKOnly, "MAP
File Send")

ISACFileSendExit:
Exit Sub

ISACFileSendErr:
If (Err = 53) Then
Resume Next
Else
MsgBox "Error in ISACFileSend: " & Err & ": " & Err.Description
Resume ISACFileSendExit
End If
End Sub

</compiled code>

BTW: Be sure to register your winzip. First shareware package I ever bought
and still the best.

 

[Wayne Morgan]

Yes, please. Copy and paste the code to avoid typos.

There are other ways to zip the files. There are zipping dlls you can get so
that the whole thing can be done from within the Windows environment.

 

[Sam]

Can you please some more information on the dlls please.

thanks
Sam