Here’s an email I got from someone using CmdTwain from Excel:
Hi,
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.
Michel
'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:
'http://www.gssezisoft/go.php?i=CmdTwain
'
'Just copy the text running MS-Excel under:
'>>>Tool\Macro\VisualBasicEditor\VBAProjects\MicrosoftExcelObjects\Sheets1
'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 = ""
Else
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
Loop
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
Disclaimer
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.
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?
Thanks,
Q
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.
Greg