CODEWITHSUNDEEP
excelvba
Divya Roopam
Divya Roopam

Reputation: 59

Activating the workbook

I have a string "sFile" that stores the name of workbook with extension.

I want to activate this workbook, stored in a string.

And then close it.

Code am using is:

Dim wbk as workbook
Set wbk = Workbooks(sFile)
wbk.Activate
wbk.close

But this is not working.Please help.

Upvotes: 0

Views: 490

Answers (2)

Shai Rado
Shai Rado

Reputation: 33692

You need to see if you succeed to Set wbk = Workbooks(sFile), it wil work only if the workbook is open.

If it doesn't succeed (wbk Is Nothing), then you need to open the workbook.

Code

Option Explicit

Sub SetWB_toOpenWorkbook()

Dim wbk As Workbook
Dim sFile As String
Dim FilePath As String

' just an example of my file name (clean with extension)
sFile = "SO_1.xlsm"

' set the Dektop path
FilePath = "C:\Users\" & Environ("USERNAME") & "\Desktop\"

On Error Resume Next
Set wbk = Workbooks(sFile)
On Error GoTo 0
If wbk Is Nothing Then ' if not open, then open the workbook
    Set wbk = Workbooks.Open(FilePath & sFile)
End If

' just for my tests, put the workbook name in "A1" in "Sheet1"
wbk.Worksheets("Sheet1").Range("A1").Value = wbk.Name

wbk.Activate
wbk.Close True

End Sub

Upvotes: 0

Jeremy
Jeremy

Reputation: 1337

As mentioned in the comments, it only takes the name (not path) - this should do it for you:

Dim wbk as workbook
Set wbk = Workbooks(right(sFile,Instrrev(sFile,"\")+1))
wbk.Activate
wbk.close

Upvotes: 1

Related Questions