By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,336 Members | 2,315 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,336 IT Pros & Developers. It's quick & easy.

Transfer Query Headers to Excel

P: 77
Hi Guys,

Need another help pls..

I got a query in Access (I have coded it in VBA as well) which returns me some data based on a calculation. The number of columns of the query change according to the criteria.

What I am looking for is a method to transfer the Column Headings alongwith the resultant data into Excel.

I am able to transfer the output data successfully, but not the column headings and currently have to put them in manually..

For example:
Expand|Select|Wrap|Line Numbers
  1. oWS.Range(vStartCol & vStartRow & ":" & vEndCol & vStartRow).Value = Array("EMPID", "EMPLOYEE NAME")
This code gives me two column headings at a desired location specified by vStartCol,vStartRow & vEndCol,vStartRow combination.. Looking to dynamically create these headers with the query headers..

Any help will be highly appreciated..

Thanks :)
Sep 8 '08 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 634
Hi Guys,

Need another help pls..

I got a query in Access (I have coded it in VBA as well) which returns me some data based on a calculation. The number of columns of the query change according to the criteria.

What I am looking for is a method to transfer the Column Headings alongwith the resultant data into Excel.

I am able to transfer the output data successfully, but not the column headings and currently have to put them in manually..

For example:
Expand|Select|Wrap|Line Numbers
  1. oWS.Range(vStartCol & vStartRow & ":" & vEndCol & vStartRow).Value = Array("EMPID", "EMPLOYEE NAME")
This code gives me two column headings at a desired location specified by vStartCol,vStartRow & vEndCol,vStartRow combination.. Looking to dynamically create these headers with the query headers..

Any help will be highly appreciated..

Thanks :)
Hi

How do you transfer the Data?

If you use a recordset then just cyle throught the field names ie.

Expand|Select|Wrap|Line Numbers
  1. Dim rs As New ADODB.Recordset
  2. Dim j As Integer
  3.  
  4.  
  5. rs.Open Sql,CurrentProject.Connection
  6.  
  7. For j = 0 To rs.fields.Count - 1
  8.     oWS.Cells(vStartRow, vStartCol + j) = rs.Fields(j).Name
  9. Next j
??

MTB
Sep 8 '08 #2

P: 77
That surely works....

You're a true star :-)

Thanks
Sep 8 '08 #3

Expert 100+
P: 634
That surely works....

You're a true star :-)

Thanks
Thanks for letting us know.

I think this particular star is a little tarnished by now, but still glad to help.


MTB
Sep 9 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.