1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254
| Option Compare Database
'This code was originally designed by http://kbase.icbconsulting.com/vba/scan-documents-into-an-access-database
'Details: This code will continually scan up to 10 documents using a scanner with an Automatic Document Feeder(ADF) and then export the jpeg
'images to (1) pdf file.
'Code tested using an HP OfficeJet 6600 Wireless All-in-one Printer.
'Requirements:
'Must include reference to Microsoft Windows Image Acquisition 2.0 dll
'Create a table named scantemp. Create ID column as Autonumber. Create 2nd column named Picture with Text as datatype.
'Create a continuous report named rptscan. Set scantemp table as recordsource. Add image control to report and set Picture
'as the control source. Make the image control the size of an 8.5 x 11 sheet so that the whole document appears normally when the
'report is exported to pdf.
'For use with a scanner that continually scans documents until the ADF tray is empty.
'NOTE: I previosuly coded this to scan up to 20 documents at once. It would always get to the 11th or 12th page and then Access 2010
'would crash (Not Responding). It would be interesting to see if someone can come up with a way to scan more that 10 documents
'with this code.
Public Sub ScanDocs()
'ErrorHandler traps feeder empty error after all documents are scanned, then begins jpeg-to-pdf file conversion
On Error GoTo ErrorHandler
'Initial Document Load into scanner
If MsgBox("Set documents (Max. 10) in the Automatic Document Feeder and then click OK.", vbOKCancel, "Scan Start") = vbCancel Then
MsgBox ("Scan Canceled")
GoTo ProcedureExit
Else
GoTo ScanStart
End If
ScanStart:
'Setup WIA imaging device for scanning
Dim Dialog1 As New WIA.CommonDialog, dpi As Integer, PP As Integer, l As Integer
dpi = 300
Dim Scanner As WIA.Device
Set Scanner = Dialog1.ShowSelectDevice(WIA.WiaDeviceType.ScannerDeviceType, False, False)
'Set Document Properties and Feeder Setup
Scanner.Properties("3088").Value = 1 'Automatic Document Feeder
Scanner.Items(1).Properties("6146").Value = 4 'Colour intent
Scanner.Items(1).Properties("6147").Value = dpi 'DPI horizontal
Scanner.Items(1).Properties("6148").Value = dpi 'DPI vertical
Scanner.Items(1).Properties("6149").Value = 0 'x point to start scan
Scanner.Items(1).Properties("6150").Value = 0 'y point to start scan
Scanner.Items(1).Properties("6151").Value = 8.5 * dpi 'Horizontal extent
Scanner.Items(1).Properties("6152").Value = 11# * dpi 'Vertical extent for letter
Scanner.Items(1).Properties("6154").Value = -30 'brightness
Scanner.Items(1).Properties("6155").Value = 30 'contrast
'Start first page scan
Dim intPages As Integer
Dim img As WIA.ImageFile
Set img = Scanner.Items(1).Transfer("{B96B3CAE-0728-11D3-9D7B-0000F81EF32E}")
Dim strFileJPG As String
strFileJPG = "c:\Essai\1.jpg"
img.SaveFile (strFileJPG)
intPages = 1
'Then every subsequent scan thereafter
Dim img2 As WIA.ImageFile
Set img2 = Scanner.Items(1).Transfer("{B96B3CAE-0728-11D3-9D7B-0000F81EF32E}")
Dim strFileJPG2 As String
strFileJPG2 = "c:\Essai\2.jpg"
img2.SaveFile (strFileJPG2)
intPages = intPages + 1
Dim img3 As WIA.ImageFile
Set img3 = Scanner.Items(1).Transfer("{B96B3CAE-0728-11D3-9D7B-0000F81EF32E}")
Dim strFileJPG3 As String
strFileJPG3 = "c:\Essai\3.jpg"
img3.SaveFile (strFileJPG3)
intPages = intPages + 1
Dim img4 As WIA.ImageFile
Set img4 = Scanner.Items(1).Transfer("{B96B3CAE-0728-11D3-9D7B-0000F81EF32E}")
Dim strFileJPG4 As String
strFileJPG4 = "c:\Essai\4.jpg"
img4.SaveFile (strFileJPG4)
intPages = intPages + 1
Dim img5 As WIA.ImageFile
Set img5 = Scanner.Items(1).Transfer("{B96B3CAE-0728-11D3-9D7B-0000F81EF32E}")
Dim strFileJPG5 As String
strFileJPG5 = "c:\Essai\5.jpg"
img5.SaveFile (strFileJPG5)
intPages = intPages + 1
Dim img6 As WIA.ImageFile
Set img6 = Scanner.Items(1).Transfer("{B96B3CAE-0728-11D3-9D7B-0000F81EF32E}")
Dim strFileJPG6 As String
strFileJPG6 = "c:\Essai\6.jpg"
img6.SaveFile (strFileJPG6)
intPages = intPages + 1
Dim img7 As WIA.ImageFile
Set img7 = Scanner.Items(1).Transfer("{B96B3CAE-0728-11D3-9D7B-0000F81EF32E}")
Dim strFileJPG7 As String
strFileJPG7 = "c:\Essai\7.jpg"
img7.SaveFile (strFileJPG7)
intPages = intPages + 1
Dim img8 As WIA.ImageFile
Set img8 = Scanner.Items(1).Transfer("{B96B3CAE-0728-11D3-9D7B-0000F81EF32E}")
Dim strFileJPG8 As String
strFileJPG8 = "c:\Essai\8.jpg"
img8.SaveFile (strFileJPG8)
intPages = intPages + 1
Dim img9 As WIA.ImageFile
Set img9 = Scanner.Items(1).Transfer("{B96B3CAE-0728-11D3-9D7B-0000F81EF32E}")
Dim strFileJPG9 As String
strFileJPG9 = "c:\Essai\9.jpg"
img9.SaveFile (strFileJPG9)
intPages = intPages + 1
Dim img10 As WIA.ImageFile
Set img10 = Scanner.Items(1).Transfer("{B96B3CAE-0728-11D3-9D7B-0000F81EF32E}")
Dim strFileJPG10 As String
strFileJPG10 = "c:\Essai\10.jpg"
img10.SaveFile (strFileJPG10)
intPages = intPages + 1
'Starts the jpeg-to-pdf conversion
StartPDFConversion:
Dim strFilePDF As String
'set pdf output path
strFilePDF = "c:\Essai\pdf.pdf"
DoCmd.SetWarnings False
'delete previously processed images from scantemp table
DoCmd.RunSQL "delete from scantemp"
'insert all newly scanned images into scantemp table
DoCmd.RunSQL "insert into scantemp (picture) values ('" & strFileJPG & "')"
If intPages >= 2 Then
DoCmd.RunSQL "insert into scantemp (picture) values ('" & strFileJPG2 & "')"
End If
If intPages >= 3 Then
DoCmd.RunSQL "insert into scantemp (picture) values ('" & strFileJPG3 & "')"
End If
If intPages >= 4 Then
DoCmd.RunSQL "insert into scantemp (picture) values ('" & strFileJPG4 & "')"
End If
If intPages >= 5 Then
DoCmd.RunSQL "insert into scantemp (picture) values ('" & strFileJPG5 & "')"
End If
If intPages >= 6 Then
DoCmd.RunSQL "insert into scantemp (picture) values ('" & strFileJPG6 & "')"
End If
If intPages >= 7 Then
DoCmd.RunSQL "insert into scantemp (picture) values ('" & strFileJPG7 & "')"
End If
If intPages >= 8 Then
DoCmd.RunSQL "insert into scantemp (picture) values ('" & strFileJPG8 & "')"
End If
If intPages >= 9 Then
DoCmd.RunSQL "insert into scantemp (picture) values ('" & strFileJPG9 & "')"
End If
If intPages >= 10 Then
DoCmd.RunSQL "insert into scantemp (picture) values ('" & strFileJPG10 & "')"
End If
'output rptscan to predefined file path
Dim RptName As String
RptName = "rptScan"
DoCmd.OpenReport RptName, acViewDesign, , , acHidden
DoCmd.Close acReport, RptName, acSaveYes
DoCmd.OutputTo acOutputReport, RptName, acFormatPDF, strFilePDF
'delete all jpeg files after report output
Dim fso46 As New FileSystemObject
fso46.DeleteFile strFileJPG
If intPages = 2 Then
fso46.DeleteFile strFileJPG2
ElseIf intPages = 3 Then
fso46.DeleteFile strFileJPG2
fso46.DeleteFile strFileJPG3
ElseIf intPages = 4 Then
fso46.DeleteFile strFileJPG2
fso46.DeleteFile strFileJPG3
fso46.DeleteFile strFileJPG4
ElseIf intPages = 5 Then
fso46.DeleteFile strFileJPG2
fso46.DeleteFile strFileJPG3
fso46.DeleteFile strFileJPG4
fso46.DeleteFile strFileJPG5
ElseIf intPages = 6 Then
fso46.DeleteFile strFileJPG2
fso46.DeleteFile strFileJPG3
fso46.DeleteFile strFileJPG4
fso46.DeleteFile strFileJPG5
fso46.DeleteFile strFileJPG6
ElseIf intPages = 7 Then
fso46.DeleteFile strFileJPG2
fso46.DeleteFile strFileJPG3
fso46.DeleteFile strFileJPG4
fso46.DeleteFile strFileJPG5
fso46.DeleteFile strFileJPG6
fso46.DeleteFile strFileJPG7
ElseIf intPages = 8 Then
fso46.DeleteFile strFileJPG2
fso46.DeleteFile strFileJPG3
fso46.DeleteFile strFileJPG4
fso46.DeleteFile strFileJPG5
fso46.DeleteFile strFileJPG6
fso46.DeleteFile strFileJPG7
fso46.DeleteFile strFileJPG8
ElseIf intPages = 9 Then
fso46.DeleteFile strFileJPG2
fso46.DeleteFile strFileJPG3
fso46.DeleteFile strFileJPG4
fso46.DeleteFile strFileJPG5
fso46.DeleteFile strFileJPG6
fso46.DeleteFile strFileJPG7
fso46.DeleteFile strFileJPG8
fso46.DeleteFile strFileJPG9
ElseIf intPages = 10 Then
fso46.DeleteFile strFileJPG2
fso46.DeleteFile strFileJPG3
fso46.DeleteFile strFileJPG4
fso46.DeleteFile strFileJPG5
fso46.DeleteFile strFileJPG6
fso46.DeleteFile strFileJPG7
fso46.DeleteFile strFileJPG8
fso46.DeleteFile strFileJPG9
fso46.DeleteFile strFileJPG10
End If
Set fso46 = Nothing
DoCmd.SetWarnings True
MsgBox ("Done!")
ProcedureExit:
Exit Sub
ErrorHandler:
'Traps 'out of paper error.' Asks user if all documents were scanned properly, if yes is chosen, start PDF conversion, if no, restarts
'scan subroutine.
Select Case Err.Number
Case -2145320957
If MsgBox("Were all documents successfully scanned?", vbYesNo, "Confirm Scan") = vbYes Then
GoTo StartPDFConversion
Else
MsgBox "OK"
End If
End Select
'Handles any other errors in subroutine
MsgBox "Error" & ": " & Err.Number & vbCrLf & "Description: " _
& Err.Description, vbExclamation, Name & ".ScanDocs"
Resume ProcedureExit
End Sub |
Partager