specifing an open workbook and reading its worksheets

[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]

NewFN contains the drive, path, and filename.

Workbooks(...) only wants the filename.

I'd use:

dim NewFN as variant
dim NewFNWkbk as workbook
dim wsht as worksheet
....
newfn = Application.GetOpenFilename(...)

if newfn = false then
'user hit cancel
exit sub
end if

set newfnwkbk = workbooks.open(filename:=newfn, ...
....

for each wsht in newfnwkbk.worksheets
...