[AccessD] Help With Excel If Possible

Here ya go Paul:

Function FillAdditionalData(strFilePath As String, dtStart As Date, dtEnd As
Date)
Dim rs As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strSQL As String
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
With cnn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Extended Properties") = "Excel 8.0"
    .Open strFilePath
End With
strSQL = "SELECT Firstname, Lastname, (SELECT Count(Code) FROM `Contract1$`
WHERE Code=""ATT"" AND " & _
"LastName=T1.LastName AND FirstName=T1.FirstName AND Jobdate BETWEEN #" &
dtStart & _
"# AND #" & dtEnd & "#) AS WorkedDays, (SELECT Count(Code) FROM
`Contract1$`" & _
" WHERE Code=""HOL"" AND LastName=T1.LastName AND FirstName=T1.FirstName AND
" & _
"Jobdate BETWEEN #" & dtStart & "# AND #" & dtEnd & "#) AS Holidays, (SELECT
" & _
"Count(Code) FROM `Contract1$` WHERE Code=""SICK"" AND LastName=T1.LastName
AND " & _
"FirstName=T1.FirstName AND Jobdate BETWEEN #" & dtStart & "# AND #" & dtEnd
& _
"#) AS SickDays " & _
"FROM `Contract1$` AS T1 " & _
"WHERE Jobdate BETWEEN #" & dtStart & "# AND #" & dtEnd & "# " & _
"GROUP BY Lastname, Firstname"
rs.Open strSQL, cnn, adOpenKeyset, adLockReadOnly
If rs.EOF = False Then rs.MoveFirst
Do Until rs.EOF = True
    strSQL = "UPDATE `AdditionalData$` SET Worked=" &
rs.Fields("WorkedDays").Value & _
    ", Holiday=" & rs.Fields("Holidays").Value & ", Sick=" &
rs.Fields("SickDays").Value & _
    " WHERE Lastname=""" & rs.Fields("Lastname").Value & _
    """ AND Firstname=""" & rs.Fields("Firstname").Value & """;"
    cnn.Execute strSQL
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
End Function


To use the code above, call it like this:

Private Sub Command1_Click()
FillAdditionalData "C:\test.xls", #1/1/2004#, #4/1/2004#
MsgBox "Done"
End Sub

A few items to note.  For this code to work, the Excel file needs to be
setup the way you explained in the email below.  The sheets have to have the
same names, and so do the first lines of the spreadsheet.  Also, you cannot
have the file open in excel when the code above is running.  In fact, you
don't even need to have excel on the machine that runs this code, since it
is just using ADO.  Which brings me to my last point, you will need to set a
reference to ADO, from whatever you run this code from.  I wrote it in VB
for you, but it will just copy and paste into Access too. (even excel, not
sure if you can run it inside Excel, if the sheet you are working on is
open....didn't test that.).

Have fun!

Drew
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
paul.hartland at fsmail.net
Sent: Thursday, June 10, 2004 6:48 AM
To: accessd
Subject: [AccessD] Help With Excel If Possible


To all,
 
I have a workbook that contains two spreadsheets, first spreadsheet is
called Contract1 and has the following columns of information:
 
Lastname, Firstname, DOW, JobNo, Code, Jobdate, Hours
Hartland    Paul            Mon    12345   ATT    01/01/04   8
Hartland    Paul            Tue    12345   ATT    02/01/04   8
Hartland    Paul            Wed   12345   HOL    03/01/04   8
Hartland    Paul            Thu    12345   ATT    04/01/04   8
Hartland    Paul            Fri      12345   ATT    05/01/04   8
Hartland    Paul            Sat    12345   ATT    06/01/04   8
Hartland    Paul            Sun    12345   ATT    07/01/04   8
Williams     Tom            Mon   12345   HOL    01/01/04   8
Williams     Tom            Tue    12345   SICK   02/01/04   8
Williams     Tom            Wed   12345   ATT    03/01/04   8
Etc, etc
 
 
DOW = Day Of Week (Mon, Tue etc), Code is either ATT, HOL or SICK.
Everything on the first spreadsheet is ok.
 
 
 
The second spreadsheet is called AdditionalData and has the following
columns of information:
 
Lastname, Firstname, Worked, Holiday, Sick
Hartland    Paul
Williams     Tom
 
And that's about where I have got to, and this is where I need your help.  I
need to scan the Contract1 spreadsheet and where the Lastname and Firstname
match from the AdditionalData spreadsheet and say date range
01/01/04-04/01/04 I need to populate the Worked, Holiday and Sick columns
with the count of either ATT, HOL or SICK  so that the result would be like
this:
 
Lastname, Firstname, Worked, Holiday, Sick
Hartland    Paul                3            1           0
Williams     Tom                 1            1          1
 
Thanks in advance to anyone that can help with this or point me in the right
direction.
 
Paul Hartland

-- 

Whatever you Wanadoo:
http://www.wanadoo.co.uk/time/

This email has been checked for most known viruses - find out more at:
http://www.wanadoo.co.uk/help/id/7098.htm
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com