How to tell if a Worksheet in Excel is hidden, from Access using VBA...

  • Thread starter Thread starter Mystif
  • Start date Start date
M

Mystif

Hello,

I could really use some help with this one...

I am importing several spreadsheets from Excel into Access via.
Access. I have one small problem, at this time. One of the sheets I
as importing is only used if it is not hidden.

My problem: I need a way to tell if the sheet is hidden or not, in
Access, using VBA.

I am not using any DoCmd statements in my code and I do not open
Excel.

This code is being debugged in Access but will be moved into a VB
application later.


Any suggestions on how I might be able to tell if the sheet is hidden?

Thanks.
 
Hi Mystif,

I think you'll need to open Excel for this. Air code:

Dim oBook as Excel.Workbook

Set oBook = GetObject("D:\folder\filename.xls")
If oBook.Worksheets("Sheetname").Visible = True Then
'worksheet is not hidden

End If

Note that You must explicitly test that the worksheet's Visible
property is True (or equal to xlSheetVisible or -1). This usual
shortcut
If oBook.Worksheets("Sheetname").Visible Then
will give the wrong result if .Visible has been set to
xlSheetVeryHidden.
 
Hello John,

Thank you for responding.

I am afraid, however, that your suggestion will not work. Somehow, if
it is even possible, I need to do this in Access and not Excel.

I am dealing with a rather complex issue. Not the least of which is
that Access is just a stepping stone as far as the code is concerned.
The end result of the project I am working on will be a VB app which
does not rely on Access or Excel but only uses the .mdb and .xls files
to complie the data I require.

I am writting all the code in Access because I am more familiar with
it than I am Excel and have, thus far, avoided using any code which
requires Access or Excel to be open (when complied into a VB app).

I require this because the end result will be called from a web page
by a user who may/may not have Excel or Access on their machine.

It is a tough one to which I have recieved few responses, but I really
need an answer, I am unable to continue to move forward without a
solution to this (as my boss has said) "rather simple problem."

For every other stumbling block I have encountered I have discovered
or had revieled to me a solution which worked in Access and could be
made to work in VB.

I need one more creative solution.

Any other ideas?
 
Here are some more possible approaches:

1) Try to import the possibly-hidden sheet and trap the error if it's
hidden.

2) Use ADODB.Connection.OpenSchema to get a list of the worksheets:
maybe this will only give you the visible worksheets. Here's a
function that Jamie Collins posted in one of these groups some time
ago which should get you started:

Public Function GetWSNames( _
ByVal WBPath As String _
) As Variant
Dim adCn As Object
Dim adRs As Object
Dim asSheets() As String
Dim nShtNum As Long
Dim nRows As Long
Dim nRowCounter As Long
Dim sSheet As String
Dim sChar1 As String
Dim sChar2 As String

Const INDICATOR_SHEET As String = "$"
Const INDICATOR_SPACES As String = "'"

Set adCn = CreateObject("ADODB.Connection­")
With adCn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB" & _
".4.0;Data Source=" & WBPath & ";Extended " & _
"Properties=""Excel 8.0;HDR=Yes"""
.CursorLocation = 3
.Open
End With

Set adRs = adCn.OpenSchema(20)
With adRs
nRows = .RecordCount
For nRowCounter = 0 To nRows - 1
sSheet = !TABLE_NAME
sChar1 = vbNullString
sChar2 = vbNullString
On Error Resume Next
sChar1 = Mid$(sSheet, Len(sSheet), 1)
sChar2 = Mid$(sSheet, Len(sSheet) - 1, 1)
On Error GoTo 0

Select Case sChar1
Case INDICATOR_SHEET
sSheet = Left$(sSheet, Len(sSheet) - 1)
Case INDICATOR_SPACES
If sChar2 = INDICATOR_SHEET Then
sSheet = Mid$(sSheet, 2, Len(sSheet) - 3)
End If
Case Else
sSheet = vbNullString
End Select

If Len(sSheet) > 0 Then
ReDim Preserve asSheets(nShtNum)
' Un-escape
asSheets(nShtNum) = Replace(sSheet, _
INDICATOR_SPACES & INDICATOR_SPACES, _
INDICATOR_SPACES)
nShtNum = nShtNum + 1
End If

.MoveNext
Next
.Close
End With

adCn.Close

GetWSNames = asSheets

End Function


3) Switch to the new Office 2007 formats so you can parse the XML to
find out what worksheets are visible.
 
Set adCn = CreateObject("ADODB.Connection­")

Gives the error "ActiveX componant can't create object".

Using Access 2003, do not have 2007.

Maybe I need some reference, or something?
 
Hmmm,

(Pasted the code, completely missed the hyphen.) Removing the hyphen
got the code working, but all sheets and print areas are visible.

I looked at the other items (adRs.Fields.Items(3).Value is the
Tablename, there were 9 Items under Fields) but could find nothing
that changed when a hidden sheet was processed.

Is there anything else you coud suggest? I am in denial at this stage
and keep believing that there must be some way of learning the
properties of a worksheet from outside of Excel.
 
My sincerest apologies for screwing something up on this end.

The code does work, you are a lifesaver.

Thank You.
 
Back
Top