Import Crosstab Query From Access into Excel WITH Column Headings

I am inporting an Access Crosstab query into excel, and then doing some vlookups on the imported data.  Everything works great unless an additional item is added to the crosstab query...then many of my vlookups are incorrect because the items I want are not in the same places anymore.  I would like to import the crosstab query column headings so that I can find my data based on the column heading regardless of whether the query has changed or not.  I currently use the attached sequel string to import the query.  I use an * because picking individual items does not work.
BrineSompositionSequelString = "Select *" _
                    & " From qryBrine_Crosstab" _
                    & " ORDER BY BrineDetails"

Open in new window

uwlchemistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try creating a select query using the Crosstab query as the domain and export the select query..

select * from CrosstabQueryName
0
Rey Obrero (Capricorn1)Commented:
oops, sorry your codes is not visible when i was typing...
0
Rey Obrero (Capricorn1)Commented:
hmm..perhaps setting the vlookups using vba..
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Patrick MatthewsCommented:
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)
0
uwlchemistAuthor Commented:
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?
0
Patrick MatthewsCommented:
See my comment http:#a30215592.  If you have column headings, that formula will find the correct column.
0
Richard DanekeTrainerCommented:
To control CrossTab headlings, I have used a Column Headings value in query design.  
This lets me set all twelve months (like Jan, Feb, Mar) and could be used here.
 In SQL, this involves the IN clause after the Pivot statement in your initial crosstab query.
This assumes that you know the column heading values you expect to see in Excel
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
uwlchemistAuthor Commented:
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?
0
Patrick MatthewsCommented:
Can you post the code with which you get the data into Excel?  It should be easy to modify that to include column headings.
0
Richard DanekeTrainerCommented:
Column headings are included with the Output to Excel.  
0
MSmaxImplementation ConsultantCommented:
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.
0
uwlchemistAuthor Commented:
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.
0
Richard DanekeTrainerCommented:
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.
0
Richard DanekeTrainerCommented:
If the data does not match a colun heading, it does not get included in the crosstab.  How dynamic are the column headings?
0
uwlchemistAuthor Commented:
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.
0
Rey Obrero (Capricorn1)Commented:
<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
0
uwlchemistAuthor Commented:
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.
0
Rey Obrero (Capricorn1)Commented:
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"
0
Richard DanekeTrainerCommented:
Note capricorn1's excellent recommendation
0
Patrick MatthewsCommented:
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"

Open in new window

0
Richard DanekeTrainerCommented:
Just to expand the discussion, why do you not use a Pivot Table?
0
Patrick MatthewsCommented:
DoDahD,

Good point!

Patrick
0
uwlchemistAuthor Commented:
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

Open in new window

0
Richard DanekeTrainerCommented:
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.
0
Richard DanekeTrainerCommented:
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
0
Richard DanekeTrainerCommented:
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?
0
Richard DanekeTrainerCommented:
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.
0
uwlchemistAuthor Commented:
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.
0
uwlchemistAuthor Commented:
Not quite what I wanted, but it was acceptable
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.