Import Crosstab Query From Access into Excel WITH Column Headings

try creating a select query using the Crosstab query as the domain and export the select query..

select * from CrosstabQueryName

oops, sorry your codes is not visible when i was typing...

hmm..perhaps setting the vlookups using vba..

You can use MATCH to determine the return column index...


=VLOOKUP("foo",'Sheet with crosstab'!$A:$Z,MATCH("column header I want",'Sheet with crosstab'!$1:$1,0),FALSE)

Yeah, the other select queries I import also do not import column headings.  My vlookups work just fine, but when a new item is added it will push everything out to different rows making my references incorrect.  If the column headings were imported then that could provide a static reference for getting the data I want.  Any ideas?

See my comment http:#a30215592.  If you have column headings, that formula will find the correct column.

MatthewsPatrick,

If I had column heading I would use them for the vlookup.  The problem is that the column headings are not imported along with the data.  I have manually added column headings, but these are rendered null and void when data is added as then columns then shift.  The question here is how do I import the column heading from access into excel?

Can you post the code with which you get the data into Excel?  It should be easy to modify that to include column headings.

Column headings are included with the Output to Excel.  

You can Export query as .xls file then you will have all the headers.
Takes probably the same amount of time as running the macro.
All your vLookups convert to Index/Match to dynamicly recognize your headers.

DoDahD,

That has also been my temporary fix...but again if data is added so that those column headings expand, it makes things difficult.  I would have to manually go in and change the column headings both in Access and in Excel.  Unless there is a way to get it to automatically update.

If you push the crosstab to excel, you will get the column headdings.
Also, setting column headings provides full control on the order of the columns.  Even if you pulled from the query, the column order would be the same.

If the data does not match a colun heading, it does not get included in the crosstab.  How dynamic are the column headings?

MSmax,

If I was always starting from scratch I probably could, but this import is part of a larger spreadsheet. Most users don't even have access tot he database itself and rely on the excel macros to pull data in so that it can be seen.  Can you export the data as an .xls file using vba?  If so then I may be able to create an inbetween file and then delete it afterwards...not very elegant but it might work.

<Can you export the data as an .xls file using vba?>

 yes you can, and i wlll suggest that you do all of this using vba codes

DoDahD,

Yes, that is correct.  The column heading are not changing every day, but this is an application that (hopefully) will be around for a while, so the potential for change in the long term is high...and I may not always be around to fix things when they don't work like they used too.  

I just noticed you said something in on of your posts

"If you push the crosstab to excel, you will get the column headdings."

How?  That has not been my experience.  Is this done using VBA?  Thanks for any help.

see this link for sample code
http://support.microsoft.com/?kbid=210288

likewise you can use the transferspreadsheet

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "NameOfQuery", xlFile, True, "NameOfsheet"

Note capricorn1's excellent recommendation

Very simple.

1) Have the Access data be transferred to one worksheet, name it Data, and put nothing else on that sheet.

2) Use the code below to push data to the Data worksheet

3) Have your VLOOKUP refer to it like this:

=VLOOKUP("doodah",Data!$A:$Z,MATCH("my column",Data!$1:$1,0),FALSE)

or if you prefer:

=INDEX(Data!$A:$Z,MATCH("doodah",Data!$A:$A,0),MATCH("my column",Data!$1:$1,0))

Dim xlApp As Object, xlWb As Object, xlWs As Object
Dim rs AS DAO.Recordset
Dim Counter As Long

Set rs = CurrentDB.OpenRecordset("SELECT * FROM QueryName")

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("c:\folder\subflder\file.xls")
Set xlWs = xlWb.Workbooks("Data")

With xlWs
    .Cells.ClearContents
    .[a2].CopyFromRecordset rs
    For Counter = 1 To rs.Fields.Count
        .Cells(1, Counter) = rs.Fields(Counter - 1).Name
    Next
End With

xlWb.Save
xlWb.Close

Set xlWs = Nothing
Set xlWb = Nothing

xlApp.Quit
Set xlApp = Nothing

rs.Close
Set rs = Nothing

MsgBox "Done"

Select allOpen in new window

Just to expand the discussion, why do you not use a Pivot Table?

DoDahD,

Good point!

Patrick

OK, thank you all for the excellent suggestions.  I think I need to explain myself a little bit better though.  I have an Access database that is used to store data generated in my lab.  The decision was made that the data entry process in Access was too complicated, so "Run Sheets" were created in Excel.  These run sheets download parameter values from the database everytime they are opened.  These parameter values are then used for dropdown boxes.  The particular case I am refering to involves selecting a name of a brine from a list...and then populating a composition table based on the selection.  The way I do that is to have excel connect to access on open...download the latest composition data, and then use vlookup on the name selected to select the correct composition.  Everything works exactly as I want it to unless something changes in the query (for example a new ion is added to the composition).  When this happens the columns (in excel...say Na+ was column 13 but now it is column 14 making my table incorrect) that correspond to specific ions change (unless I limit the selection in the crosstab query which is an option).  Ideally the column headings would download with the data so that they provide a static reference to do my vlookup on (as has been suggested).  Unfortunately the download of the query only includes the data, not the column headings.  I am not sure how a pivot table would help here...but I do not work with them often.  It may be that I didn't explain myself very well initially too!  I hope this clarifies things.  I am currently limiting the crosstab query by defining column headings, and this works OK.  It could cause problems down the road, however, so I was hoping for a more dynamic solution.  Thank you.  The code I use to actually download the data is attached.

Dim Connection As ADODB.Connection
    Dim RecordSet As ADODB.RecordSet
    
    Set Connection = New ADODB.Connection
    Set RecordSet = New ADODB.RecordSet
     
    Connection.Open strConnectionString
    RecordSet.Open strSql, Connection, adOpenStatic, adLockOptimistic
     
    rngTargetSheet.Activate
    rngTargetSheet.Name = strSheetName
    rngTargetRange.CopyFromRecordset RecordSet
    
    Set Connection = Nothing
    Set RecordSet = Nothing

Select allOpen in new window

Pulling your data from Access into Excel to generate the 'source data' could be accomplished wth the Transfer spreadsheet.   This will generate a worksheet with up to ~65,000 records in 2003 and earlier.
In excel, create a pivot table with the same row, column, and data as your crosstab (which can be learned as a Macro for re-use).   When one double-clicks on any data value in the PivotTable, you are rewarded with a new worksheet with column headings that only contains a copy of the source rows used in the calculation of that value (the appropriate subset).   The source data worksheet can be set to hidden to minimize confustion.   This process replaces the crosstab and vlookups.
I will try to gen an example.

So, the attached two files work together.
In access, a query is pushed to Excel as a named worksheet.
In Excel, the named worksheet is used for a PivotTable.
When you double click on any total, you get a supporting worksheet with all the data extracted as a copy from the original source data
The only tweak,I would add is to export as a name range for use in the PivotTable so the record length can be variable.   But this should convey the concept.
ExcelOut.XLS
Database2.accdb

BTW, this is not lab data, it is sample excel data from microsoft's web pages on pivot table training.
I extracted the MS sample files, imorted to Access, etc.  
I used Access 2010 beta.  What version should we be targeting?

BTW, there is also a function =GETPIVOTDATA to retrieve values from the PivotTable.  The syntax is a little confusing, so I usually teach this by telling student to click where you want the value, enter a = to start the formula, then click on the PivotTable data.   Excel inserts the function.

I am using Office 2007.  I will take a look at everything and see if it works the way I want it too.  Thank you very much for the help.

Not quite what I wanted, but it was acceptable