Link to home
Start Free TrialLog in
Avatar of chestera
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.csv /Y
rem pause


My Shell command is

Shell = "C:\Program Files\HNEAHS Applications\Scanner\Returns.bat"

any help appreciated

chestera
Avatar of Barry Cunney
Barry Cunney
Flag of Ireland image

Try the ShellExecute instead of Shell
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.
Avatar of chestera
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
Another way you could try is the following:

dim shell
set shell=createobject("wscript.shell")
shell.run "batchfile.bat"
set shell=nothing

Open in new window

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
bcunney

Dim Shell
Set Shell = CreateObject("wscript.Shell")
Shell.Run "C:\Program Files\HNEAHS Applications\Scanner\Returns.bat"
Set Shell = Nothing

getting error  "Method 'Run' of Object Iwshshell3 failed

chestera
bcunney

Got the ShellEx function working. Now getting the same errorwhen run  as with your other suggestion
"Method 'Run' of Object Iwshshell3 failed

chestera
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
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\Returns.bat", essSW_SHOWNORMAL, , , , Me.hwnd

chestera
Hi Chestera,
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
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.csv /Y
rem pause

A third party programed the scanner it used some obscure C language

chestera
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
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
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
Hi Chestera
Post up th red lines and I will help you
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
    InheritedFromUniqueProcessId 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
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  
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
Insert all  this declaration code into a new module
Click Modules | New  
and paste the code in there

Save the new module
ASKER CERTIFIED SOLUTION
Avatar of Barry Cunney
Barry Cunney
Flag of Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
bcunney

Nice try but getting the same results as with other Shell commands

chestera
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\Neto32.exe
So in my test.bat I made a  Call C:\Program Files\HNEAHS Applications\Scanner\Neto32.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\Neto32.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
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
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
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
ExecuteAndWait "Neto32 -c:1 -b:19200 -p:0 -d:8 -s:2 -dt:1 -pt:1 -qs:1 -asp:1 -lslf:0"
      
bcunney

I have finally got it working with the following

from access
Path = "C:\Program Files\HNEAHS Applications\Scanner\RReturns.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\Neto32.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.csv /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
Well done chestera
Glad to hear you got it working
bcunney

Thank you for your patience.

all the best

chestera