Setting Worksheet

[HWL]

This should work...

Dim tempfile1 As Workbook
Dim wsSheet As Worksheet
Set tempfile1 = Workbooks.Open(Range("D2"), ReadOnly, notify = False)
Set wsSheet = tempfile1.Worksheets(range("n2"))
MsgBox tempfile1.Worksheets(wsSheet).Cells(14, 1)

If fails on Set wsSheet = tempfile1.Worksheets(range("n2"))

I can use Set wsSheet = templfile1.Worksheets(1) and it will work fine, but I'm not certain the sheet will always be indexed as 1, thus the reason I want the user to supply the sheet name in cell N2.

What am I doing wrong here?

 

[Fizzyfish]

This should work...

Dim tempfile1 As Workbook
Dim wsSheet As Worksheet
Set tempfile1 = Workbooks.Open(Range("D2"), ReadOnly, notify = False)
Set wsSheet = tempfile1.Worksheets(range("n2"))
MsgBox tempfile1.Worksheets(wsSheet).Cells(14, 1)

If fails on Set wsSheet = tempfile1.Worksheets(range("n2"))

I can use Set wsSheet = templfile1.Worksheets(1) and it will work fine, but I'm not certain the sheet will always be indexed as 1, thus the reason I want the user to supply the sheet name in cell N2.

What am I doing wrong here?

Try Set wsSheet = tempfile1.Worksheets(range("n2")).Value

 

[HWL]

Try Set wsSheet = tempfile1.Worksheets(range("n2")).Value

Still fails, hmmm

 

[BarCodeWiz]

How about:


Dim bookName As String
bookName = Range("D2")
Dim shtName As String
shtName = Range("N2")
Dim tempfile1 As Workbook
Dim wsSheet As Worksheet
Set tempfile1 = Workbooks.Open(bookName, ReadOnly, notify = False)
Set wsSheet = tempfile1.Worksheets(shtName)
MsgBox tempfile1.Worksheets(wsSheet).Cells(14, 1)

 

[HWL]

How about:


Dim bookName As String
bookName = Range("D2")
Dim shtName As String
shtName = Range("N2")
Dim tempfile1 As Workbook
Dim wsSheet As Worksheet
Set tempfile1 = Workbooks.Open(bookName, ReadOnly, notify = False)
Set wsSheet = tempfile1.Worksheets(shtName)
MsgBox tempfile1.Worksheets(wsSheet).Cells(14, 1)

grrrrr, this should work but it still fails at the Set wsSheet point.

 

[BarCodeWiz]

One more try:

Dim bookName As String
    bookName = Sheet1.Range("D2").Text
    Dim shtName As String
    shtName = Sheet1.Range("N2").Text

    Dim tempfile1 As Workbook
    Dim wsSheet As Worksheet
    Set tempfile1 = Workbooks.Open(Filename:=bookName, ReadOnly:=True, Notify:=False)
    Set wsSheet = tempfile1.Worksheets(shtName)
    MsgBox tempfile1.Worksheets(wsSheet).Cells(14, 1)

Tell me I got it

 

[HWL]

One more try:

Dim bookName As String
    bookName = Sheet1.Range("D2").Text
    Dim shtName As String
    shtName = Sheet1.Range("N2").Text
 
    Dim tempfile1 As Workbook
    Dim wsSheet As Worksheet
    Set tempfile1 = Workbooks.Open(Filename:=bookName, ReadOnly:=True, Notify:=False)
    Set wsSheet = tempfile1.Worksheets(shtName)
    MsgBox tempfile1.Worksheets(wsSheet).Cells(14, 1)

Tell me I got it

Nope this still fails at the Set wsSheet = tempfile1.Worksheets(shtName)

This really makes no sense. The code logic is sound. Thanks for trying.

 

[BarCodeWiz]

Interesting. I tested the same code to be sure, and it did work. What if you hard code the values like this?

    Dim bookName As String
    bookName = "C:\MyBook.xlsx"
    Dim shtName As String
    shtName = "Sheet2"
 

    Dim tempfile1 As Workbook
    Dim wsSheet As Worksheet
    Set tempfile1 = Workbooks.Open(Filename:=bookName, ReadOnly:=True, Notify:=False)
    Set wsSheet = tempfile1.Worksheets(shtName)
    MsgBox tempfile1.Worksheets(wsSheet).Cells(14, 1)

 

[HWL]

Changed it but still failing at the same point. I'm about to force the end-user to utilize sheets(1) though now it won't be as dynamic.

 

[BarCodeWiz]

Well, at least we tried.