## Part 2: a VB.NET Version of this Project

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.

## File Batching

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.

### FileBatch.vb

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

dbCatalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath)

objFirstTable.Name = "FileBatch"
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


### References

http://www.4guysfromrolla.com/webtech/013101-1.2.shtml

AttachmentSize
Form1.vb.txt343 bytes
Form1.Designer.vb.txt2.24 KB
FileBatch.vb.txt2.49 KB
filebatcher.jpg8.02 KB