vb Code:
  1. Public Function openIndexFile(sheetName As String) As Boolean
  2. DoEvents
  3. Dim wb1 As Workbook
  4. Dim wb2 As Workbook
  5. Dim sh As Object
  6.  
  7. Dim sfilename As String
  8. Dim temp As String
  9. Dim shtnext As Variant
  10. Dim found As Boolean
  11.  
  12. Set wb1 = ThisWorkbook
  13.  
  14. For Each shtnext In Sheets
  15.     If shtnext.name = sheetName Then
  16.         Sheets(sheetName).Visible = True
  17.         Application.DisplayAlerts = False
  18.         Sheets(sheetName).Delete
  19.         Exit For
  20.     End If
  21. Next shtnext
  22.    
  23. sfilename = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", Title:="N-INDEX")
  24. If sfilename = "False" Then Exit Function
  25.  
  26. Set wb2 = Workbooks.Open(FileName:=sfilename)
  27.  
  28. On Error Resume Next
  29. Set sh = wb2.Sheets(sheetName)
  30. On Error GoTo 0
  31. If sh Is Nothing Then
  32.     wb2.Close SaveChanges:=False
  33.     MsgBox ("No " & sheetName & " was found in that file.")
  34.     openIndexFile = False
  35.     Exit Function
  36. End If
  37.  
  38.  
  39. On Error Resume Next
  40. Application.DisplayAlerts = False
  41. wb1.Sheets(sheetName).Delete
  42. On Error GoTo 0
  43.  
  44. sh.Copy After:=wb1.Sheets(wb1.Sheets.count)
  45. wb2.Close False
  46. Set wb2 = Nothing
  47.  
  48. wb1.Sheets(sheetName).Visible = xlVeryHidden
  49. wb1.Sheets("Driver").Activate
  50. Application.DisplayAlerts = True
  51. openIndexFile = True
  52. Set wb1 = Nothing
  53. Set sh = Nothing
  54. End Function