UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

2 Pages V   1 2 >  
Reply to this topicStart new topic
> Ms Document Scanning: Where To Start, Office 2007    
 
   
RexRivers
post Aug 17 2011, 02:09 PM
Post #1

UtterAccess Addict
Posts: 164
From: Hamilton, NJ USA



I'm trying to find info on MS Document Scanning. I'm trying to automate scanning from Access 2007 VBA.
Most searches just lead me to MS Document Imaging. These are two separate entities in my computer's programs list though.
The only reference I see in my VBA References List is for MSDI.
Can anyone point me in the direction of some code or even where to start working with these programs?
Thanks!
Go to the top of the page
 
+
jleach
post Aug 17 2011, 03:08 PM
Post #2

UtterAccess Editor
Posts: 8,166
From: Staten Island, NY, USA



Hi,
Someone may already have something ready-built, but I probably what you're going to end up having to do is a lot of work with the Windows API in order to communicate with the hardware drivers and perform the required tasks. If it's anything like the old Printing API (before the Printer object came around in 2002), it's a real nightmare.
A quick search of "Scanner Win32 API" landed me at the MSDN base page for Windows Image Acquisition. If you're API savvy you might be able to work your way through it. At the bottom of the page is the Reference link: "Information on WIA interfaces, methods, objects, and data types used in C/C++ and scripting." - where you'll find all the documentation on what to use and how.
Good luck! If you think it's something you want to get into I wouldn't mind trailing along and seeing if I can help out (I like API work), but I think it's going to be a process and I haven't had the time I usually have lately.
Again, someone may already have something ready built.
Cheers,
Go to the top of the page
 
+
RexRivers
post Aug 17 2011, 03:51 PM
Post #3

UtterAccess Addict
Posts: 164
From: Hamilton, NJ USA



I've never worked with API before, but I don't have a deadline so it looks like a good project to work on if I can't find something out there already. I'm always interested in learning new things. I will keep you posted of my progress.
Thank you for the link. Definitely a good place to start.
Go to the top of the page
 
+
jleach
post Aug 17 2011, 07:26 PM
Post #4

UtterAccess Editor
Posts: 8,166
From: Staten Island, NY, USA



It looks like we won't have to use the API after all.
For non C/C++ languages, the WIA Automation Layer is provided (for XP SP1 and newer clients and Server 2003 an newer servers).
You should see in your list of references Microsoft Windows Image Aquisition Library 2.0.
I just started playing around with a few minutes ago, but it looks like the Object Listing page is where you'll be spending a fair amount of time. It seems that you create a DeviceManager object, read/select a Device, check the Devices Items, work with the Item to transfer data, etc etc. So far I've done nothing but return a list of imaging devices and a few properties of them.
Also of note is the Shared Samples. Pretty handy.
Lots of digesting.
Cheers,
Go to the top of the page
 
+
jleach
post Aug 17 2011, 08:05 PM
Post #5

UtterAccess Editor
Posts: 8,166
From: Staten Island, NY, USA



I thought it'd be a lot more difficult than this. Check it out:
!--c1-->
CODE
Public Function WIATest()
  Dim comDialog As WIA.CommonDialog
  Dim img As WIA.ImageFile
  
  Set comDialog = New WIA.CommonDialog
  Set img = New WIA.ImageFile
  
  Set img = comDialog.ShowAcquireImage()
  
  img.SaveFile ("D:\imgfile.bmp")
  
  Set img = Nothing
  
End Function

Opens a dialog to scan an image, click Scan, code waits until the scan is complete, saves the scanned image as directed.
Pretty cool. This is on a machine with one scanner (one image device connected). Presumably with more, you'd have to select the appropriate device. This of course doesn't include any detail settings, etc etc, and isn't 100% transparent to the user as the ShowAquireImage method shows a dialog where they need to Preview/Scan or Cancel (per my device, that's what the options are - not sure if this is OS driven or Device driven).
By checking the img.FileExtension I saw the default to be a .bmp, so that's what I attempted to save it as. Lo and behold, there sits the file.
Pretty cool.
edit: Apparently WIA is the "replacement" for the TWAIN interface (which is what I was grimacing about with all the advanced API work). It's been around for a while, but still it may be well to make sure that all the devices you plan to use support WIA.
Is there a particular task/process you wish to employ? Myself, I'm going to see if I can't work on scanning on a button click, with no dialog, and save as a specified image type (not a bmp fan). Let me know and I'll see what else I can come up with while playing around.
Go to the top of the page
 
