ADF Scanner in Access

[alihussain]

Hi All
how to modify this code to scan from a feeder, scan multiple documents, and convert jpg to pdf?
Any assistance you can give would be GREATLY appreciated.

Option Compare Database
Public PathOfFile As String
Private Const WIA_Format_JPEG As String = "{B96B3CAE-0728-11D3-9D7B-0000F81EF32E}"

Public Function MyScan()

On Error Resume Next
  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
  Dim wiaScanner As WIA.Device
  Dim PicFullName
  Dim PicAdd1 As Integer
 
 
 
 
          
        If Len(Dir(PathOfFile & Forms![Form1]![IDD], vbDirectory)) = 0 Then
            MkDir PathOfFile & Forms![Form1]![IDD]
        End If

  PicFullName = PathOfFile & Forms![Form1]![IDD] & "\" & Forms![Form1]![IDD] & "-" & Format(Now, "dd-mm-yyyy_hh-nn-ss") & ".jpg"
  Set ComDialog = New WIA.CommonDialog
     Set wiaScanner = ComDialog.ShowSelectDevice(WiaDeviceType.UnspecifiedDeviceType, False, True)

    
 
    Set DevMgr = New WIA.DeviceManager
    For i = 1 To DevMgr.DeviceInfos().Count
      If DevMgr.DeviceInfos(i).DeviceID = wiaScanner.DeviceID Then
        Set DevInfo = DevMgr.DeviceInfos(i)
      End If
     Next i
    Set dev = DevInfo.Connect
    Set img = dev.Items(1).Transfer(WIA_Format_JPEG)
    img.SaveFile PicFullName

 
 
  Set img = Nothing
  Set dev = Nothing

 

[June7]

What's wrong with code? Is jpeg file saved?

Did you search topic of conversion? Here is one https://www.mrexcel.com/board/threads/vba-code-to-convert-jpg-to-pdf-and-other-conversions.1051640/

 

[alihussain]

@June7 thx for ur help,
jpg file is saved yes and I made a default location in partition D and PathOfFile hold this location and about the topic of conversion i tried the code the problem is this"sheet1.Activate"i got this error after i run it " Run- time error '424' : object required" and i tried about adding this for to get scanning from scanner feeder >> "wiaScanner.Properties("3088").Value = 1" or is there a better solution?

 

[June7]

That code may be Excel VBA. Running from Access would require modifying for Excel automation. I got this to work:

Sub JPG_PDF(strFile)
Dim xL As Excel.Application, wb As Excel.Workbook
Set xL = CreateObject("Excel.Application")
Set wb = xL.Workbooks.add
xL.Visible = True

'Declare variables
Dim file
Dim path As String

path = "C:\Users\Owner\June\Forums\"
file = Dir(path & strFile)

wb.Worksheets("Sheet1").Activate
With ActiveSheet
    'Insert picture into Excel
    .Pictures.Insert (path & file)
    .Pictures(ActiveSheet.Pictures.Count).name = "A Picture"
    .ExportAsFixedFormat Type:=xlTypePDF, FileName:=path & "test.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
'    .Shapes.Range(Array("A Picture")).Delete
End With
End Sub

When Delete line is active, image fails to export. So probably need DoEvents or Sleep code https://www.fmsinc.com/MicrosoftAccess/modules/examples/AvoidDoEvents.asp

Another option is to load image into Image control on Access report and save report as PDF. OutputTo command can save report to PDF.

 

[shadow9449]

Another option is to load image into Image control on Access report and save report as PDF. OutputTo command can save report to PDF.

I've done that and it almost works, with just one problem. Access reports where the content of the report goes too close to the margin will force a second page on SOME printers. In other words, if I want to size the image control to pretty much fill the page, it will look great on some printers and then I'll get complaints that it's creating a second blank page when exported to the PDF. I've looked extensively about exporting just Page 1 of a report to PDF but to no avail.

The reality is that people will be scanning documents that have margins less than 1/2". If I just scale the image control smaller than that to accommodate, the PDF will be noticeably smaller than the original.

Thank you

 

[alihussain]

@shadow9449 how to update the report? when I scan for the first time its good but when I try scan the 2nd document the pdf file contains the first scanned document, not the 2nd.

 

[shadow9449]

@alihussain : I have the report open in preview mode. The report has code in it that sets the source of the image to the scanned .jpg. It then exports the report as a PDF

 

[alihussain]

well im still having this problem with reports, my code is:

    Dim reportName As String
    reportName = "name of the report"
    Dim fileName As String
    fileName = "PathOfFile which contain the path of the scanned image"
    DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
    DoCmd.Close acReport, reportName, acSaveNo

so I created a table that holds the path of the images and query which will get the last path (last image scanned ), (DLast("Path","images")), and the report only got image control and the control source is path.
The report wont update to the next scanned image how can I solve this ?

 

[June7]

DLast() and DFirst() are unreliable. You need the most recent record by a date or sequence ID. Then open report filtered to that record.

Does report have Image control bound to Path field?

 

[alihussain]

for ur Q : Does report have Image control bound to Path field? yes its

 

[alihussain]

Thx guys for ur help i solve the report problem but one thing I hope u guys help me with which is the ADF scanner if I have ADF scanner, how to scan the papers from the feeder, not from flatbed or scan from both ???

 

[June7]

So essentially you want to set a stack of documents into ADF, have code feed 1 sheet to scan and save with specified name, repeat until end of stack. I suspect that is not possible. Review https://stackoverflow.com/questions/17008480/scan-multiple-pages-with-adf-scanner-using-vba

I have used code to designate printer bin but never tried to address ADF.