That did it! I used the same Left string to call for the import, as well.
I copied that import line from another database I had built a year or so ago and haven't the foggiest why I chose to use "...xml". I pulled it from the code and it all worked like a charm.
I'm posting the final code below for anyone else that might want/need to do the same for their data.
Code:
response = MsgBox("Please confirm import request", vbYesNo, "Confirm Delete")
If response = vbYes Then
DoCmd.CopyObject , "tblDocuments - " & Format(Now(), "dd-mmm-yy"), acTable, "tblDocuments"
DoCmd.RunSQL ("Delete * from tblImport")
DoCmd.RunSQL ("Delete * from tblRptGrp")
'Declare a variable as a FileDialog object.
Dim fd As FileDialog
'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim vrtSelectedItem As Variant
With fd
.AllowMultiSelect = True
.Filters.Clear
.Filters.Add "Master Document Registers", "*.xls?;*.xml", 1
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
Dim wb As Excel.Workbook
Dim xlApp As Excel.Application
Dim wst As Worksheet
Dim wstCt As Long
Dim colWorksheets As Collection
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set colWorksheets = New Collection
Set wb = xlApp.Workbooks.Open(vrtSelectedItem, True, False)
For Each wst In wb.Worksheets
wst.Rows(5).Delete
wst.Rows(4).Delete
wst.Rows(3).Delete
wst.Rows(2).Delete
wst.Rows(1).Delete
Next wst
wb.SaveAs Left(vrtSelectedItem, InStrRev(vrtSelectedItem, ".")) & "xls", xlExcel12
For wstCt = 1 To wb.Worksheets.Count
colWorksheets.Add wb.Worksheets(wstCt).Name
Next wstCt
For wstCt = colWorksheets.Count To 1 Step -1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
"tblImport", Left(vrtSelectedItem, InStrRev(vrtSelectedItem, ".")) & "xls", True, colWorksheets(wstCt) & "$"
Next wstCt
Next vrtSelectedItem
xlApp.Quit
'The user pressed Cancel.
Else: End If
End With
Set fd = Nothing
Else: End If
MsgBox "Import complete.", vbInformation, "Done."