+
RexRivers
post Aug 17 2011, 09:28 PM
Post #6

UtterAccess Addict
Posts: 164
From: Hamilton, NJ USA



Wow! I actually wish I was at work right now! That doesn't happen every day.
just build the Db a month ago to hold all the info I need for tracking Regulated Medical Waste my company transports. Each DEP tracking form has a unique number at the top of it that I use as the primary key. I was scanning the form one at a time and renaming each file with that number.
Earlier today I noticed the MSDS and MSDI programs and thought I'd give it a shot. Not only did it scan the form, the OCR was able to pick out the Form Number and name the TIFF file it created with that number which is exactly what I wanted.
Basically I want to be able to click a button and have it scan for me instead of the manual way which involves a lot more clicking and typing.
Now that I see it's not so difficult I can see a lot of possibilities with this. Thank you so much!
Go to the top of the page
 
+
jleach
post Aug 17 2011, 09:42 PM
Post #7

UtterAccess Editor
Posts: 8,166
From: Staten Island, NY, USA



I came up with the following, which is (almost) complete transparent to the user, with the exception of there being a wait while the scan is in progress (I don't see a way around this except to wrap the thing in a COM dll and send it off on it's own process).
!--c1-->
CODE
Option Compare Database
Option Explicit
Const DEVNAME As String = "EPSON NX210/TX210"
Const WIA_FORMAT_JPEG = "{B96B3CAE-0728-11D3-9D7B-0000F81EF32E}"
Public Function WIATest2(Optional bShowProgress As Boolean = True)
  Dim ComDialog As WIA.CommonDialog
  Dim DevMgr As WIA.DeviceManager
  Dim DevInfo As WIA.DeviceInfo
  Dim dev As WIA.Device
  Dim img As WIA.ImageFile
  Dim i As Integer
  
  Set DevMgr = New WIA.DeviceManager
  
  For i = 1 To DevMgr.DeviceInfos().Count
    If DevMgr.DeviceInfos(i).Properties("Name") = DEVNAME Then
      Set DevInfo = DevMgr.DeviceInfos(i)
    End If
  Next i
  
  Set dev = DevInfo.Connect
  
  'I'm not positive on this, but it appears that Item(1) is the root item
  'of the device.  The only scanner I have at hand only has one item anyway,
  'AFAICT other items might include tray feeders, etc.  Not sure, more importantly
  'not sure why Item(1) is the one to use or how safe it is to rely on...
  If bShowProgress Then
    Set ComDialog = New WIA.CommonDialog
    Set img = ComDialog.ShowTransfer(dev.Items(1), WIA_FORMAT_JPEG, True)
  Else
    Set img = dev.Items(1).Transfer(WIA_FORMAT_JPEG)
  End If
  
  img.SaveFile "D:\img7.jpg"
  
  Set img = Nothing
  Set dev = Nothing
  Set DevInfo = Nothing
  Set DevMgr = Nothing
  Set ComDialog = Nothing
  
  
End Function

Obviously we can adjust the image output name. The only issue I'm having at the moment is trying to adjust the properties of the imagefile before it's saved... preferable to a smaller more managable size. I can think of a few ways to maybe handle it after the fact, but as of now I'm still drawing some blanks on how to handle it through WIA.
Indeed, I was quite surprised and pleased with this as well. I'll certainly find a use for it... I've always hated the interface for scanners, and now I can scan and send/store/attach to a record in one click. One of those things that I always thought would be great but never persued because I thought it would be very difficult... so thanks for asking!
I'll post more as I find more, but for the time being I'm going to call it night.
Cheers,
Go to the top of the page
 
+
RexRivers
post Aug 29 2011, 03:54 PM
Post #8

UtterAccess Addict
Posts: 164
From: Hamilton, NJ USA



