vb Code:
Public Function openIndexFile(sheetName As String) As Boolean DoEvents Dim wb1 As Workbook Dim wb2 As Workbook Dim sh As Object Dim sfilename As String Dim temp As String Dim shtnext As Variant Dim found As Boolean Set wb1 = ThisWorkbook For Each shtnext In Sheets If shtnext.name = sheetName Then Sheets(sheetName).Visible = True Application.DisplayAlerts = False Sheets(sheetName).Delete Exit For End If Next shtnext sfilename = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", Title:="N-INDEX") If sfilename = "False" Then Exit Function Set wb2 = Workbooks.Open(FileName:=sfilename) On Error Resume Next Set sh = wb2.Sheets(sheetName) On Error GoTo 0 If sh Is Nothing Then wb2.Close SaveChanges:=False MsgBox ("No " & sheetName & " was found in that file.") openIndexFile = False Exit Function End If On Error Resume Next Application.DisplayAlerts = False wb1.Sheets(sheetName).Delete On Error GoTo 0 sh.Copy After:=wb1.Sheets(wb1.Sheets.count) wb2.Close False Set wb2 = Nothing wb1.Sheets(sheetName).Visible = xlVeryHidden wb1.Sheets("Driver").Activate Application.DisplayAlerts = True openIndexFile = True Set wb1 = Nothing Set sh = Nothing End Function




Reply With Quote