Solved

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

Posted on 2007-11-30
30
677 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
  • 18
  • 12
30 Comments
 
LVL 17

Expert Comment

by:Barry Cunney
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:chestera
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi Chestera,
Go to Tools | References and make a reference to Microsoft Scripting Runtime
0
 
LVL 17

Expert Comment

by:Barry Cunney
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi Chestera
Post up th red lines and I will help you
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:chestera
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
bcunney

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

chestera
0
 

Author Comment

by:chestera
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Well done chestera
Glad to hear you got it working
0
 

Author Comment

by:chestera
Comment Utility
bcunney

Thank you for your patience.

all the best

chestera
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

744 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now