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