Help with String

[wageslave101]

This sub checks if the workbook is open, which is in the format "Rep Name.xls", this one works fine.

Sub IsRepOpen()
Dim wbook As Workbook, wkbk As Variant
    wkbk = "Rep Name"
    On Error Resume Next
    Set wbook = Workbooks(wkbk)
        If wbook Is Nothing Then
            Workbooks.Open (wkbk)
    Set wbook = Nothing
        Else
    Set wbook = Nothing
        End If
End Sub

However rather than going in and entering in the Rep Name I'd like to grab the details from a data entered into the Sheet

But when I redo the IsRepOpen() sub it does nothing. Stepping through it processes the line Workbooks.Open (wkbk) but does nothing.

Sub IsRepOpen()
Dim wbook As Workbook, wkbk As Variant
    TheRep = Worksheets("Sheet1").Range("A2").Value & " " & Worksheets("Sheet1").Range("B2").Value
    wkbk = TheRep
    On Error Resume Next
    Set wbook = Workbooks(wkbk)
        If wbook Is Nothing Then
            Workbooks.Open (wkbk)
    Set wbook = Nothing
        Else
    Set wbook = Nothing
        End If
End Sub

I thought that the string might not be reading or something so I checked with a messagebox

Sub RepName()
    MsgBox Worksheets("Sheet1").Range("A2").Value & " " & Worksheets("Sheet1").Range("B2").Value & ".xls"
End Sub

Which shows the string as Rep Name fine

Can anyone help with why this is behaving or what I need to do to make it play nice?

Thanks,
WageSlave101

 

[Joe4]

This is just a short in the dark, but having spaces in file names or directories can often wreak havoc with certain types of code (that is why most programmers use underscores instead of blank spaces in names of folder, directories, variables, etc).

So with that in mind, try changing the name of your file to eliminate all spaces, and see if it makes a difference.

 

[wageslave101]

Thanks jm14

In case it was some sort of previous formatting in the workbook and created a new one and set it up there. From the clean Workbook it behaves as expected so I've just imported the data from the old workbook

Thank you again

WageSlave

 

[DougStroud]

What is wkbk?
If it is a workbook, then try declaring it as an object-

Try:

Set wkbk=TheRep

Set wbook = Workbooks(TheRep) 
        If wbook Is Nothing Then 
            Workbooks.Open (TheRep)

 

[wageslave101]

Thanks Doug I'll give that a try and see if that makes it behave

Cheers,
WageSlave