I'm finally back in the office and am trying this out, but am getting an error right off the bat.
im ComDialog As WIA.CommonDialog
Complie Error:
User-defined type not defined
Oassume I'm missing a Reference. If that's the case, do you know what I may need. I don't see any WIA or Common Dialog references in my list of refs. Is it called something else?
Go to the top of the page
 
+
jleach
post Aug 29 2011, 06:34 PM
Post #9

UtterAccess Editor
Posts: 8,166
From: Staten Island, NY, USA



In bold in this post: http://www.utteraccess.com/forum/index.php...t&p=2147931
Go to the top of the page
 
+
RexRivers
post Aug 30 2011, 12:32 AM
Post #10

UtterAccess Addict
Posts: 164
From: Hamilton, NJ USA



Oh geez, sorry about that. Thanks.
got an error, but fixed it.
The device I'm using is called "HP 6500 (network)"
For some reason when the code is looping through the devices it wasn't finding it. So I'd get an error at Set dev = DevInfo.connect
After stepping through I noticed the code was picking up the scanner as "HP 6500 (NET)"
Not sure if that's important or not. I just changed DEVNAME to "HP 6500 (NET)" and now it works.
its not scanning the entire image for some reason so I have to mess around with it a little. But I am in complete awe that you were able to figure all that out in an afternoon. Thank you so much!
Go to the top of the page
 
+
jleach
post Aug 30 2011, 03:42 AM
Post #11

UtterAccess Editor
Posts: 8,166
From: Staten Island, NY, USA



I think in my last post that had code I was listing all the properties... I had tried changing the Vertical and Horizontal Extent properties and had succeeded in shrinking the image down to the pixels sizes I entered.
really didn't get much further than that, and don't doubt things tend to vary from one device mgfr to the next. I've also noticed that you need to have a relatively new scanner that recognizes WIA - apparently a handfull within the last 5 or 6 years or so are still TWAIN only.
>> I am in complete awe that you were able to figure all that out in an afternoon <<
Me too! Unfortunately I had to drop the project for more pressing things, but it's definately in my list of things to get back to as soon as I can.
Cheers,
Go to the top of the page
 
+
RexRivers
post Aug 30 2011, 03:25 PM
Post #12

UtterAccess Addict
Posts: 164
From: Hamilton, NJ USA



I'm trying to convert the jpg that is created to a pdf. Perhaps you can clue me in on a problem. I found a piece of code that is close to what I need and I've tweaked it little for my needs, but I there's a problem with it. Here's part of the code
CODE
Dim a
Dim my_pdfMaker As AdobePDFMakerForOffice.pdfMaker
Dim pdfSettings As AdobePDFMakerForOffice.ISettings
et my_pdfMaker = Nothing ' locate PDFMaker object
For Each a In Application.COMAddIns
If InStr(UCase(a.Description), "PDFMAKER") > 0 Then
Set my_pdfMaker = a.Object '<---NOT WORKING
Exit For
End If
Next
If my_pdfMaker Is Nothing Then
MsgBox "Cannot Find PDFMaker add-in", vbOKOnly, ""
Exit Sub
End If

should I be using something other than .Object? I don't get an error, but the my_pdfMaker does not set to anything. Stepping through, the code does go through that line, but it doesn't set for some reason.
Any clues?
Go to the top of the page
 
+
jleach
post Aug 30 2011, 03:38 PM
Post #13

UtterAccess Editor
Posts: 8,166
From: Staten Island, NY, USA



It seems as thought it should work, but I don't see how that code is converting anything.
Try removing the .Object, and just setting a
CODE
Set my_pdfMaker = a
Go to the top of the page
 
+
RexRivers
post Aug 30 2011, 03:41 PM
Post #14

UtterAccess Addict
Posts: 164
From: Hamilton, NJ USA



type mismatch
Go to the top of the page
 
+
jleach
post Aug 30 2011, 03:51 PM
Post #15

UtterAccess Editor
Posts: 8,166
From: Staten Island, NY, USA



I'm not familiar with looping COM addins like that. Usually you would declare the variable and instaitate it:
!--c1-->
CODE
Dim my_pdfMaker As AdobePDFMakerForOffice.pdfMaker
Dim pdfSettings As AdobePDFMakerForOffice.ISettings
Set my_pdfMaker = New AdobePDFMakerForOffice.pdfMaker
Set pdfSettings = my_pdfMaker.Settings 'or whatever

