Solved

displaying data in excel

Posted on 2004-08-04
6
190 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

696 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