Store data from Access table to excelsheet in the corresponding cells

[aman]

Hi All
I am writing the following code that will first of all display column headers dynamically using "Headers" field data from Access table and then find out the sum(volume) using column header and first column values. The following code works fine to display headers dynamically in Excelsheet from Access table but doesn't display sum(volume) in all the corresponding cells. As I can't attach the Access table so I have stored data from Access table to sheet named "Access Data" as attached. The sheet2 named "Report" should populate total volume .

Public Function Inputdata()
  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim r As Long
  Dim i As Integer
  Dim count As Integer
    ' connect to the Access database
  Set cn = New ADODB.Connection
i = 2
TWP = "J:\Tables"
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=" & TWP & "\Mail.mdb;"
 
 
Set rs = New ADODB.Recordset
Dim wb As Workbook
Dim Dest As Workbook
Dim ws As Worksheet
Dim strsql As String
 
Set wb = ActiveWorkbook
strsql = "select distinct Headers from MailMI where inputdate>= #" & Format(DateSerial(UserForm2.ComboBox3, UserForm2.ComboBox2, UserForm2.ComboBox1), "dd/mm/yyyy") & "# and inputdate <= #" & Format(DateSerial(UserForm2.ComboBox6, UserForm2.ComboBox5, UserForm2.ComboBox4), "dd/mm/yyyy") & "#"
rs.Open strsql, cn
  MsgBox strsql
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
rs.Close
ws.UsedRange.Columns.AutoFit
 'rs.Close
 count = J - 1
 MsgBox count
 'Exit Function
  ws.Rows(1).Font.Bold = True
 
  J = 2
  Do While i <= 10
  Do While J <= count
  strsql = "select sum(volume) from MailMI where EnvelopeSource='" & ws.Cells(i, 1).Value & "' and Headers='" & ws.Cells(i, J).Value & "' group by EnvelopeSource,headers"
  MsgBox strsql
  rs.Open strsql, cn
  ws.Cells(i, J).Value = rs.Fields(0).Value
  J = J + 1
  rs.Close
  Loop
  i = i + 1
  Loop
  'rs.Close
  cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Function

 

[aman]

Can anyone please help me in this? Thanks

 

[The_Doc_Man]

Two options come to mind.

First, you can compute the sum of your data elements from Access via the DSum domain aggregate function and just store it as a number in the appropriate cell. That would be a static sum and would not change if you updated the spreadsheet.

Second, you could store a text value in the Excel cell such as "=Sum(a2:a15)" (or whatever would be the correct sequence.) And you can compute those if necessary. That would be a dynamic sum that would update if you updated a cell in the summation range later.