Not sure, won't have a bit to look into it until later, maybe tomorrow
Go to the top of the page
 
+
jleach
post Aug 30 2011, 07:05 PM
Post #16

UtterAccess Editor
Posts: 8,166
From: Staten Island, NY, USA



Is PDFMaker a "codable" object that is distributed with the intent to use from VBA (or another COM language)? From what I'm seeing, it seems that PDF Maker is an addin for the Office suite that is used to make pdfs, but through their UI rather than programmatically.
I'm finding it quite difficult to find any information on the object, and the code you found suggests that it's an addin rather than a control. Adobe doesn't seem to be giving out any information on it either - in fact one (informal) reference says that he may have recalled some lawsuit based due to reverse engineering the COM addin.
Generally object model information is readily available, even for lesser known objects - formal and informal. The ins and outs of this PDFMaker though - even the MVPs responding in other discussions are saying yea, it'd be nice if Adobe gave up the model so it could be used, and the little pieces that some people have squeezed out don't seem to be doing a lot of good.
ref: http://groups.google.com/group/microsoft.p...2e664b877d7c689
Go to the top of the page
 
+
RexRivers
post Aug 31 2011, 09:31 AM
Post #17

UtterAccess Addict
Posts: 164
From: Hamilton, NJ USA



I found a post on Adobe's website that actually has the code I had picked up elsewhere. http://forums.adobe.com/thread/286431
From all the other topics I'm seeing, from the releases of Acrobat 7 to current releases the Object has been removed rendering AdobePDFMakerForOffice basically useless.
Oh well. Thanks again for looking into it. Perhaps I'll stumble upon a workaround.
Go to the top of the page
 
+
jleach
post Aug 31 2011, 10:02 AM
Post #18

UtterAccess Editor
Posts: 8,166
From: Staten Island, NY, USA



I can't say enough good things about Adobe (absolutely saturated with scarasm)
Take a look at the second reply in that link... I suspect the link that the poster had referenced was to some terms or other statement from Adobe saying that this isn't allowed, although it's a dead link at this point, but seems to go in hand with adobe's stance on it and the other smiliar mentions elsewhere.
Unfortunately, I can't even find a jpg -> pdf (programmatic) converter, short of printing the image to the adobe printer (if you happen to have Acrobat that works), and even then in order to do it without having to choose the file save name etc from the standard dialog is a major, major task. Stephen Lebans has a module that convers reports to PDF, which works by outputting the report to a snapshot file, then converting the snapshot, so I looked for jpg -> snapshot converters as well, with even less luck than jpg -> pdf.
I never really knew why devlopers tend to hate working with PDFs until fairly recently as they're extremely popular on the user end, but if you try programming with them, you're bound to be pulling your hair out. I'm not impressed, to say the least - nothing but problems, and reading through the adobe forums with people trying to find answers is depressing. Poor people...
Go to the top of the page
 
+
RexRivers
post Aug 31 2011, 02:53 PM
Post #19

UtterAccess Addict
Posts: 164
From: Hamilton, NJ USA



I did a little workaround that isn't totally automated at this point but does the job I want it to.
Ochanged the file format to bitmap. For some reason Acrobat couldn't open the JPEG format. It will open any other jpg on my computer just not the one created from the function. I also tried a tiff file with the same negative results.
Const WIA_FORMAT_BMP = "{B96B3CAB-0728-11D3-9D7B-0000F81EF32E}"
added strFormNumber as a passed variable. This is the number of the tracking form that I have entered into Access and am now scanning. I use this as the file name.
Dim strFile As String
I added this code to the bottom of the function
CODE
strFile = "C:\Users\John\Pictures\Test_Scan\" & strFormNumber & ".bmp"

img.SaveFile strFile

Shell "C:\Program Files (x86)\Adobe\Acrobat 7.0\Acrobat\Acrobat.exe" + " " + strFile, 3
Kill strFile

Opening the bitmap with acrobat automatically puts it in pdf form. I just close Acrobat and its prompts to save. And bam! I've got my pdf.
The code then deletes the bitmap it created.
Go to the top of the page
 
