I have two documents: A word template and an excel spreadsheet.
When I use the template, a userform collects two dates using
datetimepickers.
I need to create a connection to the spreadsheet, (using a relative
path - the two documents are in the same folder) using ADO.
I need to create and populate a recordset that finds records between
the two dates, (using the dates as parameters) in column 'myDATE'.
I want to populate the only table in the document (starting with the
second row and adding additional rows for each record) with the
records in the recordset.
I want to total all the values in the 'PAID' column of the recordset
and use the total to fill in a bookmark in the document.
(I think I have to close the connection as well).
That's it.
Can someone please show me the code for doing this. It would be
GREATLY appreciated.
Well, I've slogged through it and I'm almost there. Thanks to help
from Doug Robbins and Peter Jamieson I have been able to piece
together the code I need, (mostly). I have come up with the code
below, which generally works, but which comes with two snags, and is
pretty ugly, (and suggestions to clean it up would be welcome and
quickly implemented).
Snags:
1. The SQL query returns strange results. In my database I have
records for dates 6/1/2009 to 6/19/2009, (except for 6/6/2009,
6/7/2009, and 6/13/2009 through 6/15/2009).
- If I query BETWEEN 6/2/2009 AND 6/9/2009 I gets the records I'd
expect.
- If I query BETWEEN 6/1/2009 AND 6/9/2009 I get all of the records.
- If I query BETWEEN 6/2/2009 AND 6/10/2009 I get the record for
6/2/2009 and all the teen records, (6/10/2009 through 6/19/2009)
- If I query BETWEEN 6/10/2009 AND 6/19/2009 I get records I'd expect.
I'm flabergasted by this one.
2. I want to validate the input from the userform. After the two
dates are taken in, they are used as parameters in a query that fills
a recordset. If, when the recordset it returned, it has 0 records, I
want to to the execution of further code, throw a MsgBox to the user
telling them to try again, and keeping the userform up until input is
given that returns a record. How would I do that?
Any help would help to cool down my brain. Thanks.
My Code:
Private Sub CommandButton1_Click()
With ActiveDocument
.Bookmarks("Text1").Range _
.InsertBefore DTPicker1
.Bookmarks("Text2").Range _
.InsertBefore DTPicker2
End With
UserForm1.Hide
Dim thisSQL As String
thisSQL = "SELECT * FROM [List Of Hours$] WHERE xDATE BETWEEN #" &
DTPicker1 & "# AND #" & DTPicker2 & "#;"
Const thisConnectionString As String = "Provider=Microsoft.Jet.OLEDB.
4.0;" + _
"Data Source=Log.xls;Mode=Read;Extended Properties=""Excel
8.0;HDR=Yes;IMEX=1;"";"
Dim thisSet As Recordset
Set thisSet = New Recordset
Call thisSet.Open(thisSQL, thisConnectionString, 1, 1)
thisSet.MoveFirst
Set objTable = ActiveDocument.Tables(1)
Dim curRow As Integer
Dim curCol As Integer
curRow = 2
curCol = 1
While Not thisSet.EOF
While curCol < 9
If (IsNull(thisSet.Fields(curCol - 1)) <> True) Then _
objTable.Cell(curRow, curCol).Range.Text = CStr
(thisSet.Fields(curCol - 1))
curCol = curCol + 1
Wend
curCol = 1
objTable.Rows.Add
curRow = curRow + 1
thisSet.MoveNext
Wend
thisSet.MoveFirst
Dim pTotal As Single
Dim gTotal As Single
pTotal = 0
gTotal = 0
While Not thisSet.EOF
If (IsNull(thisSet.Fields(6).Value) <> True) Then _
pTotal = pTotal + thisSet.Fields(6).Value
thisSet.MoveNext
Wend
thisSet.MoveFirst
While Not thisSet.EOF
gTotal = gTotal + thisSet.Fields(7).Value
thisSet.MoveNext
Wend
With ActiveDocument
.Bookmarks("Text3").Range _
.InsertBefore CStr(gTotal - pTotal)
.Bookmarks("Text4").Range _
.InsertBefore CStr(pTotal)
.Bookmarks("Text5").Range _
.InsertBefore CStr(gTotal)
End With
thisSet.Close
End Sub
