chestera
asked on
running a batch file from access problem in conjunction with a scanner
Hi
I have a batch file that works in conjunction with a scanner it up loads data from the scanner to a *.csv text file on the pc. We select X'mit data on the scanner then run the batch file from the desktop All works ok. So I have tried running the batch file from access using the Shell command I can see the batch file openning then closing but doesn't up load the data from the scanner. Here is contents of the batch file
if exist c:\R_data.fil del c:\R_data.fil
Neto32 -c:1 -b:19200 -p:0 -d:8 -s:2 -dt:1 -pt:1 -qs:1 -asp:1 -lslf:0
rem Port1,(2N/A), 19200, n,8, s, Overwrite, Single run, Quit, Start Protocol on startup, no log file
:loop
echo "Waiting"
if not exist c:\R_data.fil goto loop
copy c:\R_data.fil c:\scanner_data\Returns.cs v /Y
rem pause
My Shell command is
Shell = "C:\Program Files\HNEAHS Applications\Scanner\Retur ns.bat"
any help appreciated
chestera
I have a batch file that works in conjunction with a scanner it up loads data from the scanner to a *.csv text file on the pc. We select X'mit data on the scanner then run the batch file from the desktop All works ok. So I have tried running the batch file from access using the Shell command I can see the batch file openning then closing but doesn't up load the data from the scanner. Here is contents of the batch file
if exist c:\R_data.fil del c:\R_data.fil
Neto32 -c:1 -b:19200 -p:0 -d:8 -s:2 -dt:1 -pt:1 -qs:1 -asp:1 -lslf:0
rem Port1,(2N/A), 19200, n,8, s, Overwrite, Single run, Quit, Start Protocol on startup, no log file
:loop
echo "Waiting"
if not exist c:\R_data.fil goto loop
copy c:\R_data.fil c:\scanner_data\Returns.cs
rem pause
My Shell command is
Shell = "C:\Program Files\HNEAHS Applications\Scanner\Retur
any help appreciated
chestera
ASKER
bcunney
Sorry for the delay I didn't get an email from EE. Thanks for the above info. I will give it a try and get back to you
chestera
Sorry for the delay I didn't get an email from EE. Thanks for the above info. I will give it a try and get back to you
chestera
Another way you could try is the following:
dim shell
set shell=createobject("wscript.shell")
shell.run "batchfile.bat"
set shell=nothing
ASKER
bcunney
Looks easier. I down loaded the ShellEx function but having trouble on this line
Public Function ShellEx(ByVal sFIle As String, Optional ByVal eShowCmd As EShellShowConstants = essSW_SHOWDEFAULT, Optional ByVal sParameters As String = "", Optional ByVal sDefaultDir As String = "", Optional sOperation As String = "open", Optional Owner As Long = 0) As Boolean
When I run this line gets highlighted in yellow and at the end of this function Err.Raise I get App.ExeName not defined
Will try the easier way now and get back to you.
chestera
Looks easier. I down loaded the ShellEx function but having trouble on this line
Public Function ShellEx(ByVal sFIle As String, Optional ByVal eShowCmd As EShellShowConstants = essSW_SHOWDEFAULT, Optional ByVal sParameters As String = "", Optional ByVal sDefaultDir As String = "", Optional sOperation As String = "open", Optional Owner As Long = 0) As Boolean
When I run this line gets highlighted in yellow and at the end of this function Err.Raise I get App.ExeName not defined
Will try the easier way now and get back to you.
chestera
ASKER
bcunney
Dim Shell
Set Shell = CreateObject("wscript.Shel l")
Shell.Run "C:\Program Files\HNEAHS Applications\Scanner\Retur ns.bat"
Set Shell = Nothing
getting error "Method 'Run' of Object Iwshshell3 failed
chestera
Dim Shell
Set Shell = CreateObject("wscript.Shel
Shell.Run "C:\Program Files\HNEAHS Applications\Scanner\Retur
Set Shell = Nothing
getting error "Method 'Run' of Object Iwshshell3 failed
chestera
ASKER
bcunney
Got the ShellEx function working. Now getting the same errorwhen run as with your other suggestion
"Method 'Run' of Object Iwshshell3 failed
chestera
Got the ShellEx function working. Now getting the same errorwhen run as with your other suggestion
"Method 'Run' of Object Iwshshell3 failed
chestera
ASKER
bcunney
Sorry to stuff around I still had your Shell.run code behind the button. back to square one still getting the error with the ShellEx function
chestera
Sorry to stuff around I still had your Shell.run code behind the button. back to square one still getting the error with the ShellEx function
chestera
ASKER
bgcunny
Got the ShellEx function working I rem'd out the Err.Raise App.Name (Not ideal but it's working). Can't get it to run the batch file this is what I have done so far
ShellEx "C:\Program Files\HNEAHS Applications\Scanner\Retur ns.bat", essSW_SHOWNORMAL, , , , Me.hwnd
chestera
Got the ShellEx function working I rem'd out the Err.Raise App.Name (Not ideal but it's working). Can't get it to run the batch file this is what I have done so far
ShellEx "C:\Program Files\HNEAHS Applications\Scanner\Retur
chestera
Hi Chestera,
Go to Tools | References and make a reference to Microsoft Scripting Runtime
Go to Tools | References and make a reference to Microsoft Scripting Runtime
Also create a very simple .bat file with like just an echo command in it and see iif this works
ASKER
bcunney
I have finally got the ShellExecute to work and it opens a batch file I created one just as you suggested. Don't think you can help any further. When I open the Returns.bat(See below) it runs but errors and goes into a loop. When I click on the shortcut on the desktop the batch file runs then opens the Neto32.exe and transfers data from scanner to a csv file on the PC then batch file and Neto32.exe closes. It's looking for c:\R_data.fil
if exist c:\R_data.fil del c:\R_data.fil
Neto32 -c:1 -b:19200 -p:0 -d:8 -s:2 -dt:1 -pt:1 -qs:1 -asp:1 -lslf:0
rem Port1,(2N/A), 19200, n,8, s, Overwrite, Single run, Quit, Start Protocol on startup, no log file
:loop
echo "Waiting"
if not exist c:\R_data.fil goto loop
copy c:\R_data.fil c:\scanner_data\Returns.cs v /Y
rem pause
A third party programed the scanner it used some obscure C language
chestera
I have finally got the ShellExecute to work and it opens a batch file I created one just as you suggested. Don't think you can help any further. When I open the Returns.bat(See below) it runs but errors and goes into a loop. When I click on the shortcut on the desktop the batch file runs then opens the Neto32.exe and transfers data from scanner to a csv file on the PC then batch file and Neto32.exe closes. It's looking for c:\R_data.fil
if exist c:\R_data.fil del c:\R_data.fil
Neto32 -c:1 -b:19200 -p:0 -d:8 -s:2 -dt:1 -pt:1 -qs:1 -asp:1 -lslf:0
rem Port1,(2N/A), 19200, n,8, s, Overwrite, Single run, Quit, Start Protocol on startup, no log file
:loop
echo "Waiting"
if not exist c:\R_data.fil goto loop
copy c:\R_data.fil c:\scanner_data\Returns.cs
rem pause
A third party programed the scanner it used some obscure C language
chestera
ASKER
bcunney
There must be a slight difference opening a batch file directly and through access using the shell command.
I have checked and there is a file in the root directory of C called R_data.fil. got me stuffed.
chesters
There must be a slight difference opening a batch file directly and through access using the shell command.
I have checked and there is a file in the root directory of C called R_data.fil. got me stuffed.
chesters
Maybe try a shell execute and wait - i.e waiting for batch file to do it's thing.
Check out this link
http://planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1102&lngWId=-10
Check out this link
http://planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1102&lngWId=-10
ASKER
bcunney
Down loaded function. problem code in both General section and Public sub have a few red lines causing errors. I can never get these functions to work.
chestera
Down loaded function. problem code in both General section and Public sub have a few red lines causing errors. I can never get these functions to work.
chestera
Hi Chestera
Post up th red lines and I will help you
Post up th red lines and I will help you
ASKER
bcunney
Really appreciate your help here
Here is the General Declaration code
'************************* ********** ********** ********** *******
' Windows API Declarations for ShellExecAndWait
' COPY THESE DECLARATIONS TO THE GENERAL DECLARATIONS SECTION
'************************* ********** ********** ********** *******
Private Const PROCESS_QUERY_INFORMATION = &H400
Private Const TH32CS_SNAPPROCESS As Long = 2&
Private Const MAX_PATH As Integer = 260
Private Const SW_SHOW As Integer = 5
Private Type PROCESSENTRY32
dwSizeAs Long 'Red line
cntUsage As Long
th32ProcessIDAs Long 'Red line
th32DefaultHeapID As Long
th32ModuleIDAs Long 'Red line
cntThreads As Long
th32ParentProcessID As Long
pcPriClassBaseAs Long 'Red line
dwFlagsAs Long 'Red line
szExeFile As String * MAX_PATH
End Type
Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
Private Declare Function CreateToolhelpSnapshot Lib "kernel32" Alias "CreateToolhelp32Snapshot" (ByVal lFlags As Long, ByVal lProcessID As Long) As Long
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function ProcessFirst Lib "kernel32" Alias "Process32First" (ByVal hSnapShot As Long, uProcess As PROCESSENTRY32) As Long
Private Declare Function ProcessNext Lib "kernel32" Alias "Process32Next" (ByVal hSnapShot As Long, uProcess As PROCESSENTRY32) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
' Windows Version API Calls '
Type OSVERSIONINFO
dwOSVersionInfoSize As Long
' -------------------------- ---------- ----'Start redline
dwMajorVersionAs Long
dwMinorVersionAs Long 'All
dwBuildNumberAs Long
dwPlatformIdAs Long
szCSDVersionAs String * 128
'------------------------- ---------- ---' End redline
End Type
'------------------------- ---------- -- Start red line
Public Const VER_PLATFORM_WIN32s = 0
Public Const VER_PLATFORM_WIN32_WINDOWS = 1
Public Const VER_PLATFORM_WIN32_NT = 2
'------------------------- ---------- ---End redline
Private Declare Function GetVersionEx Lib "kernel32" Alias "GetVersionExA" (ByRef lpVersionInformation As OSVERSIONINFO) As Long
Public Type PROCESS_BASIC_INFORMATION
ExitStatus As Long
PebBaseAddressAs Long 'Red Line
AffinityMask As Long
BasePriority As Long
UniqueProcessIdAs Long 'Red Line
InheritedFromUniqueProcess Id As Long 'ParentProcessID'
End Type
Public Declare Function NtQueryInformationProcess _
Lib "ntdll" (ByVal ProcessHandle As Long, _
ByVal ProcessInformationClass As Long, _
ByRef ProcessInformation As PROCESS_BASIC_INFORMATION, _
ByVal lProcessInformationLength As Long, _
ByRef lReturnLength As Long) As Long
Public Declare Function EnumProcesses Lib "psapi.dll" (ByRef lpidProcess As Long, ByVal cb As Long, ByRef cbNeeded As Long) As Long
Public Const PROCESS_VM_READ = 16 'Red Line
The Public sub has the same but might be rectified if the above fixed
chestera
Really appreciate your help here
Here is the General Declaration code
'*************************
' Windows API Declarations for ShellExecAndWait
' COPY THESE DECLARATIONS TO THE GENERAL DECLARATIONS SECTION
'*************************
Private Const PROCESS_QUERY_INFORMATION = &H400
Private Const TH32CS_SNAPPROCESS As Long = 2&
Private Const MAX_PATH As Integer = 260
Private Const SW_SHOW As Integer = 5
Private Type PROCESSENTRY32
dwSizeAs Long 'Red line
cntUsage As Long
th32ProcessIDAs Long 'Red line
th32DefaultHeapID As Long
th32ModuleIDAs Long 'Red line
cntThreads As Long
th32ParentProcessID As Long
pcPriClassBaseAs Long 'Red line
dwFlagsAs Long 'Red line
szExeFile As String * MAX_PATH
End Type
Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
Private Declare Function CreateToolhelpSnapshot Lib "kernel32" Alias "CreateToolhelp32Snapshot"
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function ProcessFirst Lib "kernel32" Alias "Process32First" (ByVal hSnapShot As Long, uProcess As PROCESSENTRY32) As Long
Private Declare Function ProcessNext Lib "kernel32" Alias "Process32Next" (ByVal hSnapShot As Long, uProcess As PROCESSENTRY32) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
' Windows Version API Calls '
Type OSVERSIONINFO
dwOSVersionInfoSize As Long
' --------------------------
dwMajorVersionAs Long
dwMinorVersionAs Long 'All
dwBuildNumberAs Long
dwPlatformIdAs Long
szCSDVersionAs String * 128
'-------------------------
End Type
'-------------------------
Public Const VER_PLATFORM_WIN32s = 0
Public Const VER_PLATFORM_WIN32_WINDOWS
Public Const VER_PLATFORM_WIN32_NT = 2
'-------------------------
Private Declare Function GetVersionEx Lib "kernel32" Alias "GetVersionExA" (ByRef lpVersionInformation As OSVERSIONINFO) As Long
Public Type PROCESS_BASIC_INFORMATION
ExitStatus As Long
PebBaseAddressAs Long 'Red Line
AffinityMask As Long
BasePriority As Long
UniqueProcessIdAs Long 'Red Line
InheritedFromUniqueProcess
End Type
Public Declare Function NtQueryInformationProcess _
Lib "ntdll" (ByVal ProcessHandle As Long, _
ByVal ProcessInformationClass As Long, _
ByRef ProcessInformation As PROCESS_BASIC_INFORMATION,
ByVal lProcessInformationLength As Long, _
ByRef lReturnLength As Long) As Long
Public Declare Function EnumProcesses Lib "psapi.dll" (ByRef lpidProcess As Long, ByVal cb As Long, ByRef cbNeeded As Long) As Long
Public Const PROCESS_VM_READ = 16 'Red Line
The Public sub has the same but might be rectified if the above fixed
chestera
dwSizeAs Long 'Red line
...Insert a space before the As
dwSize As Long
-------------------------- -
dwSize is the Name of the the variable
and it is declare as a Long datatype
...Insert a space before the As
dwSize As Long
--------------------------
dwSize is the Name of the the variable
and it is declare as a Long datatype
ASKER
bcunney
Hell didn't notice that most of the red lines had the same error. Here is the last
'------------------------- ---------- -- Start red line
Public Const VER_PLATFORM_WIN32s = 0
Public Const VER_PLATFORM_WIN32_WINDOWS = 1
Public Const VER_PLATFORM_WIN32_NT = 2
'------------------------- ---------- ---End redline
When I did a debug it didn't like the = 0 etc. I removed the = but still got an error
the err msg like "Constants Fix length strings blah blah Not allowed Public member"
chestera
Hell didn't notice that most of the red lines had the same error. Here is the last
'-------------------------
Public Const VER_PLATFORM_WIN32s = 0
Public Const VER_PLATFORM_WIN32_WINDOWS
Public Const VER_PLATFORM_WIN32_NT = 2
'-------------------------
When I did a debug it didn't like the = 0 etc. I removed the = but still got an error
the err msg like "Constants Fix length strings blah blah Not allowed Public member"
chestera
Insert all this declaration code into a new module
Click Modules | New
and paste the code in there
Save the new module
Click Modules | New
and paste the code in there
Save the new module
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
bcunney
Great I will give it a try. It will a while before I get back to you I have to go and visit one of our other departments
chestera
Great I will give it a try. It will a while before I get back to you I have to go and visit one of our other departments
chestera
ASKER
bcunney
Nice try but getting the same results as with other Shell commands
chestera
Nice try but getting the same results as with other Shell commands
chestera
ASKER
bcunney
Ok what I have done so far is play around with the batch file. the Neto32.exe is located in
C:\Program Files\HNEAHS Applications\Scanner\Neto3 2.exe
So in my test.bat I made a Call C:\Program Files\HNEAHS Applications\Scanner\Neto3 2.exe
Still no go
So I moved Neto32.exe to the root directory of the C drive
When I opened the batch file from access Neto32 also opened.
so the batch file can't find C:\Program Files\HNEAHS Applications\Scanner\Neto3 2.exe
Would this have sonething to do with DOS and it's 8 charater file naming convention??
Bottom line is the scanner will only upload data if Neto32 is located in it's present position. Program Files etc
chestera
Neto32 -c:1 -b:19200 -p:0 -d:8 -s:2 -dt:1 -pt:1 -qs:1 -asp:1 -lslf:0
Ok what I have done so far is play around with the batch file. the Neto32.exe is located in
C:\Program Files\HNEAHS Applications\Scanner\Neto3
So in my test.bat I made a Call C:\Program Files\HNEAHS Applications\Scanner\Neto3
Still no go
So I moved Neto32.exe to the root directory of the C drive
When I opened the batch file from access Neto32 also opened.
so the batch file can't find C:\Program Files\HNEAHS Applications\Scanner\Neto3
Would this have sonething to do with DOS and it's 8 charater file naming convention??
Bottom line is the scanner will only upload data if Neto32 is located in it's present position. Program Files etc
chestera
Neto32 -c:1 -b:19200 -p:0 -d:8 -s:2 -dt:1 -pt:1 -qs:1 -asp:1 -lslf:0
ASKER
bcunney
If I put Quotes round the path and it now opens Neto32.exe but wont upload data. Thanks for your help but I think we have gone as far as we can. If you agree I will award the points
chestera
If I put Quotes round the path and it now opens Neto32.exe but wont upload data. Thanks for your help but I think we have gone as far as we can. If you agree I will award the points
chestera
Hi Chestera,
Maybe try a different approach - try and eliminate the need for a .bat file
Try and do everything that the .bat file is doing by direct calls from Acccess VBA
The first line of the .bat file is just deleting a file - This can be done from the VBA
Then you can directly shell Neto32 using ShellExecuteandWait and passing in the necessary parameters
The command string will contain Neto32 and the params
and you should also be able to do the file copy from VBA - It would be worth a shot
Maybe try a different approach - try and eliminate the need for a .bat file
Try and do everything that the .bat file is doing by direct calls from Acccess VBA
The first line of the .bat file is just deleting a file - This can be done from the VBA
Then you can directly shell Neto32 using ShellExecuteandWait and passing in the necessary parameters
The command string will contain Neto32 and the params
and you should also be able to do the file copy from VBA - It would be worth a shot
ASKER
bcunnney
Tried running Neto32 seperately it just opens and does nothing. Deleting and coping no probs. I can Shell Neto32 not sure how to pass the parameters after that. I have learned heaps from this problem.
chestera
Tried running Neto32 seperately it just opens and does nothing. Deleting and coping no probs. I can Shell Neto32 not sure how to pass the parameters after that. I have learned heaps from this problem.
chestera
ExecuteAndWait "Neto32 -c:1 -b:19200 -p:0 -d:8 -s:2 -dt:1 -pt:1 -qs:1 -asp:1 -lslf:0"
ASKER
bcunney
I have finally got it working with the following
from access
Path = "C:\Program Files\HNEAHS Applications\Scanner\RRetu rns.bat"
ShellExecute Me.hwnd, "open", Path, "", "C:\", 1
Batch file
if exist c:\R_data.fil del c:\R_data.fil
Call "C:\Program Files\HNEAHS Applications\Scanner\Neto3 2.exe" -c:1 -b:19200 -p:0 -d:8 -s:2 -dt:1 -pt:1 -qs:1 -asp:1 -lslf:0
rem Port1,(2N/A), 19200, n,8, s, Overwrite, Single run, Quit, Start Protocol on startup, no log file
:loop
echo "Waiting"
if not exist c:\R_data.fil goto loop
copy c:\R_data.fil c:\scanner_data\Returns.cs v /Y
rem pause
All I have done here is but a Call to Neto32 and put quotes round the path. I think we can now put it to rest.
chestera
I have finally got it working with the following
from access
Path = "C:\Program Files\HNEAHS Applications\Scanner\RRetu
ShellExecute Me.hwnd, "open", Path, "", "C:\", 1
Batch file
if exist c:\R_data.fil del c:\R_data.fil
Call "C:\Program Files\HNEAHS Applications\Scanner\Neto3
rem Port1,(2N/A), 19200, n,8, s, Overwrite, Single run, Quit, Start Protocol on startup, no log file
:loop
echo "Waiting"
if not exist c:\R_data.fil goto loop
copy c:\R_data.fil c:\scanner_data\Returns.cs
rem pause
All I have done here is but a Call to Neto32 and put quotes round the path. I think we can now put it to rest.
chestera
Well done chestera
Glad to hear you got it working
Glad to hear you got it working
ASKER
bcunney
Thank you for your patience.
all the best
chestera
Thank you for your patience.
all the best
chestera
See this link which details what you need to insert in module to use Shellexute
http://www.vbaccelerator.com/codelib/shell/shellex.htm
If you have any questions on the ode specifics, please get back to me.