How do I test whether a defined sheetname exists in a closed workbook?


Hi...
I've been searching all over and can't find anything on this particula
challenge so I thought you guys might be able to help!

I'm wondering if anyone knows how to test whether (or not) a specifi
sheetname (e.g. "TestSheet") exists in a closed workbook? 

(I'd really prefer not to have to open the workbook if at all possibl
as its one step in an intensive consolidation process from upto 10
workbooks.)

Thx muchly!


-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

0
11/16/2003 8:06:17 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
487 Views

Similar Articles

[PageSpeed] 53

Greg,

Here is a function to return True or False for the supplied file and sheet
name

Function IfSheetExists(fName As String, sh As String) As Boolean

Dim objConn As ADODB.Connection
Dim objCat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim iRow As Long
Dim sConnString As String
Dim sTableName As String
Dim cLength As Integer
Dim iTestPos As Integer
Dim iStartpos As Integer

    IfSheetExists = False

    With ActiveSheet

        sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source=" & fName & ";" & _
                      "Extended Properties=Excel 8.0;"

        Set objConn = New ADODB.Connection
        objConn.Open sConnString
        Set objCat = New ADOX.Catalog
        Set objCat.ActiveConnection = objConn

        iRow = 1
        .lstSheetNames.Clear
        For Each tbl In objCat.Tables
            sTableName = tbl.Name
            cLength = Len(sTableName)
            iTestPos = 0
            iStartpos = 1
                'Worksheet name with embedded spaces are enclosed by single
quotes
            If Left(sTableName, 1) = "'" And Right(sTableName, 1) = "'" Then
                iTestPos = 1
                iStartpos = 2
            End If
                'Worksheet names always end in the "$" character
            If Mid$(sTableName, cLength - iTestPos, 1) = "$" Then
                If sh = Mid$(sTableName, iStartpos, cLength - (iStartpos +
iTestPos)) Then
                    IfSheetExists = True
                    Exit For
                End If
            End If
            Next tbl
    End With

    objConn.Close
    Set objCat = Nothing
    Set objConn = Nothing

End Function




--

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"gpreg" <gpreg.wz92m@excelforum-nospam.com> wrote in message
news:gpreg.wz92m@excelforum-nospam.com...
>
>
> Hi...
> I've been searching all over and can't find anything on this particular
> challenge so I thought you guys might be able to help!
>
> I'm wondering if anyone knows how to test whether (or not) a specific
> sheetname (e.g. "TestSheet") exists in a closed workbook?
>
> (I'd really prefer not to have to open the workbook if at all possible
> as its one step in an intensive consolidation process from upto 100
> workbooks.)
>
> Thx muchly!
>
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
11/16/2003 9:21:01 AM
Greg,

Forgot to mention that you need to set references to the following libraries
Microsoft ADOX Ext n.nn for DDL and Security
Microsoft ACtiveX Data Object Library

as it uses early binding

