WIA with Access VBA slow

[dvirgint]

I have a macro running in MS Access 2013 which was created to scan documents from an HP Scanjet Enterprise Flow 7500 scanner, save the results as a PDF and automatically email the file to someone else in our organization.

The macro itself runs as it should, however compared to the HP software which comes with the scanner, the scanning portion of the macro takes much longer. With the HP software, the time to scan 23 pages was about 30 seconds. With the macro, I had about 3-4 sheets scanned in that time. Please find below the code I'm using to control the scanner. Does anyone see anything that could be improved or changed to increase the speed of execution?

Const WIA_FORMAT_JPEG = "{B96B3CAE-0728-11D3-9D7B-0000F81EF32E}"

Private Sub cmdOK_Click()

Dim intPages                            As Integer 'number of pages
Dim img                                 As wia.ImageFile
Dim strPath                             As String
Dim strPathImg                          As String
Dim strFileJPG                          As String
Dim txt_id                              As String
Dim strRPTScan                          As String

    strPath = "H:\Scan\" 'set path to save files
    strPathImg = "H:\Scan\Images\"
    intPages = 1

    On Error GoTo ErrorHandler

'scan
ScanStart:
Dim DialogScan                          As New wia.CommonDialog
Dim DPI                                 As Integer
Dim PP                                  As Integer
Dim l                                   As Integer
Dim Scanner                             As wia.Device
Dim intVerticalExtent                   As Integer
Dim intOneTwoSided                      As Integer

    Set Scanner = DialogScan.ShowSelectDevice(wia.WiaDeviceType.ScannerDeviceType, False, False)

    'Set page length
    Select Case fraPaperFormat
            Case 1
                    intVerticalExtent = 1650
                    strRPTScan = "rptScan11"
            Case 2
                    intVerticalExtent = 2100
                    strRPTScan = "rptScan14"
    End Select
    'Set single or two-sided scanning
    Select Case fraSingleTwoSided
            Case 1
                    intOneTwoSided = 1
            Case 2
                    intOneTwoSided = 5
    End Select

    'Set scanner properties depending on userform letter format values
    With Scanner
            .Properties("3088").Value = intOneTwoSided                              'determined above
            .Items(1).Properties("Horizontal Resolution").Value = 150
            .Items(1).Properties("Vertical Resolution").Value = 150
            .Items(1).Properties("6149").Value = 0          'x point to start scan
            .Items(1).Properties("6150").Value = 0  'y point to start scan
            .Items(1).Properties("Horizontal Extent").Value = 1275
            .Items(1).Properties("Vertical Extent").Value = intVerticalExtent       'determined above
    End With

    'Start Scan if err number -2145320957 Scan document finish
    Do While Err.Number <> -2145320957 'error number is ADF status don't feed document
            On Error GoTo here
            Set img = Scanner.Items(1).Transfer(WIA_FORMAT_JPEG)
            strFileJPG = strPathImg & CStr(intPages) & ".jpg"
            img.SaveFile (strFileJPG) 'save files .jpg in temp folder
            DoCmd.SetWarnings False
            DoCmd.RunSQL "insert into scantemp (picture) values ('" & strFileJPG & "')" 'insert picture temp to table scan temp
            intPages = intPages + 1 'add number pages
here:
    Loop

'after finish scan start convert to pdf

Any help would be appreciated.

 

[Andrzejek]

The best way I've found to find a 'bottle neck' in my code is to step thru the code and see which line(s) of code takes the most of time to execute.


Have fun.

---- Andy

There is a great need for a sarcasm font.