Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ACCESS ="NO" BUT EXCEL DISPLAYS "0" | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |