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
Here's some code that I have used to send data to and existing template in excel.
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")
'This tests to make sure there is data and then dumps the query to excel
intcount = .RecordCount
If GetRowsOK(rst, intcount, varArray) Then
If intcount > UBound(varArray, 2) + 1 Then
'Debug.Print "(Not enough records in Recordset to retrieve " & intcount & " rows.)"
Debug.Print UBound(varArray, 2) + 1 & _
" records found."
' 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
Debug.Print "GetRows failed!"
Debug.Print "GetRows failed! " & _
"Recordset not Restartable!"
' 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.
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
objExcel.Visible = True
Set objExcel = Nothing
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
GetRowsOK = True
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.
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.