set/show a string in vba in excel

hi,
can someone help me. i'm trying to set a string, and then show it in a msgbox using vba in excel and for some reason i can't get it working.

says object doesn't support this property or method. highlighting  "Fname = Report.Range("J11").Text". if i comment this out, I get the same error message on "Msgbox Report"

can't figure out what's up

Thanks,

Sub CopyPaste2()
    Dim Wb As Workbook                  ' current workbook
    Dim Report As Workbook              ' referenced workbook
    Dim Ffn As String                   ' full file name of Report
    Dim Fname As String                    ' retrieved file name
    
    Ffn = "H:\F&O Report Instructions Macro 1-22-2014.xlsm"
    Set Report = GetWorkbook(Ffn)
    If Report Is Nothing Then
        MsgBox "Couldn't find the Report", vbCritical, _
               "Missing workbook"
        Exit Sub
    Else
        Fname = Report.Range("J11").Text
        MsgBox Fname
    End If
    'If Len(Fn) Then
    End Sub
Private Function GetWorkbook(Wn As String) As Workbook

    Dim Wb As Workbook
    Dim Sp() As String
    
    Sp = Split(Wn, "\")
    Debug.Print Sp(UBound(Sp))
    On Error Resume Next
    Set GetWorkbook = Workbooks(Sp(UBound(Sp)))
    If Err Then
        Set GetWorkbook = Workbooks.Open(Wn)
    End If
End Function

Open in new window

jfrank14Asked:
Who is Participating?
 
gowflowConnect With a Mentor Commented:
yes for sure you will get an error as you cannot associate a range to a workbook you can associate a range to a worksheet. So say in your workbook Report it is the first sheet you want to examine then do this:

replace this line
Fname = Report.Range("J11").Text

by this line
Fname = Report.worksheets(1).Range("J11").Text

gowflow
0
 
Rob HensonFinance AnalystCommented:
Alternatively, if you give the required cell (range J11) a name in the Name Manager, eg FileName, you can then use:

Fname = Report.Range("FileName").Text

When creating the name, make sure it has scope for Workbook rather than Worksheet otherwise you will endup back with the same problem of having to define the sheet.

Thanks
Rob H
0
 
jfrank14Author Commented:
that did it, gowflow . thanks!
0
 
gowflowCommented:
Your welcome
gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.