+
RexRivers
post Sep 9 2011, 01:03 PM
Post #20

UtterAccess Addict
Posts: 164
From: Hamilton, NJ USA



Oooook...so after some more work with this. I got it totally automated.
Ocreated a report with an image object on it. The object is 8" wide x 10.5" high and set all the margins to .125
The control source for this object a field containing a path to the bitmap image created by the function you wrote.
After the report is loaded with this image you can output it as a pdf.
Here is your code with my alterations. There's currently no error trapping.
CODE
Const DEVNAME As String = "HP Officejet 6500 E710n-z (USB)"
Const WIA_FORMAT_BMP = "{B96B3CAB-0728-11D3-9D7B-0000F81EF32E}"
Public Function WIATest2(strFormNumber As String, Optional bShowProgress As Boolean = True)

Dim ComDialog As WIA.CommonDialog
Dim DevMgr As WIA.DeviceManager
Dim DevInfo As WIA.DeviceInfo
Dim dev As WIA.Device
Dim itm As WIA.Item
Dim img As WIA.ImageFile
Dim i As Integer
Dim strFolder As String
Dim strBMP_Path As String
Dim strPDF_Path As String
Dim ChanNum As Long

Set DevMgr = New WIA.DeviceManager

For i = 1 To DevMgr.DeviceInfos().Count
If DevMgr.DeviceInfos(i).Properties("Name") = DEVNAME Then
Set DevInfo = DevMgr.DeviceInfos(i)
Exit For
End If
Next i
Set dev = DevInfo.Connect
If dev Is Nothing Then
MsgBox "Did not connect", vbCritical
Exit Function
End If
Set itm = dev.Items(1)

Set ComDialog = New WIA.CommonDialog
'I'm not positive on this, but it appears that Item(1) is the root item
'of the device. The only scanner I have at hand only has one item anyway,
'AFAICT other items might include tray feeders, etc. Not sure, more importantly
'not sure why Item(1) is the one to use or how safe it is to rely on...
If bShowProgress Then
Set img = ComDialog.ShowTransfer(dev.Items(1), WIA_FORMAT_BMP, True) '
Else
Set img = dev.Items(1).Transfer(WIA_FORMAT_BMP)
End If

strFolder = "C:\Users\John\Documents\Folders\DEP_PDFs\"
strBMP_Path = strFolder & strFormNumber & ".bmp"

img.SaveFile strBMP_Path
' Set path to bmp file in table
UpdatePath strBMP_Path, strFormNumber

DoCmd.OpenReport "rptFormPic", acViewPreview
DoCmd.OutputTo acOutputReport, "", acFormatPDF, Mid(strBMP_Path, 1, Len(strBMP_Path) - 3) & "pdf", True
'Let's close our previewed report
DoCmd.Close acReport, "rptFormPic"
' Give Acrobat a second to open, then close it
' You can add in a loop here to test if Acrobat has
' opened yet before moving past sSleep, just haven't
' bothered with that yet
Call sSleep(1000)
'-------------------------------------------
' http://acrobat-dde.nnn2.com/?p=50
' Close Acrobat without prompts
ChanNum = DDEInitiate("Acroview", "Control")
DDEExecute ChanNum, "[AppExit()]"
'-------------------------------------------
strPDF_Path = Mid(strBMP_Path, 1, Len(strBMP_Path) - 3) & "pdf"
' Set path to pdf file in table
UpdatePath strPDF_Path, strFormNumber
' Delete bitmap image
Kill strBMP_Path

Set img = Nothing
Set dev = Nothing
Set DevInfo = Nothing
Set DevMgr = Nothing
Set ComDialog = Nothing


End Function
Sub UpdatePath(strPath As String, strFormNumber As String)

Dim strUPDATE As String

strUPDATE = "UPDATE tblRMW_PickedUp " & _
"SET RWM_PDF_Path = '" & strPath & "' " & _
"WHERE RMW_FormNumber = '" & strFormNumber & "';"

CurrentDb.Execute (strUPDATE)

End Sub
Go to the top of the page
 
+

2 Pages V   1 2 >
Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 23rd September 2014 - 11:22 AM