?
Solved

displaying data in excel

Posted on 2004-08-04
6
Medium Priority
?
195 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
  • 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 400 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
Suggested Courses
Course of the Month6 days, 2 hours left to enroll

589 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