Worksheets Select

[Ashley Frank]

I am trying to select a worksheet by an object name:

Sheet 1 user selection of worksheets = Vnumber

Sub SheetExists()
Dim Sh As Worksheet, Exist As Boolean, Vnumber As Object
Set Vnumber = Range("F7")
Exist = False
For Each Sh In Sheets
If Sh.Name = Vumber Then
Exist = True
End If
Next Sh

MsgBox Vnumber
Sheets = Vnumber.Select (this does not work)
Range("A1").Select

I have also tried
Sheets(Vnumber).Select
Sheets.Name = Vnumber.Select

Thanks

Ashley

[Kjeldc]

try to chance: If Sh.Name = Vumber Then to If Sh.Name = Vnumber


"Ashley Frank" skrev:

>
> I am trying to select a worksheet by an object name:
>
> Sheet 1 user selection of worksheets = Vnumber
>
> Sub SheetExists()
> Dim Sh As Worksheet, Exist As Boolean, Vnumber As Object
> Set Vnumber = Range("F7")
> Exist = False
> For Each Sh In Sheets
> If Sh.Name = Vumber Then
> Exist = True
> End If
> Next Sh
>
> MsgBox Vnumber
> Sheets = Vnumber.Select (*this does not work)*
> Range("A1").Select
>
> I have also tried
> Sheets(Vnumber).Select
> Sheets.Name = Vnumber.Select
>
> Thanks
>
> Ashley
>
>
> --
> Ashley Frank
> ------------------------------------------------------------------------
> Ashley Frank's Profile: http://www.excelforum.com/member.php...o&userid=26488
> View this thread: http://www.excelforum.com/showthread...hreadid=397549
>
>

[Norman Jones]

Hi Ashley,

See my in line comments:

> Sub SheetExists()
> Dim Sh As Worksheet, Exist As Boolean, Vnumber As Object

Since Vnumber is a cell value, it should not be declared as Object; I would
change this to variant.

> Set Vnumber = Range("F7")

Set is used to assign an object to an object variable. Hee you are
assigning a cell value to the Vnumber variable. Drop the Set.

> Exist = False
> For Each Sh In Sheets
> If Sh.Name = Vumber Then
> Exist = True
> End If
> Next Sh

Your For ...Next loop assigns a value to the Exist boolean variable but does
nothing with it. Also, since a sheet can only be located once in your loop,
when it is found, exit the loop with a Exit For statement to avoid
unnecessary loops.

> MsgBox Vnumber
> Sheets = Vnumber.Select (*this does not work)*

Sheets represents a collection which comprises all sheet types. VBA,
understandaby, splutters here. You provide a more appropriate syntax
later...

> Range("A1").Select

This line is fine but is unnecessary and has no relevance to the intentions
underying your procedure. It can safely be deleted.

> I have also tried
> Sheets(Vnumber).Select

Subject to the above, this should now work as intended.

> Sheets.Name = Vnumber.Select

The Sheets collection does not have a Name property. In any event, an
equality expression cannot represent the object required by the Select
statement .

Perhaps the following is near to what you wanted:

'========================>>
Sub SheetExists()
Dim Sh As Worksheet
Dim Exist As Boolean
Dim Vnumber As Variant

Vnumber = Range("A1").Value
Exist = False

For Each Sh In Sheets
If Sh.Name = Vumber Then
Exist = True
MsgBox Vnumber
Sh.Select
Exit For
End If
Next Sh

If Not Exist Then
'sheet not found
'do something, e.g.:
MsgBox "Sheet not found, Check A1 value"
End If
End Sub

'<<=====================


Since there is a frequent need to check for the existance of a given sheet,
you might find it convenient to add a function for this purpose. For
example:

Function SheetExists(sName As String) As Boolean
On Error Resume Next
SheetExists = CBool(Len(Sheets(sName).Name))
End Function

If stored, perhaps in your Personal.xls macro workbook, a function like this
could be called on demand from a procedure, e.g:

Sub TestIt()
MsgBox SheetExists(ActiveSheet.Range("A1").Value)
End Sub


---
Regards,
Norman



"Ashley Frank" <[email protected]>
wrote in message
news:[email protected]...
>
> I am trying to select a worksheet by an object name:
>
> Sheet 1 user selection of worksheets = Vnumber
>
> Sub SheetExists()
> Dim Sh As Worksheet, Exist As Boolean, Vnumber As Object
> Set Vnumber = Range("F7")
> Exist = False
> For Each Sh In Sheets
> If Sh.Name = Vumber Then
> Exist = True
> End If
> Next Sh
>
> MsgBox Vnumber
> Sheets = Vnumber.Select (*this does not work)*
> Range("A1").Select
>
> I have also tried
> Sheets(Vnumber).Select
> Sheets.Name = Vnumber.Select
>
> Thanks
>
> Ashley
>
>
> --
> Ashley Frank
> ------------------------------------------------------------------------
> Ashley Frank's Profile:
> http://www.excelforum.com/member.php...o&userid=26488
> View this thread: http://www.excelforum.com/showthread...hreadid=397549
>

[Ashley Frank]

Thanks.

I eventually got it as

Dim Sh As Worksheet, Exist As Boolean, Vnumber As Object, Sheet_name As String

Sheets("Start").Select
Set Vnumber = Range("J11")
Exist = False
For Each Sh In Sheets
If Sh.Name = Vnumber Then
Exist = True
MsgBox Vnumber

End If
Next Sh

If Exist = True Then
Sheet_name = Sheets("Start").[J11].Value
Sheets(Sheet_name).Select
Range("A1").Select
Exit Sub
Else
Set NewSheet = Worksheets.Add
NewSheet.Name = Vnumber
End If

End Sub

Just about the same as what you were suggesting. Clearly it is "value" that is tthe key.

[katakau]

currently im working on importing excel data into access database.

here is the codes


Sub Open_All_Files()

Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String


sPath = "C:\STR\webSTR" 'location of files
ChDir sPath
sFil = Dir("*.xls") 'change or add formats


Set oWbk = Workbooks.Open(sPath & "\" & sFil) 'opens the file

End Sub

Sub run_macro()


Application.DisplayAlerts = False

Open_All_Files

oWbk.Sheets("DBT").Select<--erorr - object required come out

'open database
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
'connect to the access database
Set cn = New ADODB.Connection
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open "Data Source = C:\STR\SurveillanceMIS(hehe).mdb"

'open recordset

Set rs = New ADODB.Recordset

rs.Open "DBT", cn, adOpenKeySet, adLockOptimistic, adCmdTableDirect

r = 2



Application.StatusBar = "Reading row " & r
Do While Len(Range("A" & r).Formula) > 0

With rs

.AddNew

.Fields("rMonth") = Range("A" & r).Value
.Fields("rYear") = Range("B" & r).Value

.Update

End With

r = r + 1

Loop

rs.Close


Set rs = Nothing

End Sub

help me!

[royUK]

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.