Office 2016 - Excel Macro to Insert Scanned Image direct from Scanner into Worksheet with VBA

Question

Hi All,

I am trying to customise a friends Excel 2016 (64 Bit) to include a macro assigned button to insert a scanned image/document direct from her scanner into the worksheet in a single click. She is disabled and struggles with her hands so a 'single click' function is both desirable and less stressful than working through the usual menu structures saving the image and then inserting as a picture. I have successfully implemented the companion macros into both Word 2016 (64 Bit) and Outlook 2016 (64 Bit) and these function like a charm.

The issue with the Excel Macro appears to be either in the path or the 'Selection.ActiveSheet.Pictures' process as the scanner initialises, the scan is processed (visible in preview window) but fails to insert into the worksheet the highlight remaining boxed around the A1 cell but no image appears :(

I am not especially conversant with VBA and so I have so far been unable to identify which part of the code contains the error preventing the insertion in this case and so resolve the problem. I have consulted both the forum and the wider internet extensively and whilst I was able to adapt the Word and Outlook codes successfully using posts both here and links externally suggested by forum experts I am at a loss as to what is wrong!

This is the adapted code I am using, perhaps someone with greater expertise than myself could either test or suggest how I can modify the code to resolve the problem. (BTW. The end user's scanner documents default to  "D:<Username>\Username's Filing Cabinet\Username's Scanned Documents" in case this is applicable to the issue. Also I have saved the Book1 as a 'Excel Macro Enabled Template' in both the 'customised' Excel template location "D:<Username>\Username's Filing Cabinet\Username's Documents\Office Documents\Excel\Templates" and copied to "C:\Users<Username>\AppData\Roaming\Microsoft\Excel\XLSTART" as suggested in one internet post if that helps).

' Scan for Excel 2016 - 64 Bit

' Based Upon Author: Günter Born www.borncity.de blog.borncity.com

' Implements a Scan function in Excel 2016

Private Declare PtrSafe Function GetTempPath Lib "kernel32" Alias "GetTempPathA" (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Private Function TempPath() As String

  Const MaxPathLen = 256 ' Max length of the path, just as big as possible

  Dim FolderName As String ' Name of the folder

  Dim ReturnVar As Long ' Return Value

  FolderName = String(MaxPathLen, 0)

  ReturnVar = GetTempPath(MaxPathLen, FolderName)

  If ReturnVar <> 0 Then

    TempPath = Left(FolderName, InStr(FolderName, Chr(0)) - 1)

  Else

    TempPath = vbNullString

  End If

End Function

Sub Scan()

'

' Scan Macro, to be invoked in Excel 2016

'

  On Error Resume Next

     Dim objCommonDialog As WIA.CommonDialog

     Dim objImage As WIA.ImageFile

     Dim strDateiname

     ' instantiate Scan WIA objects

     Set objCommonDialog = New WIA.CommonDialog

     Set objImage = objCommonDialog.ShowAcquireImage

    strDateiname = TempPath & "Scan.jpg" ' set temporary file

'     strDateiname = "C:\Users\Public\Pictures" & "Scan.jpg"

     If Not objImage Is Nothing Then

       Kill strDateiname

       objImage.SaveFile strDateiname ' save into temp file

       Selection.ActiveSheet.Pictures strDateiname ' insert into worksheet

       Set objImage = Nothing

     End If

     Set objCommonDialog = Nothing

  '  MsgBox strDateiname  ' test output

End Sub

Many thanks in advance of your kind responses.

Best regards,

PC Pilot

Answer 1

Comment out all of your code and try the sub below.

Andreas.

Sub Scan()

  Dim objCommonDialog As WIA.CommonDialog

  Dim objImage As WIA.ImageFile

  Dim strDateiname As String

  ' instantiate Scan WIA objects

  Set objCommonDialog = New WIA.CommonDialog

  Set objImage = objCommonDialog.ShowAcquireImage

  strDateiname = Environ$("TEMP") & "\Scan.jpg" ' set temporary file

  If Not objImage Is Nothing Then

    If Dir(strDateiname) <> "" Then Kill strDateiname

    objImage.SaveFile strDateiname 'save into temp file

    DoEvents

    ActiveSheet.Shapes.AddPicture _

      strDateiname, False, True, ActiveCell.Left, ActiveCell.Top, -1, -1

  End If

End Sub

Answer 2

All proceeds well with Word and Excel where the resulting templates are saved to the STARTUP folder

(C:\Users&lt;Username>\AppData\Roaming\Microsoft\Word\STARTUP or C:\Users&lt;Username>\AppData\Roaming\Microsoft\Excel\STARTUP) and launch with the program preserving both the buttons and their function. Similarly so for Outlook where upon opening a 'New Message' the VBA is imported via the 'Developer' Tab and the resulting Macro saved as the default .OTM file which again preserves the buttons and function each time Outlook is launched,

I do however have an issue with Publisher & Powerpoint where the saved templates are not opened by default

I'm sorry, that it the wrong way, when you want to have the code available in the application for every file you have to write AddIns.

Excel, Word and Powerpoint supports VBA-AddIns (and COM-AddIns), but Outlook and Publisher only supports COM-AddIns (which can be created with Visual Studio).

https://blogs.msdn.microsoft.com/mcsuksoldev/2010/07/12/building-and-deploying-an-outlook-2010-add-in-part-1-of-2/

And Publisher do not work with templates (in your sense, means a file that can provide the macro).

Here is a simple as possible sample for a VBA-AddIn for Excel:

https://dl.dropboxusercontent.com/u/35239054/SimpleRibbon.xlsm

Andreas.

Answer 3

Hi Andreas,

Thanks for your helpful reply.

If I understand your reply correctly I think that you are suggesting the development of an addin to launch the relevant template containing the Macro in respect of each program? Or, are you suggesting that the Addin performs both the actions of the Macro as well as the launch of the relevant program? 

I suspect that the COM-Addin may well be the global answer here but regret that my technical understanding of this process is zero. Outlook 2010 (with Visual Studio 2010) is the subject of the example you provided the link for, is the process the same with Office 2016 and Visual Studio 2015?

I am keen to expand my knowledge though and attempt to develop a COM-Addin sufficient to launch the relevant template containing the macro. If this is possible simply by following a template I might be able to figure it out but if it requires written code I will most likely soon be out of my depth!!

Best regards,

PC Pilot

Answer 4

For PowerPoint you have to write an AddIn. After that the step to have an AddIn for Word and Excel is very short and easy. See this as demo:

https://dl.dropboxusercontent.com/u/35239054/Document%20Location.zip

Outlook and Publisher only supports COM-AddIns, for that I can't advise.

Publisher doesn't work with templates nor has a Startup-Folder.

Andreas.

Answer 5

Hi Andreas,

I am having some issue with the PowerPoint Add In, I have installed from the 'install' presentation you kindly provided. As noted it is actioned with the F5 key and installed. As the created macro did not invoke the 'scan' function (misunderstanding on my part most likely) I imported the vba file containing the 64 bit scan macro and tested that it worked.

I then started afresh, installed the 'install' presentation that you provided, from developer tab (Visual Basic) I imported the 'scan' macro, off the tools menu I added the 'Microsoft Windows Image Aquisition Library V2.0' from the 'Available References' and re exported and saved the 'scan' vba file. Now I saved the presentation as a Powerpoint Macro-Enabled Template with the intention that this could be re-launched as a 'single click' access to PowerPoint but with the macro enabled buttons enabled and available upon opening.

However the 'scan' macro cease to function once this 'saved template' is reopened :(

If I use the 'install' presentation instead I have to invoke the 'F5' install function which remains as an active slide but otherwise the 'scan' macro enabled buttons now function.

Is there any way in which I can get PowerPoint to open with the addin loading on initial start up of PowerPoint (say to Presentation1) as a clean slide with the 'scan' functions (macro) invoked as part of the automated load?

Apologies for seeking your assistance yet again but have been playing with the issue for a whole week now with no success.

Very best regards,

PC Pilot