Private Sub cmdSelectXLSFile_Click()
'Requires reference to Microsoft Office 12.0 Object Library.
Dim fDialog As Office.FileDialog 'Declares variable
Dim varfile As Variant 'Variant type variable that will store the selected file path
Dim myCurrentDir As String
'Dialog box to open to users Desktop
myCurrentDir = "C:\Documents and Settings\" & Environ("username") & "\Desktop\"
'Set up the File Dialog.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker) 'Instantiates the variable creating a filepicker object using late binding
With fDialog
.AllowMultiSelect = False 'Does not allow selecting more than one file
.InitialFileName = myCurrentDir 'Open the users desktop folder
.Title = "Please Select Your Grid Supply Demand Analysis File." 'Set the file dialog title
.Filters.Clear 'Clears the file dialog file type existing filters
.Filters.Add "Excel", "*.xls" 'This file dialog will only allow the selection of .xls files
.Show
If .SelectedItems.Count = 1 Then 'A file was selected
'**************************************************************************************************Import Excel Data Start
'Clear the existing table
CurrentDb.Execute "DELETE * FROM tbl_ImportedGridSupplyDemandAnalysis", dbFailOnError
'Import from selected .xls file to tbl_ImportedGridSupplyDemandAnalysis table
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tbl_ImportedGridSupplyDemandAnalysis", .SelectedItems(1), True, "A3:J205"
'**************************************************************************************************Cleanup Imported Data Start
'Delete not needed rows in tbl_ImportedGridSupplyDemandAnalysis
Call RemoveOffendingRows
'Delete not needed phrases in tbl_ImportedGridSupplyDemandAnalysis
Call RemoveOffendingPhrases
'Trims spaces in front of Discription and at the end in tbl_ImportedGridSupplyDemandAnalysis
Call LTrimRTrimLeadingSpace
'**************************************************************************************************Import Excel Criteria Start
'Clear the existing info in table tbl_ImportDemandAnalysisCriteriaUsed
CurrentDb.Execute "DELETE * FROM tbl_ImportDemandAnalysisCriteriaUsed", dbFailOnError
'Import from selected .xls files row to tbl_ImportDemandAnalysisCriteriaUsed table
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tbl_ImportDemandAnalysisCriteriaUsed", .SelectedItems(1), False, "A1:A1"
'Give completed message
'MsgBox ("The selected file: " & .SelectedItems(1) & " was successfully imported!")
MsgBox ("The selected file was successfully imported!"), vbInformation, "Import Results"
'Open the report to print
DoCmd.OpenReport "rpt_GridSupplyDemandAnalysis", acViewPreview, "", "", acWindowNormal
Me.Visible = False ' Hide the switchboard
Else 'No file was selected
MsgBox ("No file was selected."), vbInformation, "No File"
End If
End With
End Sub