Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to tell if sheet exists in closed workbook
I need to find out if a worksheet named Sheet1 exists in a closed workbook.
A search of news groups suggest you can use ADO to find out sheet names in closed workbooks but I don't need to know the names. I just need to know if Sheet1 exists in a workbook. If it doesn't exist, that workbook is ignored. I have other validation code to screen for improper data on closed workbooks when the name is Sheet1 which is a pretty common name so that part is not a problem. My code for reading data from closed workbooks works fine until a workbook is found that doesn't have a sheet named Sheet1. Then Excel puts up a list of sheet names for the workbook. I haven't coded for this, don't know how and don't want to and even though it creates an error eventually that I handle, it thoroughly confuses Users. I'd sure like to find some way to query the closed workbook to see if Sheet1 exist. An error returned on a workbook without Sheet1 is fine since that can be handled. Ideas would be very greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to tell if sheet exists in closed workbook
This should do the job:
Function SheetExistsInClosedWB(strFolder As String, _ strFile As String, _ strSheet As String) As Long 'will return: '-1 if file doesn't exist '-2 if sheet doesn't exist '0 if both file and sheet exist '------------------------------- Dim strSep As String Dim strArgs As String Dim strResult As String On Error GoTo ERROROUT strSep = "\" If Right$(strFolder, 1) < strSep Then strFolder = strFolder & strSep End If 'see if the file exists '---------------------- If bFileExists(strFolder & strFile) = False Then SheetExistsInClosedWB = -1 Exit Function End If 'create the argument '------------------- strArgs = "'" & strFolder & "[" & strFile & "]" & strSheet & "'!" & _ Range("A1").Address(, , xlR1C1) 'execute the Excel4 macro and test for the sheet '---------------------------------------------- strResult = ExecuteExcel4Macro(strArgs) Exit Function ERROROUT: SheetExistsInClosedWB = -2 End Function Function bFileExists(strFile As String) As Boolean Dim lAttr As Long On Error Resume Next lAttr = GetAttr(strFile) bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0) On Error GoTo 0 End Function Sub test() MsgBox SheetExistsInClosedWB("C:\ExcelFiles", "AA13.xls", "Sheet1") End Sub RBS "Jim" wrote in message ... I need to find out if a worksheet named Sheet1 exists in a closed workbook. A search of news groups suggest you can use ADO to find out sheet names in closed workbooks but I don't need to know the names. I just need to know if Sheet1 exists in a workbook. If it doesn't exist, that workbook is ignored. I have other validation code to screen for improper data on closed workbooks when the name is Sheet1 which is a pretty common name so that part is not a problem. My code for reading data from closed workbooks works fine until a workbook is found that doesn't have a sheet named Sheet1. Then Excel puts up a list of sheet names for the workbook. I haven't coded for this, don't know how and don't want to and even though it creates an error eventually that I handle, it thoroughly confuses Users. I'd sure like to find some way to query the closed workbook to see if Sheet1 exist. An error returned on a workbook without Sheet1 is fine since that can be handled. Ideas would be very greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to tell if sheet exists in closed workbook
Wow! This works great. I don't completely understand "strResult =
ExecuteExcel4Macro(strArgs)" part but have looked it up on the web and am beginning to get the gist of it. This is exactly what I need. Thank you very much. I greatly appreciate it. "RB Smissaert" wrote in message ... This should do the job: Function SheetExistsInClosedWB(strFolder As String, _ strFile As String, _ strSheet As String) As Long 'will return: '-1 if file doesn't exist '-2 if sheet doesn't exist '0 if both file and sheet exist '------------------------------- Dim strSep As String Dim strArgs As String Dim strResult As String On Error GoTo ERROROUT strSep = "\" If Right$(strFolder, 1) < strSep Then strFolder = strFolder & strSep End If 'see if the file exists '---------------------- If bFileExists(strFolder & strFile) = False Then SheetExistsInClosedWB = -1 Exit Function End If 'create the argument '------------------- strArgs = "'" & strFolder & "[" & strFile & "]" & strSheet & "'!" & _ Range("A1").Address(, , xlR1C1) 'execute the Excel4 macro and test for the sheet '---------------------------------------------- strResult = ExecuteExcel4Macro(strArgs) Exit Function ERROROUT: SheetExistsInClosedWB = -2 End Function Function bFileExists(strFile As String) As Boolean Dim lAttr As Long On Error Resume Next lAttr = GetAttr(strFile) bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0) On Error GoTo 0 End Function Sub test() MsgBox SheetExistsInClosedWB("C:\ExcelFiles", "AA13.xls", "Sheet1") End Sub RBS "Jim" wrote in message ... I need to find out if a worksheet named Sheet1 exists in a closed workbook. A search of news groups suggest you can use ADO to find out sheet names in closed workbooks but I don't need to know the names. I just need to know if Sheet1 exists in a workbook. If it doesn't exist, that workbook is ignored. I have other validation code to screen for improper data on closed workbooks when the name is Sheet1 which is a pretty common name so that part is not a problem. My code for reading data from closed workbooks works fine until a workbook is found that doesn't have a sheet named Sheet1. Then Excel puts up a list of sheet names for the workbook. I haven't coded for this, don't know how and don't want to and even though it creates an error eventually that I handle, it thoroughly confuses Users. I'd sure like to find some way to query the closed workbook to see if Sheet1 exist. An error returned on a workbook without Sheet1 is fine since that can be handled. Ideas would be very greatly appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to tell if sheet exists in closed workbook
No trouble; it was a little alteration to code I had to get values from a
closed workbook. RBS "Jim" wrote in message . net... Wow! This works great. I don't completely understand "strResult = ExecuteExcel4Macro(strArgs)" part but have looked it up on the web and am beginning to get the gist of it. This is exactly what I need. Thank you very much. I greatly appreciate it. "RB Smissaert" wrote in message ... This should do the job: Function SheetExistsInClosedWB(strFolder As String, _ strFile As String, _ strSheet As String) As Long 'will return: '-1 if file doesn't exist '-2 if sheet doesn't exist '0 if both file and sheet exist '------------------------------- Dim strSep As String Dim strArgs As String Dim strResult As String On Error GoTo ERROROUT strSep = "\" If Right$(strFolder, 1) < strSep Then strFolder = strFolder & strSep End If 'see if the file exists '---------------------- If bFileExists(strFolder & strFile) = False Then SheetExistsInClosedWB = -1 Exit Function End If 'create the argument '------------------- strArgs = "'" & strFolder & "[" & strFile & "]" & strSheet & "'!" & _ Range("A1").Address(, , xlR1C1) 'execute the Excel4 macro and test for the sheet '---------------------------------------------- strResult = ExecuteExcel4Macro(strArgs) Exit Function ERROROUT: SheetExistsInClosedWB = -2 End Function Function bFileExists(strFile As String) As Boolean Dim lAttr As Long On Error Resume Next lAttr = GetAttr(strFile) bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0) On Error GoTo 0 End Function Sub test() MsgBox SheetExistsInClosedWB("C:\ExcelFiles", "AA13.xls", "Sheet1") End Sub RBS "Jim" wrote in message ... I need to find out if a worksheet named Sheet1 exists in a closed workbook. A search of news groups suggest you can use ADO to find out sheet names in closed workbooks but I don't need to know the names. I just need to know if Sheet1 exists in a workbook. If it doesn't exist, that workbook is ignored. I have other validation code to screen for improper data on closed workbooks when the name is Sheet1 which is a pretty common name so that part is not a problem. My code for reading data from closed workbooks works fine until a workbook is found that doesn't have a sheet named Sheet1. Then Excel puts up a list of sheet names for the workbook. I haven't coded for this, don't know how and don't want to and even though it creates an error eventually that I handle, it thoroughly confuses Users. I'd sure like to find some way to query the closed workbook to see if Sheet1 exist. An error returned on a workbook without Sheet1 is fine since that can be handled. Ideas would be very greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving Sheet to Closed Workbook | Excel Discussion (Misc queries) | |||
Help - How to check if value exists in closed workbook? | Excel Programming | |||
Check if sheet exists in a closed workbook | Excel Programming | |||
Finding the last row in a sheet in a closed workbook. | Excel Programming | |||
Import Sheet from Closed Workbook | Excel Programming |