--

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:ekd35LCrDHA.3616@tk2msftngp13.phx.gbl...
> Greg,
>
> Here is a function to return True or False for the supplied file and sheet
> name
>
> Function IfSheetExists(fName As String, sh As String) As Boolean
>
> Dim objConn As ADODB.Connection
> Dim objCat As ADOX.Catalog
> Dim tbl As ADOX.Table
> Dim iRow As Long
> Dim sConnString As String
> Dim sTableName As String
> Dim cLength As Integer
> Dim iTestPos As Integer
> Dim iStartpos As Integer
>
>     IfSheetExists = False
>
>     With ActiveSheet
>
>         sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>                       "Data Source=" & fName & ";" & _
>                       "Extended Properties=Excel 8.0;"
>
>         Set objConn = New ADODB.Connection
>         objConn.Open sConnString
>         Set objCat = New ADOX.Catalog
>         Set objCat.ActiveConnection = objConn
>
>         iRow = 1
>         .lstSheetNames.Clear
>         For Each tbl In objCat.Tables
>             sTableName = tbl.Name
>             cLength = Len(sTableName)
>             iTestPos = 0
>             iStartpos = 1
>                 'Worksheet name with embedded spaces are enclosed by
single
> quotes
>             If Left(sTableName, 1) = "'" And Right(sTableName, 1) = "'"
Then
>                 iTestPos = 1
>                 iStartpos = 2
>             End If
>                 'Worksheet names always end in the "$" character
>             If Mid$(sTableName, cLength - iTestPos, 1) = "$" Then
>                 If sh = Mid$(sTableName, iStartpos, cLength - (iStartpos +
> iTestPos)) Then
>                     IfSheetExists = True
>                     Exit For
>                 End If
>             End If
>             Next tbl
>     End With
>
>     objConn.Close
>     Set objCat = Nothing
>     Set objConn = Nothing
>
> End Function
>
>
>
>
> --
>
> HTH
>
> Bob Phillips
>     ... looking out across Poole Harbour to the Purbecks
> (remove nothere from the email address if mailing direct)
>
> "gpreg" <gpreg.wz92m@excelforum-nospam.com> wrote in message
> news:gpreg.wz92m@excelforum-nospam.com...
> >
> >
> > Hi...
> > I've been searching all over and can't find anything on this particular
> > challenge so I thought you guys might be able to help!
> >
> > I'm wondering if anyone knows how to test whether (or not) a specific
> > sheetname (e.g. "TestSheet") exists in a closed workbook?
> >
> > (I'd really prefer not to have to open the workbook if at all possible
> > as its one step in an intensive consolidation process from upto 100
> > workbooks.)
> >
> > Thx muchly!
> >
> >
> >
> > ------------------------------------------------
> > ~~ Message posted from http://www.ExcelTip.com/
> > ~~View and post usenet messages directly from http://www.ExcelForum.com/
> >
>
>


0
bob.phillips1 (6510)
11/16/2003 10:22:37 AM
Reply:

Similar Artilces:

Using an existing chart, how do I update the data
...

ANOVA procedures and t-test equal variance
In looking through Excel I see that it has both the ANOVA procedure and a t-test with equal variances and a t-test for unequal variances. One of the assumptions that is made in both the "standard t test" and ANOVA is that the variances are equal. Yet, Excel seems to have no test for homoscedasicity (oh what a cool word). How do you know these procedures are ok to use if you have not checked the assumption of equal variances? Noosa - > In looking through Excel I see that it has both the ANOVA procedure and a > t-test with equal variances and a t-test for unequal vari...

Excel 2003 Crashes when closed
Hi! For a few days I've noticed that whenever I open excel it crashes generating the bellow errors. Sometimes it happens when I just open a file and Excel proposes to "Recover my work and restart Microsoft Office Excel". But more frequently it happens when I close Excel. I've also noticed that when I close excel workbook first and then close Excel itself this error is not happening (or just a matter of chance... not sure). Other Office 2003 applications work good. I have no addins for excel and I'v also completely removed and reinstalled Office 2003. But it didn't he...

test of wizard
-- This posting is provided "AS IS" with no warranties, and confers no rights. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=f61980a5-9a9d-4399-8044-95f7dc1810...

Hit testing
I'm trying to implement client area hit testing in a MDI application. In my document, I have a list of graphic objects (e.g. polygons) that are displayed in the view. The list can get pretty long, so the hit test algorithm needs to be fairly efficient. My first attempt at this was to add HitTest(CPoint) function to the graphic object class. In this function I created a Region and then tested it. Something like... int CPolygon::HitTest(CPoint Point) { CRgn Rgn; Rgn.CreatePolygonRgn(PointList.GetData(), PointList.GetSize(), WINDING); if (Rgn.PtInRegion(Point)) ...

Closed Year Finacel Statements
Hello, I have closed the year , but after that when trying to access any finacela statement report , I got this error : * The open year you've selected for a temporary column is invalid. * How can solve this error? With regards, On Apr 19, 10:52 am, Amdawi <amd...@gmail.com> wrote: > Hello, > I have closed the year , but after that when trying to access any > finacela statement report , I got this error : > * > The open year you've selected for a temporary column is invalid. > * > > How can solve this error? > > With regards, Repl...

