Dave:
Thanks for the answer. No, I did not have 11 sheets in this new workbook. Both names in the workbook for this particular sheet are 'Sheet11', and I didn't realize that that is not what 'Worksheets(11)' means, so when I changed it to Worksheets("Sheet11") the code ran fine.
Again, thank you for pointing me in the right direction.
Paul
"Dave Peterson" wrote:
You sure you have 11 worksheets?
that error #9 means subscript out of range and I don't see any other subscripts
in that line of code.
Also, for later use:
dim gFileCell as range
dim gFile as string
with worksheets(11) 'after you've fixed this!
set gFileCell = .Range("A2:A7736").Find(What:=i, _
After:=.cells(.cells.count), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
if gfilecell is nothing then
'error message
else
gfile = gfilecell.offset(0,1).value
end if
end with
If you don't find what you're looking for, you're code will break--better to
check to see if you found what you were looking for.
a wrote:
I've got some code below that has run fine elsewhere (another sheet with different pathnames), but it is giving me a runtime error now. Basically it is supposed to go get the cell contents (value) that act as a pathname for a web query. The error is happening at the "gFile = Worksheets(11)...." line. The ranges and the cell references are correct and the pathnames actually do return a document when used elsewhere. (The layout of the table with the pathnames that are looked-up is between the double lines at the bottom of the page.)
Any ideas as to why this isn't working?
Thanks,
Paul
-----------------------------------------------------------------------------
Sub Get_Data()
Dim i As Integer
Dim gFile As String
For i = 1 To 2 '7735
'this assigns the path from the Worksheets("Sheet11") to gFile (THE FILE TO OPEN)
gFile = Worksheets(11).Range("A2:A7736").Find(What:=i, After:=Worksheets(11).Range("A2"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Offset(0, 1)
With ActiveSheet.QueryTables.Add(Connection:=gFile, Destination:=Range("A1"))
.Name = "0001084869-03-000011"
.FieldNames = True
End With
Next i
End Sub
================================================
Column Number
1 Number GET (Path To file)
2 1 http://www.sec.gov/Archives/edgar/da...-04-000015.txt
================================================
--
Dave Peterson