I have the following that nicely checks whether the workbook is open, opens it. thenchecks for a particular worksheet. If it exists, it does give me the debug message I put in. However, it if does not exist, it stops ath the worksheet add line with a subscript out of range error and I do not understand why.
Public Sub cmdRefreshActiveRatingData_Click()
'First check to see if the file is open and if not, then open. Then check to see if the
'worksheet DataSource exists in the workbook and if not, create it. Then query the
'ActiveMasterTempate DataSource worksheet to bring in the Rating data
Dim wks As Worksheet
strFileExportPathSumm = ThisWorkbook.Worksheets("Settings").Range("FOLDER_PATH_SUMMARY") ' Summary Reports/General Folder, Range name on Settings worksheet, Settings!$I$5
If Not fntIsWbkOpen("TEST_ClientActiveMasterTemplate.xls") Then
Application.Workbooks.Open (strFileExportPathSumm & "TEST_ClientActiveMasterTemplate.xls")
If fntDoesSheetExist(Workbooks("TEST_ClientActiveMasterTemplate.xls"), "DataSource") Then
MsgBox "Yes, wks exists.", vbOKOnly
MsgBox "No', wks does not exit.", vbOK
'Get Subscript ouf of range here???
Workbooks("TEST_ClientActiveMasterTemlate.xls").Worksheets.Add().Name = "DataSource"