Results 1 to 3 of 3

Thread: Report in excel

  1. #1
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224

    Unanswered: Report in excel

    I want to be able to open my report in excel. Does any one know how to implement this. Currently I am running a query and displaying my fields id, priority, module, status in an access report. But I want to export it with a format in excel. I have attached the excel sheet. You can see how it should look like. Any help would be greatly appreciated. Thank you

  2. #2
    Join Date
    May 2005
    Posts
    119
    Here's some code that I have used to send data to and existing template in excel.

    Function DEP_WaterQualityReportToExcel()
    Dim objExcel As Object
    Dim objXLRange As Object
    Dim varArray As Variant
    Dim rst As Recordset
    Dim mdb As Database
    Dim intcount As Integer
    Dim intI As Integer
    Dim StartDate As Date
    Dim StopDate As Date
    Dim sSql As String

    ssql = "your table or query name, or type your own sql query here"

    Set mdb = CurrentDb
    Set rst = mdb.OpenRecordset(sSql)
    Set objExcel = Nothing
    Set objExcel = CreateObject("excel.application")
    objExcel.Workbooks.Open "filename.xls"

    'This tests to make sure there is data and then dumps the query to excel
    With rst
    .MoveLast
    intcount = .RecordCount
    .MoveFirst
    If GetRowsOK(rst, intcount, varArray) Then
    If intcount > UBound(varArray, 2) + 1 Then
    'Debug.Print "(Not enough records in Recordset to retrieve " & intcount & " rows.)"
    End If
    Debug.Print UBound(varArray, 2) + 1 & _
    " records found."

    Else
    ' Assuming the GetRows error was due to data
    ' changes by another user, use Requery to

    ' refresh the Recordset and start over.
    If .Restartable Then
    If MsgBox("GetRows failed--retry?", vbYesNo) = vbYes Then
    .Requery
    Else
    Debug.Print "GetRows failed!"
    End If
    Else
    Debug.Print "GetRows failed! " & _
    "Recordset not Restartable!"
    End If
    End If
    ' Because using GetRows leaves the current record
    ' pointer at the last record accessed, move the
    ' pointer back to the beginning of the Recordset
    ' before looping back for another search.
    .MoveFirst
    End With
    rst.Close
    Set rst = Nothing
    Set mdb = Nothing

    Dim xlrownum As Integer
    xlrownum = 9 'Put the number of the row you'd like your data to start in here.

    Set objXLRange = objExcel.Range("a" & XLrowNum)
    For intI = 0 To UBound(varArray, 2)
    objExcel.Range("a" & xlrownum) = varArray(0, intI) ' collect date vararray(0, int) is column 1, vararray(1,int) is column2, etc.
    <<YOU MAY REPEAT THIS LINE FOR WHATEVER DATA YOU'D LIKE TO EXPORT>>


    xlrownum = xlrownum + 1
    Next intI
    objExcel.ActiveWorkbook.SaveAs FileName:="newfilename.xls"
    objExcel.Visible = True
    objExcel.Workbooks.Close
    objExcel.Quit
    Set objExcel = Nothing
    Erase varArray

    End Function

    Function GetRowsOK(rstTemp As Recordset, _
    intNumber As Integer, avarData As Variant) As Boolean

    ' Store results of GetRows method in array.
    avarData = rstTemp.GetRows(intNumber)
    ' Return False only if fewer than the desired number of

    ' rows were returned, but not because the end of the
    ' Recordset was reached.
    If intNumber > UBound(avarData, 2) + 1 And _
    Not rstTemp.EOF Then
    GetRowsOK = False
    Else
    GetRowsOK = True
    End If

    End Function


    See, clear as mud. I hope this is helpful - it really does work. I'll be glad to help clear things up if I can.

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    or you can just

    open excel and

    data -> Import External Data -> New DataBaseQuery

    Mite need the cd

    then just point to the Query in question
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •