Reading a worksheet from a specified workbook

[jerm.hannley]

Hi,

I open a workbook, hide it and want to read some values from it. I
want to read a data within a range on each work sheet.

It try the following and it says runtime error 9 subscript out of
range.

Dim myCurrentFolder As String
myCurrentFolder = ActiveWorkbook.Path
ChDir myCurrentFolder
'
NewFN = Application.GetOpenFilename(FileFilter:="Excel Files
(*.xls), *.xls", Title:="Please select a file", MultiSelect:=False)
'
'Open the folder as read only
Set myBook = Workbooks.Open(NewFN, , False)
With ActiveWindow
.Visible = False
End With
'
' I ACN GET TO ThIS FOR LOOP AND IT GIVES ME THAT ERROR
Dim wSht As Worksheet
For Each wSht In Workbooks(NewFN).Worksheets
Set myRng = wSht.Range("A1:A13")
myarray = myRng.Value
'SetDrawingCollection myarray, coll

Next wSht



thanks for the help,
jeremiah

 

[Dave Peterson]

You have a response at your other post.

Hi,

I open a workbook, hide it and want to read some values from it. I
want to read a data within a range on each work sheet.

It try the following and it says runtime error 9 subscript out of
range.

Dim myCurrentFolder As String
myCurrentFolder = ActiveWorkbook.Path
ChDir myCurrentFolder
'
NewFN = Application.GetOpenFilename(FileFilter:="Excel Files
(*.xls), *.xls", Title:="Please select a file", MultiSelect:=False)
'
'Open the folder as read only
Set myBook = Workbooks.Open(NewFN, , False)
With ActiveWindow
.Visible = False
End With
'
' I ACN GET TO ThIS FOR LOOP AND IT GIVES ME THAT ERROR
Dim wSht As Worksheet
For Each wSht In Workbooks(NewFN).Worksheets
Set myRng = wSht.Range("A1:A13")
myarray = myRng.Value
'SetDrawingCollection myarray, coll

Next wSht

thanks for the help,
jeremiah