Go Premium for a chance to win a PS4. Enter to Win

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

Iterate though sheets in external workbook

Trying to run a for each loop on an external workbook with variable number of sheets.

for each wkSht in Workbooks(startFolder).Worksheets
  msgbox("TEST")
next

Open in new window


Not sure why it won't work. startFolder is my absolute path to the workbook, as in C:/book.xls

Still new to VBA and I know this is simple. Please help!

Thanks!
0
keats88
Asked:
keats88
  • 2
2 Solutions
 
SiddharthRoutCommented:
Try this

Sub Sample()
    Dim wb As Workbook
    Dim wkSht As Worksheet
    
    Set wb = Workbooks.Open("C:\MyFile.xls")
    For Each wkSht In wb.Worksheets
      MsgBox wkSht.Name
    Next
End Sub

Open in new window


Sid
0
 
keats88Author Commented:
That works, but is there a way to do this without having to actually open the workbook?

Thanks!
0
 
SiddharthRoutCommented:
No that I know of... AFAIK, You have to open the workbook.

Sid
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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