runtime error 9
 

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

runtime error 9
 

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

runtime error 9
 

Watch out for that .find stuff, too.

a wrote:

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



--

Dave Peterson