Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

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

0
jfrank14
Asked:
jfrank14
  • 2
1 Solution
 
gowflowCommented:
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 HensonIT & Database AssistantCommented:
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

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now