ignore -- testing
Haven't seen a posting in several NGs since 6/25. Just curious if GG is updating these NGs. I'm using the "old" user interface. Can't find these NGs using the new GG "experience". just testing "joeu2004" <joeu2004@hotmail.com> wrote in message news:812e733c-2d95-4174-8a3d-d08ecc672145@e17g2000prj.googlegroups.com... Haven't seen a posting in several NGs since 6/25. Just curious if GG is updating these NGs. I'm using the "old" user interface. Can't find these NGs using the new GG "experience". testing a...

Testing an Exchange backup
Hello everyone I have been backing up my Exchange database (SBS 2003) using an online backup with the NTBackup utility, and everything appears to be working fine: backup files created in the right place at the right time, and a log file saying that the backup was successful. However, I would like to make sure that the backup has worked by using it to restore data. My problem is I want to be absolutely sure that if everything goes wrong and I can't restore, that I won't lose any data. I wonder whether the following plan would work? 1. Dismount my databases. 2. Rename my priv1.e...

Moving Gmail Acct to Outlook? Passes Test but won't work Why/Fix?
I have followed the directions on the gmail page on moving my acct to outlook. It passes the test and yet does not work. What gives? How do I fix? Is this some weirdness between google and microsoft. Any suggestions? Help! Could you be a little more specific? What doesn't work? Does it send? Doesn't receive? Do you get any error messages? What version of Outlook? -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP Roland Schorr & Tower http://www.rolandschorr.com Microsoft OneNote FAQ: http://www.factplace.com/onenotefaq.htm **I apologize but I am unable to respond to direct r...

