export access data to specific cells in excell 1

[boomer1974]

good day experts,

I'm working on a project that I have not been able to complete and I could really use some help. I have an access query that has two fields, date and incident code. In excel I have a grid, 12 rows and 31 columns, one row for each month and one column for each day of the month. I need to find a way for access to plug the incident code in the excel grid on the appropriate date. Any help would be greatly appreciated.

boomer

 

[VBslammer]

Here's an example. I used named ranges to the left of each row in the format "JAN", "FEB", etc. I also named the entire calendar area "CALENDAR" so I could clear its contents before processing. The function takes the year as an argument such as:

 Call OutPutIncidents("2003")

EXAMPLE

Sub OutputIncidents(ByVal strYear As String)
On Error GoTo ErrHandler
  Dim rst As Recordset
  Dim db As Database
  Dim strSQL As String
  Dim xl As Excel.Application
  Dim wb As Excel.Workbook
  Dim sht As Excel.Worksheet
  Dim rng As Excel.Range
  
  strSQL = "SELECT [IncidentDate], [IncidentCode] FROM Incidents "
  strSQL = strSQL & "WHERE [IncidentDate] BETWEEN "
  strSQL = strSQL & "#1/1/" & strYear & "# AND #12/31/" & strYear & "# "
  strSQL = strSQL & "ORDER BY [IncidentDate]"
  
  Set db = CurrentDb()
  Set rst = db.OpenRecordset(strSQL)
  
  With rst
    If .RecordCount > 0 Then
      Set xl = New Excel.Application
      Set wb = xl.Workbooks.Open("C:\Incidents.xls", AddToMRU:=False)
      Set sht = wb.Sheets("Incident Dates")
      sht.Range("CALENDAR").ClearContents
      While Not .EOF
        Set rng = sht.Range(UCase(Format(.Fields("IncidentDate"), "MMM")))
        rng.Offset(0, Day(.Fields("IncidentDate"))) = .Fields("IncidentCode")
        .MoveNext
      Wend
    End If
  End With
ExitHere:
  On Error Resume Next
  wb.Close True
  xl.Quit
  Exit Sub
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Sub

VBSlammer

Unemployed in Houston, Texas

 

[boomer1974]

VBSlammer,

I got two words for ya. YOU RULE!!! Thanks a million

Boomer

 

[MikeGK]

I am trying to export some report data from access to excel so that I can make pie charts and bar charts in excell. I was searching the forum and I came accross this thread.
I tried this example to get an idea of how to transfer some data to excell but It didn't work. I got the following error message: "Method 'Range' of object '_Worksheet' failed".
I know a lot about VBA coding with DAO but I don't know much about excel. Can you please tell me some more details about how to run this? I created a database with a table called "Incidents" with two fields IncidentDate and IncidentCode and entered some data in it. Do I need to create the insidents.xls or is it created by the code? If I need to create it how do I create the named ranges?