CODEWITHSUNDEEP
excelvba
anandhu
anandhu

Reputation: 780

How to get Workbook Name after opening the workbook using workbooks.open?

Workbooks.Open "C:\abc.xlsx"
Workbooks("abc").Worksheets("Sheet1").Range("A1:B7").Clear

In the above code I am opening the workbook using Workbooks.Open in first line. In the second line I am accessing the opened workbook using the workbook name.

How can I access the opened workbook without the filename in second line?

(I want to create a function and I don't want to pass both the file paths and filenames separately)

Upvotes: 2

Views: 3824

Answers (4)

أبو عائشة ورقية ومحمد
أبو عائشة ورقية ومحمد

Reputation: 133

You can try this

Option Explicit
Sub TEST()
Dim WB As Workbook
Set WB = Workbooks.Open(Filename:="C:\abc.xlsx")
For Each WB In Workbooks
    If WB.Name = "abc.xlsx" Then
        WB.Worksheets(Sheet1).Range("A1:B7").ClearContents
        Exit Sub
    End If
Next
End Sub

Upvotes: 1

Error 1004
Error 1004

Reputation: 8230

You can shorten your code:

Option Explicit

Sub OpenWb()

    Dim ws As Worksheet

    Set ws = Workbooks.Open("C:\abc.xlsx").Worksheets("Sheet1")

    With ws '<- Use With Statement to avoid sheet repetition
        .Range("A1:B7").ClearContents
    End With

End Sub

Upvotes: 1

Damian
Damian

Reputation: 5174

You need to use references and reference the workbook and the sheet for example:

Option Explicit

Sub OpenWorkbook()

   Dim wb As Workbook, ws As Worksheet

   Set wb = Workbooks.Open("C:\abc.xlsx", UpdateLinks:=False, ReadOnly:=True)
   Set ws = wb.Sheets("Sheet1")

   ws.Range("A1:B7").ClearContents

End Sub

Note that the parameters on the openworkbook such as Updatelinksand ReadOnly can be modified to True or Falseas you need to.

Upvotes: 4

Nathan_Sav
Nathan_Sav

Reputation: 8531

Create an object of type Excel.Workbook and open the workbook into that.

Like so

Dim w as Excel.Workbook
set w= Workbooks.Open ("C:\abc.xlsx")

and then you can say

w.worksheets.add.....

etc

Upvotes: 3

Related Questions