After a while, it became obvious that there was no way to drive the ArcMap application from Excel -- timeouts from errors wouldn't get handled, so bad runs would hang.
A real app could raise errors on timeouts, so, I had to learn VB OLE programming. Fortunately there's a free version of VB called VB Express Edition. It's a complete VB environment, that uses .NET. Unfortunately, there aren't references for the old VB classes included. .NET is, in parts, a bit more complex than VB - it's a victim of feature-itis. There are also fewer VB.NET tutorials out there.
Here's a diagram of the "new" system, which is, mostly, going to be an iteration of the "old"system.
The app is broken into three parts. One part manages a list of files. One part is a bunch of "scripts" that do the actual work of analyzing, copying, and deleting files. One part is a scheduler that will run the scripts only at specified times, so that it won't interrupt the normal workday.
This code fits into the larger goal of a project that will reliably run an application on a set of files, over the course of several nights.
The first thing I've written, so far, is something that will scan the file system for file names, to create a "batch". The batch is stored in a Microsoft Access .mdb file.
The coolest feature is that you don't need Access to run it. It creates the .mdb file from scratch, and inserts data into it.
Another cool feature is the call to System.IO.Directory.GetFiles. That does all the scanning that, in the original project, required custom code.
This is very alpha code, but, it might help someone out there.
Imports System.Data Imports system.Data.SqlClient Public Class FileBatch Private Const StatusNone = 0 Private Const StatusProcessed = 1 Private Const StatusSkip = 2 Private Sub CreateNewDatabase(ByVal dbPath As String) ' delete the file first If System.IO.File.Exists(dbPath) = True Then System.IO.File.Delete(dbPath) End If Dim dbCatalog As New ADOX.Catalog() dbCatalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath) Dim objFirstTable As New ADOX.Table() objFirstTable.Name = "FileBatch" objFirstTable.Columns.Append("File", ADOX.DataTypeEnum.adLongVarWChar, 1024) objFirstTable.Columns.Append("DestinationFile", ADOX.DataTypeEnum.adLongVarWChar, 1024) objFirstTable.Columns.Append("Status", ADOX.DataTypeEnum.adInteger) objFirstTable.Columns.Append("ProcessingDate", ADOX.DataTypeEnum.adDate) objFirstTable.Columns.Append("Comment", ADOX.DataTypeEnum.adVarWChar, 255) objFirstTable.Keys.Append("PK_File", 1, "File") dbCatalog.Tables.Append(objFirstTable) 'cleanup dbCatalog = Nothing objFirstTable = Nothing End Sub Public Function CreateBatch(ByVal dbPath As String, _ ByVal pathStart As String, _ ByVal ext As String, _ Optional ByVal statusBox As TextBox = Nothing) Dim ar, element CreateNewDatabase(dbPath) If statusBox IsNot Nothing Then statusBox.Text = "Scanning for *." & ext & " in " & pathStart & "." statusBox.Refresh() End If ar = System.IO.Directory.GetFiles(pathStart, "*." & ext, IO.SearchOption.AllDirectories) Dim cs Dim conn As OleDb.OleDbConnection Dim command As OleDb.OleDbCommand Dim sql As String cs = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath conn = New OleDb.OleDbConnection(cs) conn.Open() For Each element In ar sql = "INSERT INTO FileBatch (File,DestinationFile,Status,ProcessingDate,Comment) VALUES ('" _ & element & "','',0,'1/1/1899','')" ' Console.WriteLine(sql) command = New OleDb.OleDbCommand() With command .Connection = conn .CommandText = sql .ExecuteNonQuery() .Dispose() End With Next conn.Close() CreateBatch = 1 End Function End Class
Here's the code that calls it (from a form button):
Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim fb As FileBatch fb = New FileBatch fb.CreateBatch("C:\tmp\text.mdb", "C:\Documents and Settings\johnkuser\", "jpg", Me.StatusMessage) Close() End Sub