import access dates into xl "calendar"
 

import access dates into xl calendar

Hello, hopefully you can help me out with a concept I'm struggling with,

I've got a Tasks table in Access with 3 fields: task names, start dates, and
due dates. I'd like to export this data into Excel so that the excel columns
are days of the month (1-31 columns). The rows would contain the Tasks data,
such that a task whose start date falls on the 5th of the month and whose due
date falls on the 31st of the month would fill a unique color to cells 5-31.
Essentially, this would be like a task calendar.

Is this at all possible? any help much appreciated, thanks
Dave

import access dates into xl "calendar"
 

I experimented with this and it seems to work. If someone doesn't post
anything else maybe you can use it:


Sub getData()

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

'connect to the database
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source='C:\Tasks.mdb'"

'select all tasks that start in july
cmd.ActiveConnection = cn
cmd.CommandText = "SELECT * FROM Tasks " & _
"WHERE Start Between " & _
"#7/1/2007# And #7/31/2007#"

Set rs = cmd.Execute

rs.MoveFirst

'color index
cl = 5

'starting row
i = 1

'color the cells
Do While Not rs.EOF

st = DatePart("d", rs.Fields("Start"))
due = DatePart("d", rs.Fields("Due"))

Sheets(1).Cells(i, st) = rs.Fields("Name")

For j = st To due
Sheets(1).Cells(i, j) = _
Sheets(1).Cells(i, j) & " " & j
Sheets(1).Cells(i, j).Interior.ColorIndex = cl
Next
rs.MoveNext

i = i + 1
cl = cl + 1

Loop

End Sub



--
urkec


"DaveMZ" wrote:

import access dates into xl calendar

Hello, hopefully you can help me out with a concept I'm struggling with,

I've got a Tasks table in Access with 3 fields: task names, start dates, and
due dates. I'd like to export this data into Excel so that the excel columns
are days of the month (1-31 columns). The rows would contain the Tasks data,
such that a task whose start date falls on the 5th of the month and whose due
date falls on the 31st of the month would fill a unique color to cells 5-31.
Essentially, this would be like a task calendar.

Is this at all possible? any help much appreciated, thanks
Dave

import access dates into xl "calendar"
 

Thanks urkec, that code works great. I'll play around with it to get the look
I need.

cheers

"urkec" wrote:

I experimented with this and it seems to work. If someone doesn't post
anything else maybe you can use it:


Sub getData()

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

'connect to the database
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source='C:\Tasks.mdb'"

'select all tasks that start in july
cmd.ActiveConnection = cn
cmd.CommandText = "SELECT * FROM Tasks " & _
"WHERE Start Between " & _
"#7/1/2007# And #7/31/2007#"

Set rs = cmd.Execute

rs.MoveFirst

'color index
cl = 5

'starting row
i = 1

'color the cells
Do While Not rs.EOF

st = DatePart("d", rs.Fields("Start"))
due = DatePart("d", rs.Fields("Due"))

Sheets(1).Cells(i, st) = rs.Fields("Name")

For j = st To due
Sheets(1).Cells(i, j) = _
Sheets(1).Cells(i, j) & " " & j
Sheets(1).Cells(i, j).Interior.ColorIndex = cl
Next
rs.MoveNext

i = i + 1
cl = cl + 1

Loop

End Sub



--
urkec


"DaveMZ" wrote:

import access dates into xl calendar

Hello, hopefully you can help me out with a concept I'm struggling with,

I've got a Tasks table in Access with 3 fields: task names, start dates, and
due dates. I'd like to export this data into Excel so that the excel columns
are days of the month (1-31 columns). The rows would contain the Tasks data,
such that a task whose start date falls on the 5th of the month and whose due
date falls on the 31st of the month would fill a unique color to cells 5-31.
Essentially, this would be like a task calendar.

Is this at all possible? any help much appreciated, thanks
Dave