Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


WIA with Access VBA slow

WIA with Access VBA slow

WIA with Access VBA slow

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

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

'after finish scan start convert to pdf 

Any help would be appreciated.

RE: WIA with Access VBA slow

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close