Dynamically create headers in Excelsheet using Access table data (1 Viewer)

aman · Apr 2, 2014

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 Access table.
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

namliam · Apr 2, 2014

Howabout making a crosstab query, something along the lines of:

Code:

TRANSFORM Sum(tblmain.Volume) AS SumOfVolume
SELECT Month([InputDate]) AS Expr1
FROM tblmain
GROUP BY Month([InputDate])
PIVOT tblmain.EnvelopeSize;

aman · Apr 2, 2014

Thanks namliam but how to write the code in Excel vba that will check data in Access table and then insert headers in Sheet1.

Many thanks

namliam · Apr 2, 2014

well you can do a
Docmd.Transferspreadsheet

From access.... or you can try something like I posted here:
http://www.access-programmers.co.uk/forums/showthread.php?t=259994
Though that too is from access to excel, but should be easy enough to turn that around.

aman · Apr 2, 2014

Namliam, Its a part of the project I am doing and in this I have to write down the code ion Excel/vba which is the frontend and Access is the backend.

On the button press even I want the code to run and store headers in Excelsheet from the values in Access table.

I hope you can help me in this.

Many thanks

namliam · Apr 2, 2014

Think I already did, or did you even try the code I linked you?

aman · Apr 2, 2014

Hi namliam. Yes thanks for that. 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.

Code:

Public Function Inputdata()
Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim r As Long
    ' connect to the Access database
  Set cn = New ADODB.Connection
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=C:\db3.mdb;"
        
Set rs = New ADODB.Recordset
Dim wb As Workbook
Dim Dest As Workbook
Dim ws As Worksheet
     
Set wb = ActiveWorkbook
MsgBox (Format(DateSerial(2014, 2, 1), "dd/mm/yyyy"))
MsgBox Format(DateSerial(2014, 2, 30), "dd/mm/yyyy")
strsql = "select distinct(EnvelopeType & ' ' & EnvelopeSize) from tblmain where inputdate>= #" & Format(DateSerial(2014, 2, 1), "dd/mm/yyyy") & "# and inputdate <= #" & Format(DateSerial(2014, 2,28), "dd/mm/yyyy") & "#"
  rs.Open strsql, cn
  
  Set ws = ThisWorkbook.Worksheets("Sheet2")
Dim j As Integer
j = 2
rs.MoveFirst
Do While Not rs.EOF
ws.Cells(1, j).Value = rs.Fields(0).Value
j = j + 1
rs.MoveNext
Loop
ws.UsedRange.Columns.AutoFit
 rs.Close
 
  ws.Rows(1).Font.Bold = True
        cn.Close
     
    Set rs = Nothing
    Set cn = Nothing
End Function

Many thanks for your help so far.

namliam · Apr 2, 2014

Why not run the crosstab query in access and populate that into the spreadsheet???

I know I sound like a broken record, but that IS the best/easiest way of doing it.

Your code looks NOTHING like my code I linked, which actually uses:
objWS.Cells(2, 1).CopyFromRecordset objRS

To copy the data from the DAO recordsset into the spreadsheet, instead of your ADO way.... but that may be a consesion you need to do in excel but dont think its "really needed" though ADO vs DAO certainly from an excel pov doesnt really matter (much)

aman · Apr 2, 2014

Can you please tell me how to write crosstab query in Access and populate that in Excel?

Sorry to bother you but Its very urgent.

Many thanks for your help so far

aman · Apr 2, 2014

namliam, please see attached the database. I have written a query named query1. Now how to display the output in the right way as in attached sheet.

Thanks

namliam · Apr 2, 2014

Your query1 has 3 crossable values and one "display" value.
If your problem is urgent, simply do as I suggested already about 7 hours ago... have a look at the code I linked and rework it a litlle.

aman · Apr 8, 2014

Hi namliam

I am writing the following code and it does work but doesn't display the data in the way I want.

Code:

Private Sub Command0_Click()
strsql = "SELECT Month([InputDate]) AS Mnth, tblmain.EnvelopeSize, tblmain.EnvelopeType, Sum(Tblmain.Volume) AS SumOfVolume FROM Tblmain GROUP BY Month([InputDate]), Tblmain.EnvelopeSize, Tblmain.EnvelopeType;"
If DCount("Name", "MSysobjects", "Name='qrytemp' and type=5") > 0 Then
DoCmd.DeleteObject acQuery, "qrytemp"
End If
Set qdf = CurrentDb.CreateQueryDef("qrytemp", strsql)
'need to add a reference to Microsoft Excel 11.0 (or the version you have) Object Library
    Dim objXls As Excel.Application
    Dim objWrkBk As Excel.Workbook
    Dim xprtFile As String
   xprtFile = "C:\RReport.xls"
   'DoCmd.OutputTo acOutputQuery, "qrytemp", acFormatXLS, xprtFile, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qrytemp", xprtFile, False
       Set objXls = New Excel.Application
    objXls.Visible = True
   
    Set objWrkBk = objXls.Workbooks.Open(xprtFile)
   ' Set objWrkBk = objXls.Workbooks.Open(xprtFile)
    objWrkBk.Sheets("qrytemp").Select
    With objWrkBk.Sheets("qrytemp")
           .Application.Rows("1:1").Select
        .Application.Selection.Font.Bold = True
    End With
        
       'objXls.DisplayAlerts = False
       'objWrkBk.Close SaveChanges:=False
       'objXls.DisplayAlerts = True
        objWrkBk.Save
        Set objWrkBk = Nothing
   'objXls.Quit
    
    Set objXls = Nothing
