[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

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), Cells(rws+1, cls+1)).Value=resultsArray

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.
0
janeburton
Asked:
janeburton
  • 4
  • 2
1 Solution
 
kay2gCommented:
I tried your code and it worked for me: I have a couple of questions

-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
0
 
janeburtonAuthor Commented:
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(sqlString)
if not rs.EOF
resultArray=rs.getRows

end if
0
 
kay2gCommented:
Why dont you try this:

Public Sub loadData(startCell, resutlsArray )

'get number of rows & columns from the resultsArray
rws=UBound(resultsArray)
cls=UBound(resultsArray,2)

'the change
startCell.Range(startCell.Worksheet.Cells(1, 1), startCell.Worksheet.Cells(rws + 1, cls + 1)).Value = resultsArray

End Sub
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
kay2gCommented:
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.
0
 
janeburtonAuthor Commented:
That worked!  thanks!
0
 
kay2gCommented:
no problem
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now