Using CmdTwain from Excel

Here’s an email I got from someone using CmdTwain from Excel:


First of all, let me thank you for the free CmdTwain !

I’m just trying to use it within Excel to scan, store and have a complete list of all that has been scanned without the need to wait on the scanner to be able to type in the filename, like most other software is doing. This speeds up the work without making mistakes by already looking to the next page to be scanned and then having to type in the things from the previous scan.

I tried almost every software, even the sharewares, but none did it as it should do the job and certainly not making a list of all the scannings and still having each scan automaticly saved to 1 file with the important things in the name of the file.

I’ve attached the first coding which is working for me now and may be distributed also for free.



'Program code for VBA for Excel
'It will scan, save and link documents in the spreadsheet.
'Filenames made up from data out of other cells on the same row.
'Automaticly adds the hyperlink to that file on the same row.
'Date: 20-feb-2008 Belgium.   MichelR-be(at)gmail(dot)com
'Thanks to the free scanner command-TWAIN program from GssSoftware:
'Just copy the text running MS-Excel under:
'Then add the column name 'scan' on the first row and other columns like Firm, Date, EURO, ...
'Scanner will start and add the link to the scanned document
'on the row where you have entered the number 1 in the column 'scan'
Private Sub Worksheet_Change(ByVal oTarget As Range)
  If UCase(oTarget.EntireColumn.Rows(1).Value) <> "SCAN" Then Exit Sub 'Only start when in the column "SCAN"
  If oTarget.Count <> 1 Then Exit Sub 'stop when more then 1 cell changes at ones
  If Not IsNumeric(oTarget.Value) Or oTarget.Value = 0 Then Exit Sub 'only scan when there is a number been entered
    vScanPrg = "C:\Program Files\GssSoftware\CmdTwain\CmdTwain.exe /PAPER=A4 /DPI=200 /JPG25"
  Set oFSO = CreateObject("Scripting.FileSystemObject") 'to have diskaccess
  vFolder = ThisWorkbook.Path & "\ScanDocs" 'Folder to save the documents
  vFileExt = ".jpg"
  If Not oFSO.FolderExists(vFolder) Then oFSO.CreateFolder (vFolder)
  vFile = oTarget.EntireColumn.Rows(1).NoteText 'filename set-up hidden in comment on top row
  'getting and checking the filename
  vError = ""
  Do 'enter how the filenames should be made up from values out of other columns
     If vFile = "" Then
        vFile = InputBox(vError & Chr(13) _
        & "Enter the filename for the scans:" & Chr(13) _
        & "use:  * " & Chr(13) _
        & "example: myscans   EUR", "SAVE SCANS TO")
        If vFile = "" Then Exit Sub
        oTarget.EntireColumn.Rows(1).NoteText vFile
        oTarget.EntireColumn.Rows(1).Comment.Shape.TextFrame.AutoSize = True
     End If
     If InStr(vFile, "\") Or InStr(vFile, "/") Or InStr(vFile, "?") Or InStr(vFile, "*") _
     Or InStr(vFile, ".") Or InStr(vFile, ":") Or InStr(vFile, "|") Or InStr(vFile, Chr(34)) Then
        vError = "Don't include a file-extension and \ / ? * " & Chr(34) & " : . |  can't be used !"
        vFile = ""
     End If
  Loop Until vFile <> ""
  Do 'change all columnnames between < and > with the values in those columns
     vLoc = InStr(1, vFile, "<")
     vLen = InStr(vLoc, vFile, ">") - vLoc - 1
     vFind = Mid(vFile, vLoc + 1, vLen)
     Set oFound = ActiveSheet.Rows(1).Find(vFind, LookIn:=xlValues, LookAt:=xlWhole)
     If IsEmpty(oFound) Then ' if the columnname isn't been found
        vFound = ""
        vFound = oFound.EntireColumn.Rows(oTarget.Row).Value 'take the value from the column
     End If
     If IsDate(vFound) Then vFound = Format(vFound, "YYYY-MM-DD") 'change date-format
     vFile = Left(vFile, vLoc - 1) & vFound & Mid(vFile, vLoc + vLen + 2)
  Loop Until InStr(1, vFile, "<") = 0
  If vFile = "" Then vFile = "ScanDoc"
  vExtra = ""
  Do 'enter an unique number to the file if it already exist
     If Not oFSO.FileExists(vFolder & vFile & vExtra & vFileExt) Then Exit Do
     vExtra = Val(vExtra) + 1
  vFile = vFile & vExtra & vFileExt
  Application.EnableEvents = False 'stop trigering this routine when adding the document-link to the sheet
  oTarget.Formula = "=hyperlink(" & Chr(34) & vFolder & "\" & vFile & Chr(34) & "," & Chr(34) & vFile & Chr(34) & ")"
  Application.EnableEvents = True
  ' now start the dos CmdTwain.exe from GssSoftware to scan-to-file
  Call Shell(vScanPrg & " " & Chr(34) & vFolder & "\" & vFile & Chr(34), vbMinimizedNoFocus)
  ' while scanning one can keep on typing into the Excel-sheet...
End Sub 



The example provided here is free of charge but it is also without warranty of any kind. Users are responsible for determining its suitability or otherwise for their particular needs. Neither the authors, nor GssEziSoft, accept liability for any problems arising out of the use of the example program.

Download the Example

Right click and Save the example code.

Download CmdTwain

You can also download CmdTwain for free.

2 thoughts on “Using CmdTwain from Excel”

  1. Hi,
    I’ve benntrying use the vba code for excel, but since I’m a novice at this I can’t seem to make it work. Can you help me?

    1. You’re right – it doesn’t work with the current version of CmdTwain.

      The code was developed a while ago and not me. That said, it is a pretty neat bit of software (kudos to the original author) and most of the problems you are experiencing ARE my fault.

      The current install path for CmdTwain has changed and is now “C:\Program Files\Gss EziSoft\CmdTwain Free\”. As this path now includes spaces, the executable bit of the command needs quoting so you get

      vScanPrg ="""path\CmdTwain.exe"" /args"

      When I copied and pasted the code from the website it wrapped one of the lines without a proper VBA continuation character.

      Also when I ran it, the program complained because I didn’t have any < characters in the filename I entered. This is a really neat feature which allows you to type values into the spreadsheet and the program will use those values in the filename. An example:

      If you have a column called From
      You enter details on a row and include “joe” in the “from” column.
      You type 1 in the Scan column and it asks for a filename.
      You type “<from> – scan”
      It scans the document and saves it as “joe – scan.jpg”.

      The original program assumes that you have at least one < in the filename. I have modified it so that you can use them or not.

      You can download a sample spreadsheet with the macro in it to scan documents into Excel.

      I hope this solves your problem. I have run it in Excel 2003. I haven’t tried it in 2007 or 2010 but it should work in those too.


Leave a Reply

Your email address will not be published. Required fields are marked *