Solved

displaying data in excel

Posted on 2004-08-04
6
191 Views
Last Modified: 2010-05-02
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
Comment
Question by:janeburton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 3

Expert Comment

by:kay2g
ID: 11726246
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
 
LVL 1

Author Comment

by:janeburton
ID: 11726830
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
 
LVL 3

Accepted Solution

by:
kay2g earned 100 total points
ID: 11727376
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
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!

 
LVL 3

Expert Comment

by:kay2g
ID: 11727391
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
 
LVL 1

Author Comment

by:janeburton
ID: 11728238
That worked!  thanks!
0
 
LVL 3

Expert Comment

by:kay2g
ID: 11728978
no problem
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question