Dynamically create headers in Excelsheet using Access table values

[aman1234]

Hi guys

I have an access table and I want the code that will check two columns in the table "EnvelopeType" and "EnvelopeSize" and create headers in Excelsheet automatically. In the attached workbook, like in sheet1 the headers are already appeared,, I want this to be done dynamically using vba code so that if new values get inserted in EnvelopeType and EnvelopeSize then we won't have to change the code to display more headers.

Please see attached workbook named Sample and as i can't upload Access table so I have exported it in Excel to give you an idea how my Access table looks like. But in the code we have to make a connection with Access table and getvalues from EnvelopeType and EnvelopeSize to display hearders in the excelsheet.

e.g EnvelopeType EnvelopeSize
TNT 2nd Class C5
PP1 2nd Class C5
PPI 1st Class A4
Recorded A4
TNT 2nd Class C5
PP1 2nd Class C5
Recorded A4
PPI 1st Class A4
Recorded C5

With the code it should display following headers in excelsheet:

TNT 2nd Class C5
PP1 2nd Class C5
PPI 1st Class A4
Recorded A4
Recorded C5

Any help would be much appreciated.

Thanks

[aman1234]

Can anyone please help me to do this?

its very urgent to resolve so please guys give me ur ideas.

Thanks

[alansidman]

Here is an alternative solution:

Create an aggregate query in Access and export it directly to Excel

Here is the SQL statement for the Aggregate Query

Please Login or Register  to view this content.

[aman1234]

Thanks. The following code works fine in displaying the headers in the excelsheet. so attached is the excelsheet with the headers in rows and columns. Now I want to display the sum(volume) for each month matching with the EnvelopeType and Envelope Size in the column headers.

Please could you help me in this?

Please Login or Register  to view this content.

[aman1234]

Thanks alansidman

The query displays the right result but Now how to display the output in the right way as in attached sheet and how to export it on the click event of a button?

Thanks

[alansidman]

You can export using the TransferSpreadsheet vba function in Access or use the built in Export Wizard. Then you can cut and pastespecial transpose to get it to look like you wish in excel

Edit: You could also use the exported data to make a Pivot Table.

[alansidman]

Here's a PT created from the query.

[aman1234]

Hi alansidman

The pivot table looks great but when I add more data in Query1 sheet then it doesn't appear in pivot table. Any suggestions?

Many thanks for your help so far.

Aman

[aman1234]

hi alansidman

I am writing the following code to export the query result from Access to Excel. but how to create pivot table of that data using vba code.

Please Login or Register  to view this content.

Thanks

[aman1234]

The following code works perfectly fine. now I just want to add one more condition in this i.e Display those results only where EnvelopeSource='Elevate Bulk'

Please Login or Register  to view this content.

Can you please help me in this.
Thanks