VBA - batch file
bsn
Hi NG
I try to start a batch file which uses an .txt file for scripting...
The txt file includes FTP username and password, and a put statement...
Code:
stAppName = CurrentProject.Path & "\backupfile.bat"
Debug.Print stAppName
MsgBox stAppName
Call Shell(stAppName, 1)
/Code
stAppName = C:\Documents and Settings\Bjarne\Desktop\backupfile.bat
The batch file should start an .txt file(backupfile.txt)
The .txt file lies on the Desktop to...
When i click on the batch file on the Desktop it all works well...
Its only when i try starting script from VBA the error occurs...
When I try to start the batch file from VBA, i get an error saying :
"Error opening scriptfile backupfile.txt."
Anybody have a hint...???
Greetings
Bjarne
"Every day new surprises"
Vacuum Sealed
I fairly certain you have to wrap this line:
stAppName = C:\Documents and Settings\Bjarne\Desktop\backupfile.bat
stAppName = "C:\Documents and Settings\Bjarne\Desktop\backupfile.bat"
HTH
Mick
bsn
"Vacuum Sealed" <mcl...@bigpond.com> skrev i en meddelelse
news:jWGzp.4248$aH5....@viwinnwfe02.internal.bigpond.com...
Thx for answer...
I tried this, but with same result...
Strange problem, when i click on the .bat file on the Desktop it all works
fine...
Bjarne
John Spencer
You did something like the following to wrap the string in quotes.
stAppName = Chr(34) & CurrentProject.Path & "\backupfile.bat" & Chr(34)
Call Shell(stAppName, 1)
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
bsn
Ur suggestion are not working - same result...
No I did this way, with same result as u write about ...
"""" & CurrentProject.Path & "\bufile.bat" & """"
As u see i have shorten the filename to under 8 chars to...
another hint i found on the internet...
but its still not working...8-(
Bjarne
"John Spencer" <JSPE...@Hilltop.umbc> skrev i en meddelelse
news:iqom3d$3bm$1...@dont-email.me...
Douglas J Steele
run batch files.
Try:
stAppName = Chr$(34) & Environ(%comspec%) & Chr$(34) & " " & _
Chr$(34) & CurrentProject.Path & "\backupfile.bat" & Chr$(34)
Call Shell(stAppName, 1)
Because Chr$(34) puts double quotes around the filename, there's no need to
worry about it being less than 8 characters.
"bsn" wrote in message
news:4dcfeec5$0$56780$edfa...@dtext02.news.tele.dk...
bsn
Unfortunately its still not working...
Ur suggestion gives me a dos window with a prompt who says "C:\Documents"...
Bjarne
"Douglas J Steele" <NOSPAM_djsteele@NOSPAM_gmail.com>
Benny Andersen
cmd /k
or
...Environ(%comspec%)& " /k" ...
Yoy will have to close the window manually - or else finalize the
command with
"&pause&exit"
I believe it's also possible to use a 'silent' version:
cmd /c
but then the second parameter yo the shell call shall be
vbMinimizedFocus (2)
bsn
...Environ(%comspec%) & " /k"... gives an error :
"File not found"
Bjarne
"Benny Andersen" <a.mai...@gmail.com> skrev i en meddelelse
news:4dd032e8$0$307$1472...@news.sunsite.dk...
Phil
Try ShellWait(CurrentProject.Path &""\backupfile.bat", true)
The bit up to the "Public Function ShellWait" needs to go in the
declarations. You probably don't need the word "PtrSafe"
Phil
'This code was originally written by Terry Kreft.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Terry Kreft
Private Const STARTF_USESHOWWINDOW& = &H1
Private Const NORMAL_PRIORITY_CLASS = &H20&
Private Const INFINITE = -1&
Private Type STARTUPINFO
cb As Long
lpReserved As String
lpDesktop As String
lpTitle As String
dwX As Long
dwY As Long
dwXSize As Long
dwYSize As Long
dwXCountChars As Long
dwYCountChars As Long
dwFillAttribute As Long
dwFlags As Long
wShowWindow As Integer
cbReserved2 As Integer
lpReserved2 As Long
hStdInput As Long
hStdOutput As Long
hStdError As Long
End Type
Private Type PROCESS_INFORMATION
hProcess As Long
hThread As Long
dwProcessID As Long
dwThreadID As Long
End Type
Private Declare PtrSafe Function WaitForSingleObject Lib "kernel32" (ByVal _
hHandle As Long, ByVal dwMilliseconds As Long) As Long
Private Declare PtrSafe Function CreateProcessA Lib "kernel32" (ByVal _
lpApplicationName As Long, ByVal lpCommandLine As String, ByVal _
lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As Long, _
lpStartupInfo As STARTUPINFO, lpProcessInformation As _
PROCESS_INFORMATION) As Long
Public Function ShellWait(PathName As String, Optional WindowStyle As Long)
As Integer 'ShellWait ("C:\Program Files\ZipGenius 6\ZG.Exe -add " &
"""C:\Documents and Settings\Phil\My Documents\Access\MDB\WFYC\WFYCTab2007 02
September 2008 09 49.zip""" & " C5 +" & """C:\Documents and Settings\Phil\My
Documents\Access\MDB\WFYC \WFYCTab2007.mdb""")
Dim proc As PROCESS_INFORMATION
Dim Start As STARTUPINFO
Dim ret As Long
' Initialize the STARTUPINFO structure:
With Start
.cb = Len(Start)
If Not IsMissing(WindowStyle) Then
.dwFlags = STARTF_USESHOWWINDOW
.wShowWindow = WindowStyle
End If
End With
' Start the shelled application:
ret& = CreateProcessA(0&, PathName, 0&, 0&, 1&, _
NORMAL_PRIORITY_CLASS, 0&, 0&, Start, proc)
' Wait for the shelled application to finish:
ret& = WaitForSingleObject(proc.hProcess, INFINITE)
ret& = CloseHandle(proc.hProcess)
ShellWait = ret ' 1 for OK, 0 for failes
End Function
bsn
"Phil" <ph...@stantonfamily.co.uk> skrev i en meddelelse
news:iqqpo9$n9f$1...@speranza.aioe.org...
Gives an error - "Sub or Function not defined" ---> (CreateProcessA)
Bjarne
bsn
My bufile.bat file
code
@echo off
echo Backup startet - Vent venligst mens statusbar er aktiv...
ftp -v -s:bufile.txt ftp.mydomain.dk
echo Backup afsluttet...
pause
/code
My bufile.txt file
code
/code
bsn
Here is the code in both files...
The bat file should start the txt file - but it never happens...
But if I click on the bat file on the Desktop - all works like a charm...
My bufile.bat file
code
@echo off
echo Backup startet - Vent venligst mens statusbar er aktiv...
ftp -v -s:bufile.txt ftp.mydomain.dk
echo Backup afsluttet...
pause
/code
My bufile.txt file
code
username
password
hash
cd test
lcd C:\Documents and Settings\Bjarne\Skrivebord
put "MyFileName_2003_XPsp3.mdb" "8533-4968-8320.mdb"
disconnect
bye
/code
Phil
> Gives an error - "Sub or Function not defined" ---> (CreateProcessA)
> Bjarne
>
>
I presume this is a compile error.
What version of Access are you using?
You may or may not need the word "PtrSafe" in the 2 Declare statements - try
it both ways. As you can see, the CreateProcessA is defined, so not sure why
you are having a problem. The whole of the information I gave you needs to go
in a standard module, not a form module
Phil
bsn
>>
>> Gives an error - "Sub or Function not defined" ---> (CreateProcessA)
>> Bjarne
>>
>>
> Hi Bjarne.
> I presume this is a compile error.
> What version of Access are you using?
Access 2003
But this error are fixed...
There comes a new error up...
"Sub or Function not defined" ---> (CloseHandle)
I tried to declare CloseHandle as Long, but it gave a new error "Expected
array"...proberly the HandleList...
> You may or may not need the word "PtrSafe" in the 2 Declare statements -
> try
> it both ways. As you can see, the CreateProcessA is defined, so not sure
> why
> you are having a problem. The whole of the information I gave you needs to
> go
> in a standard module, not a form module
> Phil
I cant use the function if it contains "PtrSafe"...
Bjarne
Phil
Sorry Bjarne
You need
Private Declare Function CloseHandle Lib "kernel32" (ByVal _
hObject As Long) As Long
in the declarations as well. Missed it in my original post.
I think the PtrSafe bit is only used in Access 2010 64 bit, and everyone
advises using the 32 bit version. I installed the 64 bit version originally
so added the PtrSafe thing to all my functions, then changed it for the 32
bit version and never bothered to remove the PtrSafe
Phil
bsn
Thx...sorry for the late respons - busy u know...:-))
Now the function starts, but sends the database seems like into an endless
loop...or endless waiting...
The batch file never starts...
Still if i click batch file on desktop it starts fine...its driving me
nuts...
Bjarne
Phil
ProgressMeter.bat",vbhide) in the immediate window, it works fine
Other than that, I don't know
Phil
bsn
Ok...
Maybee I come back to the subject later...
In the meantime I found, and use this solution - it dont depend on any
files...
FTP directly from Access...
http://www.tek-tips.com/viewthread.cfm?qid=1073963&page=1
Thx a lot Phil for helping...
Bjarne
bsn
Did ur batch file contain a FTP job, calling a scriptfile...???
Bjarne
Phil
No Just a straightforeward decompile instruction
Copy "C:\Phil Data\Access\MDB 2010\Progress Meter.accdb", "C:\Phil
Data\Access\MDB 2010\Decompile Progress Meter.accdb" "C:\Program Files
(X86)\Microsoft Office\Office14\MSACCESS.EXE" "C:\Phil Data\Access\MDB
2010\Decompile Progress Meter.accdb" /decompile
Echo "Compile DB, then compact it"
Can't see what is in the batch file should make any difference, if it runs OK
from the desktop. If you change the "vbHide" to "vbNormalFocus", you shouls
see the DOS box
ie ?shellwait("C:\Phil Data\Access\MDB 2010\Decompile
ProgressMeter.bat",vbNormalFocus)
Phil
Stuart McCall
news:4dda77bb$0$56771$edfa...@dtext02.news.tele.dk...
[]
> Ok...
> Maybee I come back to the subject later...
> In the meantime I found, and use this solution - it dont depend on any
> files...
> FTP directly from Access...
> http://www.tek-tips.com/viewthread.cfm?qid=1073963&page=1
>
> Thx a lot Phil for helping...
> Bjarne
You could also give this a try. Does more than copy files, should you need
it to...
http://www.smccall.demon.co.uk/Downloads.htm#FTPClient
bsn
I have tried this - gives the same error...
"Error starting scriptfile bufile.txt"
I think maybee its the rights to the file bufile.txt...
But I will use the fileless version...:-)
Thx for ur patience...
Bjarne
bsn
"Stuart McCall" <smc...@myunrealbox.com> skrev i en meddelelse
news:SGBCp.24830$7H.1...@newsfe08.ams2...
Thx Stuart - Ill look at it later on...
Bjarne
Phil
Last thought Bjarne
Does your scriptfile use the database you are calling it from, in which case
there could be a problem. If you are, might be worth trying to run the batch
file on a copy of your Db. Otherwise, I'm stumped
Phil
bsn
Yes it uses the open database...
I tried to copy another database...the same error appears...
But still it works if I click on the bat file on the desktop...despite of
the database are open...
I'm stumped in the problem to - so I'm satisfied with my fileless FTP'ing...
Bjarne