Solved

running a batch file from access problem in conjunction with a scanner

Posted on 2007-11-30
30
685 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:chestera
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 18
  • 12
30 Comments
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 20389017
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.
0
 

Author Comment

by:chestera
ID: 20389026
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
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 20389029
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

0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:chestera
ID: 20389107
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
0
 

Author Comment

by:chestera
ID: 20389135
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
0
 

Author Comment

by:chestera
ID: 20389282
bcunney

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

chestera
0
 

Author Comment

by:chestera
ID: 20389331
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
0
 

Author Comment

by:chestera
ID: 20389500
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
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 20390496
Hi Chestera,
Go to Tools | References and make a reference to Microsoft Scripting Runtime
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 20390500
Also create a very simple .bat file with like just an echo command in it and  see iif this works
0
 

Author Comment

by:chestera
ID: 20390716
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
0
 

Author Comment

by:chestera
ID: 20390808
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
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 20391641
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
0
 

Author Comment

by:chestera
ID: 20392103
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
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 20392115
Hi Chestera
Post up th red lines and I will help you
0
 

Author Comment

by:chestera
ID: 20392174
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
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 20392207
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  
0
 

Author Comment

by:chestera
ID: 20392265
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
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 20392375
Insert all  this declaration code into a new module
Click Modules | New  
and paste the code in there

Save the new module
0
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 500 total points
ID: 20392484
Hi Ches,
Try the following code - it's a simpler version of the shell execute and wait
Option Explicit
 
'*******************************
' Type Definition for ExecCmd
'*******************************
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 Function WaitForSingleObject Lib "kernel32" (ByVal _
hHandle As Long, ByVal dwMilliseconds As Long) As Long
 
Private Declare 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
 
Private Declare Function CloseHandle Lib "kernel32" (ByVal _
hObject As Long) As Long
 
Private Const NORMAL_PRIORITY_CLASS = &H20&
Private Const INFINITE = -1&
 
 
 
Public Sub ExecuteAndWait(cmdline$)
Dim proc As PROCESS_INFORMATION
Dim START As STARTUPINFO
Dim ret As Long
 
' Initialize the STARTUPINFO structure:
START.cb = Len(START)
 
' Start the shelled application:
ret = CreateProcessA(0&, cmdline$, 0&, 0&, 1&, _
NORMAL_PRIORITY_CLASS, 0&, 0&, START, proc)
If ret Then
' Wait for the shelled application to finish:
ret = WaitForSingleObject(proc.hProcess, INFINITE)
End If
CloseHandle (proc.hProcess)
End Sub

Open in new window

0
 

Author Comment

by:chestera
ID: 20392547
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
0
 

Author Comment

by:chestera
ID: 20393606
bcunney

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

chestera
0
 

Author Comment

by:chestera
ID: 20393965
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
0
 

Author Comment

by:chestera
ID: 20394009
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
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 20394050
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
0
 

Author Comment

by:chestera
ID: 20394189
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
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 20395489
ExecuteAndWait "Neto32 -c:1 -b:19200 -p:0 -d:8 -s:2 -dt:1 -pt:1 -qs:1 -asp:1 -lslf:0"
      
0
 

Author Comment

by:chestera
ID: 20397983
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
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 20400451
Well done chestera
Glad to hear you got it working
0
 

Author Comment

by:chestera
ID: 20401299
bcunney

Thank you for your patience.

all the best

chestera
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question