Message Box

[Sinfathisar]

Hi All,

I have a question about adding a specific message box to my code. When users click a button on a form, a dialog box (using the FileDialog filepicker) allows users to select multiple excel files to import into a table. The file picker stores those file names and then TransferSpreadsheet imports all the files. I want to add a message box that will pop up after each selected file was imported properly - the kicker is I want the message box to display the filename that was properly imported. This is important because the order in which the files are actually imported is random, and if one of the files is corrupted or formatted incorrectly the code will fail without telling the user which files were actually imported. I want to keep users from accessing the tables, so if they could see a message box that told them which file was just imported, they would know which files were not imported and have a smaller group of files to search through and find the format error.

Here is my code:

Click()
 ' Requires reference to Microsoft Office 11.0 Object Library!!!!!!
   
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
   
   ' Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      ' Set the path to the default folder, which is pulled from project defaults table
      .InitialFileName = DLookup("[Processpath]", "[Project_Defaults]")
      ' Allow user to make multiple selections in dialog box
      .AllowMultiSelect = True
            
      ' Set the title of the dialog box.
      .Title = "Please select one or more files"
      ' Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "Excel Files", "*.xls"
      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If .Show = True Then
         'Loop through each file selected and add it to our list box.
         For Each varFile In .SelectedItems
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Processingfiles", varFile, True
         Next
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With
End Sub

Im pretty sure the message box needs to be added directly after the TransferSpreadsheet command is run, but I have no idea how to get a message box to show the filename in its text each time it pops up....

If anyone has ideas, I would love to hear them...for now I will continue my search for an answer. By the way, I am using Access 2007.

Thanks in advance,
~Jen

We live our lives a circle
And wander where we can
Then after fire and wonder
We end where we began

 

[boblarson]

Just change this part:

         For Each varFile In .SelectedItems
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Processingfiles", varFile, True
         Next

to this:

         For Each varFile In .SelectedItems
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Processingfiles", varFile, True
            [COLOR="Red"]MsgBox "You transferred" & vbCrLf & varFile[/COLOR]
         Next

 

[Sinfathisar]

Sweet! Thank you, that was so simple and exactly what I was looking for...