Thread
:
specifing an open workbook and reading its worksheets
View Single Post
#
2
Posted to microsoft.public.excel.programming
Dave Peterson
external usenet poster
Posts: 35,218
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
Reply With Quote
Dave Peterson
View Public Profile
Find all posts by Dave Peterson