A reader commented on my email last week asking about the code. So here’s some nuts and bolts in reference to the article: A Reusable Recipe for Importing and Exporting Text Files in Access.

The user interface is a form with 2 buttons and 2 text boxes:

1. btnSelectFile – opens dialog for user to select a file and puts the path into txtFileName
2. txtFileName – Contains a file that was tested to make sure it existed.
3. btnConvert – starts the conversion process which runs and then reports any problems or instructions on using the output in the txtStatus box.
4. txtStatus – is updated with messages during the conversion process.

So what is my code behind the initial user form? Here it is:

Option Compare Database 
Option Explicit 

Private Sub Form_Open(Cancel As Integer) 
   Me.txtFileName =  "Select a File"
   Me.btnConvert.Enabled = False 
End Sub 

Private Sub btnSelectFile_Click() 
   Me.txtFileName = AppProcedures.GetRawFileName 
   If Me.txtFileName =  "" Then Me.txtFileName =  "Canceled Selection, try Step 1 again" Else btnConvert.Enabled = True 
   Me.txtStatus =  ""
End Sub 

Private Sub btnConvert_Click() 
   Dim SaveName As String,Customer As iImportFile 
   If Not HALFile.FileExists(Me.txtFileName) Then 
      Me.txtStatus =  "File selected does not exist, please try selecting the file again"
      GoTo Exit_Sub 
   End If 
   Me.txtStatus =  "Processing..." & vbCrLf 
   Set Customer = AppProcedures.GetRawEDICustomer(Me.txtFileName) 
   If Customer Is Nothing Then 
      Me.txtStatus =  "The file was not recognized as a type this program is able to process"
      GoTo Exit_Sub 
   Else 
      Me.txtStatus = Customer.CustomerName() &  " detected" & vbCrLf 
      Me.Repaint 
   End If 
   AppProcedures.ConvertRawEDIFileToAccessImportFile Me.txtFileName,Customer 
   Me.txtStatus = Me.txtStatus &  "Finished"
Exit_Sub: 
End Sub 

So when the form initially opens, it initializes the controls to tell the user to select a file and disables the conversion button.

After the user clicks on the btnSelectFile button, the code runs a helper function which returns the full path to the file using system file dialogs.

Once an existing file has been selected, the user can then click on the btnConvert button which will start attempting to do the conversion.

1. The btnConvert_Click function checks for the existence of the file before continuing (in case it was deleted or network connection lost since the first selection step was completed).
2. Then the routine populates the Customer variable with the appropriate class that implements an iImportFile interface using the helper function GetRawEDICustomer which ultimately is passing the file to the class which checks to see if it is a particular type of file. (more on this in a subsequent email)
3. Finally, if the file is determined to be of a type that can be imported, then the form uses another helper function to process the file.

So in the next email, I will start breaking down more of the code of these helper functions.