Exporting data to Excel via GetRows array

[Terpsfan]

Hi, what I'm trying to accomplish is to pump data from an Access query into an Excel spreadsheet by using a Get Row array. The array works but because my query only shows results in one row with many columns, it's filling in the cells with only the data of the first column in the first record. Could anyone be kind enough to tell me what I'm doing wrong?


Private Sub IntoExcel()
Set objXLBook = GetObject(Application.CurrentProject.Path & "\Excel.xls&quot
Set objXLApp = objXLBook.Parent
Set objResultsSheet = objXLBook.Worksheets("Results&quot
objXLApp.Visible = True
objXLBook.Windows(1).Visible = True

Set objRec = New ADODB.Recordset
objRec.Open "qryTotalAccounts", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly, adCmdTableDirect
varResults = objRec.GetRows()
objRec.Close
Set objXLRange = objResultsSheet.Range("Results&quot

objXLBook.Save

Set objRec = Nothing
Set objCmd = Nothing
Set objXLApp = Nothing
Set objXLBook = Nothing
Set objResultsSheet = Nothing
Set objXLRange = Nothing


End Sub

 

[Terpsfan]

Ok, I stumbled on the answer. My Excel formula array was oriented in the wrong direction. Oddly enough I spent 3 hours at work racking my brain to figure it out and couldn't. I got home and figured it out in 5 minutes...go figure.

 

[Terpsfan]

I spoke too soon. I changed the formula array to read horizontally but that's not going to work for what I need, I need the data to be presented vertically...so I welcome any assistance.

 

[cmmrfrds]

The getrows method returns the data with columns as the first dimension and rows as the second deminsion which is counter intuitive. Try the getstring method instead or you could set up a loop to flip the columns and rows around after the getrows method. The getstring method should fit nicely into excel.

 

[Terpsfan]

So if I use the GetString method am I still reading data into an array? And could you show me an example of how I could flip the columns and rows around after the get rows? I find the getrows method to be very fast and would like to use that method if I could. Thanks for your help.

 

[Terpsfan]

If you could please provide me an example of how I could do this via getstring, I'd like to learn both methods. I have looked in some of my books and on the MS Knowledge Base, but am not finding any useful examples.

 

[Terpsfan]

Ok, I figured out how to open a recordset with Getstring. I created a string variable and pointed the getstring method to that variable. But how do I then write it to the Excel spreadsheet? I'm learning as I go here.

 

[dwalker]

There's a single line of code that replaces ALL of the code you're using:

DoCmd.TransferSpreadsheet acExport , _ acSpreadsheetTypeExcel9 ,"qryTotalAccounts" , "C:\results.xls"

This creates a spreadsheet called "Results.xls" and places all the data from your query into a tab called "qryTotalAccounts". Adjust the path of "C:\Results.xls" to suit your needs.

Regards

Damien

 

[Terpsfan]

Dwalker, thanks for your reply, but I need to export the data to specific ranges of cells, and I don't think that the transfer spreadsheet is going to work for me in this fashion.

 

[cmmrfrds]

I am not sure how to get the data to excel, but here are the arguments for the getstring.

rst.GetString(, -1, vbTab, vbCr, "None&quot

-1 means return all the rows - can use any number
vbtab is the column delimiter
vbcr is the line feed
the above 3 are defaults for getstring.
None is a literal to put in a column that is null

In effect the results of the getstring are like a text file.
Try Debug.Print rst.Getstring to see the results. You can change any of the parameters.


dim var as variant, i as integer, svar as variant
var = rst.getrows '- assume 2 columns
'-- you will need to experiment
for i = o to Ubound(var,2)
svar(i,0) = var(0,i)
svar(i,1) = var(1,i)
next