Set qdf = Nothing
End Sub

Is there anyway to create pivot table for something like that so that the data is displayed in the exact way we want.

Thanks

namliam · Apr 8, 2014

How about if you use this as your crosstab query?

Code:

TRANSFORM Sum(Tblmain.Volume) AS SumOfVolume
SELECT Month([InputDate]) AS Mnth
FROM Tblmain
GROUP BY Month([InputDate])
PIVOT [EnvelopeType] & "- " & [EnvelopeSize];

aman · Apr 8, 2014

Thanks namliam but its giving me type mismatch error in the following line:

Code:

strsql = "TRANSFORM Sum(Tblmain.Volume) AS SumOfVolume SELECT Month([InputDate]) AS Mnth FROM Tblmain GROUP BY Month([InputDate]) PIVOT [EnvelopeType] & " - " & [EnvelopeSize];"

aman · Apr 8, 2014

namliam, it worked perfectly fine. Now I want to put one more condition i.e it should only look for data where EnvelopeSource='Elevate Bulk'. Rest all is fine.

Code:

strsql = "TRANSFORM Sum(Tblmain.Volume) AS SumOfVolume SELECT Month([InputDate]) AS Mnth FROM Tblmain GROUP BY Month([InputDate]) PIVOT [EnvelopeType] & ' - ' & [EnvelopeSize];"

namliam · Apr 8, 2014

cant use " inside a string replace it by ' or ""
And please keep SQL or code readable, splashing it on one line isnt readable...

Code:

strsql = " TRANSFORM Sum(Tblmain.Volume) AS SumOfVolume " & _
         " SELECT Month([InputDate]) AS Mnth  " & _
         " FROM Tblmain  " & _
         " GROUP BY Month([InputDate])  " & _
         " PIVOT [EnvelopeType] & "" - "" & [EnvelopeSize];"

That (to me) is (more) readable

aman · Apr 8, 2014

namliam, have you seen my previous post. Actually I want to look for only that data where EnvelopeSource="Elevate Bulk'. Rest all is same.

so how to put this condition in the query?

Many thanks

namliam · Apr 8, 2014

Code:

strsql = " TRANSFORM Sum(Tblmain.Volume) AS SumOfVolume " & _
         " SELECT Month([InputDate]) AS Mnth  " & _
         " FROM Tblmain  " & _
" WHERE EnvelopeSource='Elevate Bulk' " & _
         " GROUP BY Month([InputDate])  " & _
         " PIVOT [EnvelopeType] & "" - "" & [EnvelopeSize];"

Overlooked your second post I guess, sorry

Something like above will that do ?

If you want to cycle thru names perhaps something like:

Code:

strsql = " TRANSFORM Sum(Tblmain.Volume) AS SumOfVolume " & _
         " SELECT Month([InputDate]) AS Mnth  " & _
         " FROM Tblmain  " & _
" WHERE EnvelopeSource='"  & YourVariable &  "' " & _
         " GROUP BY Month([InputDate])  " & _
         " PIVOT [EnvelopeType] & "" - "" & [EnvelopeSize];"

aman · Apr 8, 2014

Thanks namliam, It worked gr8. Now when the data gets exported in excelsheet then is there any way we can autofit the columns width? something like ws.usedrange.columns.autofit in excel/vba.

namliam · Apr 8, 2014

If you go back to "my" source you will find:

Dim objWS As Excel.Worksheet

from which you can do whatever you want to an excel worksheet

Dynamically create headers in Excelsheet using Access table data (1 Viewer)

Registered User.

Attachments

The Mailman - AWF VIP

Registered User.

The Mailman - AWF VIP

Registered User.

The Mailman - AWF VIP

Registered User.

Attachments

The Mailman - AWF VIP

Registered User.

Registered User.

Attachments

The Mailman - AWF VIP

Registered User.

The Mailman - AWF VIP

Registered User.

Registered User.

The Mailman - AWF VIP

Registered User.

The Mailman - AWF VIP

Registered User.

The Mailman - AWF VIP

Similar threads

Users who are viewing this thread