Solved

displaying data in excel

Posted on 2004-08-04
6
188 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 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
JSON Response and request in VB6 application 11 420
VBA/SQL - Connect to SQL server and pull data 4 109
Spell Check in VB6 13 112
MsgBox 4 61
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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 Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

773 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