Tests?
Why do so many individuals need to jump on someone who posts a test? A higher percentage than Ivory soap put a clear indicator in the Subject line it's a test message. Seems the least educated of us all could identify this to make the decision to go right on by, it's a test. But what happens is a deluge of messages are sent to criticize the individual who is trying to solve a problem. And many of the criticisms are "not on topic" (like the ng they're in has "and criticism" as part of the ng title. But what is allowed is the use of vomit vocabulary! Certainly...

Closed Positions of Equity Options in Money 2004 dissapear
Most of the equity options positions that are closed in my portfolio simply dissapear from everywhere I am aware of looking. They do not show in the Portfolio view, in the investments reports, in the list of investments in the Work With Investments menu. However, the transactions appear in both the investment account registry and the cash account registry. I´m not sure if the portfolio totals are correct and if these totals include the missing options gains or losses. Does anyone have encountered a similar problem and what is the solution? Thanks for the help In microsoft.public....

Open Workbooks
Hello, How do I determine using VBA code how many workbooks are open? Thanks. Bill ...

development/test server
Hi Can we development/test server with its own database ..and at the same time have a production server with its database which host the real data... Also is it possible to export customisation to the production server from development/test server..will there be any kind of conflicts i have to keep in mind... please help.. Thanks I setup a CRM test envionment using the Adventure Works Cycle database. This is where we made all customizations, which were somewhat extensive. When I exported the customizations from the test environment and imported them to our production server e...

code closing multiple reports
I have VBA where I need to close any reports that are currently open. All of the reports in question have names starting with "rptGroup" followed by 2 more characters. There are up to 17 of these reports that could be open, so I use this coding to be sure I catch any open ones; DoCmd.Close acReport, "rptGroup01" DoCmd.Close acReport, "rptGroup02" DoCmd.Close acReport, "rptGroup03" ....... DoCmd.Close acReport, "rptGroup17" is there a way to more simply close all reports LIKE "rptGroup*"? thanks in adavance Sarah ...

Outlook Web Access
We have a user that when trying to reply to an email via Outlook Web Acces, the window closes with the email. He is running IE6 and Windows 98. I had him clear out his temp and temp internet folders. What could be causing this? Thanks Troy twilson@tgic.com Troy, Is there any type of pop-up blocking software installed on this machine? Thanks -- Greg Mansius [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights. "Troy Wilson" <twilson@tgic.com> wrote in message news:450601c42bac$b34d3f90$a401280a@phx.gbl... > We have a user...

User defined table function in XL2003
Hello all, I used to employ this feature many times earlier. My problem is: I would like to use a user defined function in Excel 2003. After having define a function, say Nrm(), in a module belonging to a table or to general, how can I make it available in my table? It does not appear in the list after INSERT > USER DEF. FUNCTION. Any help is greatly appreciated. Wolfgang The function should be in a General Module, not a sheet module or workbook module. So, in the VB Editor: Insert>Module. Type or paste your code there -- Kind regards, Niek Otten Microsoft MVP - Excel &qu...

Test
Sorry for wasting bandwidth here... think I'm having problems sending to the forums. We'll see if this gets through -- Merv Porter [SBS-MVP] ============================ ...

Workbook Paths on LAN Help Needed
How do I refrence a cell on a sheet that resides on the lan? I have tried the following but get an error. \\v1sacpdofc2\cpdprojects\CIS Financials\Costs Projections - bottom up\Nov 04 actuals to end of program - reduced scope\Gantt Planner Novemeber 2004 Incl New R3.xls.Tasks_Sheet!C12 Anyone have a suggestion or correction to my path naming. Open that workbook using File|open and the UNC naming convention. Swap back to the worksheet that should contain the formula. type = (equal sign) and click on the cell (C12) in the sheet you want to retrieve. Hit enter. Excel will build the formula ...

test message
This is a test to see what email is registered with my account. Thanks, ------=_NextPart_0001_5C4BECA8 Content-Type: text/plain Content-Transfer-Encoding: 7bit Good morning Nick, Sorry for the delay, but I have been asked to have you try creating another new test thread. Thank you in advance. Todd Berger Microsoft Online Support Engineer Get Secure! - www.microsoft.com/security ============================================= When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ===================...

Cannot create new or open existing appointment
Something happened all of a sudden in Outlook 2003, which I installed a week ago. I cannot open or create an appointment. Don't find the problem in the online database. Any help, please? Try opening Outlook with the /cleanreminders switch: http://www.howto-outlook.com/Howto/commandlineswitches.htm -- Kathleen Orland Outlook Tips: http://www.outlook-tips.net/ http://www.howto-outlook.com/ Outlook & Exchange Solutions Center: http://www.slipstick.com "Easydoesit" wrote: > Something happened all of a sudden in Outlook 2003, which I installed a week > ago...

User defined system power state
In WinCE5, it was said user can define OEM specified system power state, except the states that predefined such as On, UserIdle, SystemIdle and Suspend ... But when I try to define system power state named "WifiOn' in registery liking: [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Power\State\WifiOn] "Default"=dword:0 ; D0 "WIF1:"=dword:4 "Flags"=dword:10000 ; but I always failed when I call SetSystemPowerState(TEXT("WifiOn"),NULL,POWER_FORCE), And I use GetLastError(), found the error code...

Now to unhide columns ... and test for upper/lower case
I've got two more questions ... Having the routine to open the rows, I need to apply that to opening the required columns. I assume that the columns are referred to numerically, rather than alphabetically? When I run the routine, the first part runs fine (opening rows), but the second part generates the error message : Run-time error '1004': Application-defined or object-defined error (This occurred with the two routines below combined as one) Seperating them ( as pasted here ) and running it on its' own gives the error message: 400 Any ...

test
...

Disable Close Button
Does anyone know of a way to disable the close button (top right cross) to prevent users from accidentally closing Microsoft Outlook? Thanks Ian You can't. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Ian D McLean" <IanMcLean@itp.uk.com> wrote in message news:Oelp7QzlEHA.2492@TK2MSFTNGP15.phx.gbl... > Does anyone know of a way to disable the close button (top right cross) to > prevent users from accidentally closing Microsoft Outlo...

Show Status Reason in the Associated Closed Activity View.
In viewing closed activities, we should be able to include the Status Reason for the activity as well. Example: For a completed phone call (State = Completed), we have added "Left Voice Mail" as another Status Reason. For a cancelled phone call (State = Cancelled), we have added "Wrong Phone Number" as another Status Reason. These can be chosen from the picklist when Selecting Actions-->Close/Cancel Activity, but they cannot be displayed on the view. We should be able to view the type of reason for completing or cancelling a phone call. ---------------- This p...