janeburton
asked on
displaying data in excel
I have a subroutine that writes the data in an array to an excel sheet starting with the cell that i specify.
Public Sub loadData(startCell, resutlsArray )
'get number of rows & columns from the resultsArray
rws=UBound(resultsArray)
cls=UBound(resultsArray,2)
startCell.Range(Cells(1,1)
End Sub
This works fine if i have something like this (in sheet1):
dim startCell
set startCell = sheet1.cells(4,1)
call loadData(startCell, resultArray)
but if i have the exact same thing, except i want to write it to a different worksheet (still calling from sheet1)
dim startCell
set startCell = sheet2.cells(4,1)
call loadData(startCell, resultArray)
it prints the data, but actually starts at cell(7,1) in sheet 2, instead of cell(3,1)
how can i get it to start on any sheet with any cell that i specify when i call the function?
Thanks.
Public Sub loadData(startCell, resutlsArray )
'get number of rows & columns from the resultsArray
rws=UBound(resultsArray)
cls=UBound(resultsArray,2)
startCell.Range(Cells(1,1)
End Sub
This works fine if i have something like this (in sheet1):
dim startCell
set startCell = sheet1.cells(4,1)
call loadData(startCell, resultArray)
but if i have the exact same thing, except i want to write it to a different worksheet (still calling from sheet1)
dim startCell
set startCell = sheet2.cells(4,1)
call loadData(startCell, resultArray)
it prints the data, but actually starts at cell(7,1) in sheet 2, instead of cell(3,1)
how can i get it to start on any sheet with any cell that i specify when i call the function?
Thanks.
ASKER
Yeah, sheet2 exists.
i mean, the data loads....
but just doesn't start writing in the cell i specified.
i don't have anything else that's changing the location.
the resultArray is the data obtained from database
rs=connection.Execute(sqlS
if not rs.EOF
resultArray=rs.getRows
end if
i mean, the data loads....
but just doesn't start writing in the cell i specified.
i don't have anything else that's changing the location.
the resultArray is the data obtained from database
rs=connection.Execute(sqlS
if not rs.EOF
resultArray=rs.getRows
end if
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think that just using the Cells function with no qualifier may be the cause of the mixup, since that usually refers to the active sheet I believe.
ASKER
That worked! thanks!
no problem
-Are you sure that Sheet2 exists? Try saving the workbook again and trying it because it may not acknowledge the fact that sheet2 is there in a new workbook even though you see it until you use it or save it. That happened to me.
-And are you sure there is nothing else in your code that is changing the location of this? Check your resultsArray because I think that would be your problem. Could you show how that is being initialized?
Hope this helps
Kay