specifing an open workbook and reading its worksheets
 

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

specifing an open workbook and reading its worksheets
 

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
...



wrote:

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