none
Importing into excel from an access crosstab query RRS feed

  • Question

  • I would like to import data into a spreadsheet from an access crosstab query.

    I have been able to establish the data link to the access database query but I cannot get the data from the query into my spreadsheet.

    Does anyone have any ideas how I can get the data to pass from the database query to the excel spreadsheet.

    Thursday, November 24, 2011 1:23 AM

Answers

  • Hi Mark

    Mark Bristoll wrote:

    I have been able to establish the data link to the access database query but I cannot get the data from the query into my spreadsheet.

    I do this normally with OLE automation from Access to Excel. I use following approach:

         Dim db As Database()
         Dim qdf As QueryDef
         Dim rs As Recordset
         Dim J As Long
         Dim xls As Object    'your Excel.Application with sheet activated
         Set db = CurrentDB()
         Set qdf = db.QueryDefs("YourCrossTabQuery")
         Set rs = qdf.OpenRecordset
         With xls.Activesheet
             For J = 0 To rsv.Fields.Count - 1
                 .Cells(1, J + 1).Value = rs.Fields(J).Name
             Next
             .Range("A2").CopyFromRecordset rs
         End With
         rs.Close

    If you have your query ready then the rest should work similar in Excel

    HTH
    Henry

    Thursday, November 24, 2011 6:35 AM
  • My approach is to import teh data in a tall narrow data set (Eg one record per day or week) then use Excel's Pivot table feature to create the cross tab report.

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    Thursday, November 24, 2011 7:59 AM

All replies

  • Hi Mark

    Mark Bristoll wrote:

    I have been able to establish the data link to the access database query but I cannot get the data from the query into my spreadsheet.

    I do this normally with OLE automation from Access to Excel. I use following approach:

         Dim db As Database()
         Dim qdf As QueryDef
         Dim rs As Recordset
         Dim J As Long
         Dim xls As Object    'your Excel.Application with sheet activated
         Set db = CurrentDB()
         Set qdf = db.QueryDefs("YourCrossTabQuery")
         Set rs = qdf.OpenRecordset
         With xls.Activesheet
             For J = 0 To rsv.Fields.Count - 1
                 .Cells(1, J + 1).Value = rs.Fields(J).Name
             Next
             .Range("A2").CopyFromRecordset rs
         End With
         rs.Close

    If you have your query ready then the rest should work similar in Excel

    HTH
    Henry

    Thursday, November 24, 2011 6:35 AM
  • My approach is to import teh data in a tall narrow data set (Eg one record per day or week) then use Excel's Pivot table feature to create the cross tab report.

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    Thursday, November 24, 2011 7:59 AM
  • thank you
    Friday, November 25, 2011 2:58 AM
  • thank you
    Friday, November 25, 2011 2:58 AM