Excel by recordset

**DMT Dave** · 03-10-2019, 06:26 AM

Hi Guy's, can anyone help with this one please ?

I have got an Excel sheet that is all formatted apart from the bottom border and totals which would be 3 rows after recordset count, the sheet used to hold 22 records but then i took out the totals rows (23,24,25)

My targer is to add these total rows in after the amount of records are added then place a bottom border on it, i have only written this code by logic but doesn't work!!! thank you for any replies and help you can offer

Code:

Set rs = CurrentDb.OpenRecordset("SELECT tblInvoices.InvoiceDate, tblInvoices.InvoiceNumber, tblInvoices.Customer, tblInvoices.Item, tblInvoices.Ref, tblInvoices.Paid, tblInvoices.PaidDate, " _
& "tblInvoices.Price From tblInvoices " _
& "WHERE tblInvoices.InvoiceDate Between #" & Forms!frmMainMenu!cboStartDate & "# And #" & Forms!frmMainMenu!cboEndDate & "#" & " And Source = '" & mSource & "'")
mTotal = DSum("Price", "tblInvoices", "[InvoiceDate] Between #" & Forms!frmMainMenu!cboStartDate & "# And #" & Forms!frmMainMenu!cboEndDate & "#" & " And Source = '" & mSource & "'") ' new to test
rsQty = rs.RecordCount ' new to test
tQty = DCount("Item", "tblInvoices", "[InvoiceDate] Between #" & Forms!frmMainMenu!cboStartDate & "# And #" & Forms!frmMainMenu!cboEndDate & "#" & " And Source = '" & mSource & "'") ' new to test
SheetQty = "22" ' new to test
AddQty = tQty - rs.RecordCount  ' new to test
If tQty >= rs.RecordCount Then  ' new to test
AddQty = tQty - rs.RecordCount  ' new to test
Else ' new to test
AddQty = "0" ' new to test
End If ' new to test
With xLWB
.Worksheets(1).Cells(12 + AddQty, 1).EntireRow.Insert ' new to test supposed to add the correct amount of rows according to recordset qty 
.Worksheets(1).Cells(12, 1).CopyFromRecordset rs
.Worksheets(1).Cells(12+ rs.RecordCount + 1, 8) = mTotal ' supposed to add total amount after recordset qty 
.Worksheets(1).Cells(12 + rs.RecordCount + 2, 8) = mpY ' supposed to add total amount PAID after recordset qty and mTotal
.Worksheets(1).Cells(12 + rs.RecordCount + 3, 8) = mP ' supposed to add total amount UNPAID after recordset qty and mTotal and Paid
.Worksheets(1).Cells.EntireColumn.AutoFit
.Worksheets(1).Cells.EntireColumn.HorizontalAlignment = xlLeft
.Worksheets(1).Range("A & rs.RecordCount + 4:" & rs.RecordCount & 9).Borders(xlEdgeBottom).LineStyle = xlContinuous ' DOESNT WORK, meant to add a bottom border after all of the above ??
.Save
End With
xLWB.Close
apXL.Quit
Set apXL = Nothing

**CJ_London** · 03-10-2019, 06:40 AM

'doesn't work' doesn't help us to help you.

Your code is missing quite a bit - perhaps you haven't bothered to show it, perhaps you haven't done it - we don't know.
are you running this code in Access or Excel? - we don't know
all your comments - you say what it is supposed to do - you don't say what it is actually doing - so again, we don't know

**DMT Dave** · 03-10-2019, 06:51 AM

HERE IS THE FULL CODE, i am trying to add totals after recordset transfer, this is full code to try it....

Code:

Dim xLWB As Object
Dim apXL As Excel.Application
Dim fOpen As String, pOpen As String, fSave As String, pSave As String, mSource As String, mP As String, mpY As String
Dim rs As DAO.Recordset, rs2 As DAO.Recordset
Dim mStart As Date, mEnd As Date
Dim PaidYes As Currency, PaidNo As Currency, mTotal As Currency
Dim tQty As Long, rsQty As Long, SheetQty As Long, AddQty As Long


mSource = Me.cboReportOptions
mStart = Forms!frmMainMenu!cboStartDate
mStart = Forms!frmMainMenu!cboEndDate

If IsNull(PaidYes = DSum("Price", "tblInvoices", "[Source] = '" & mSource & "' And [InvoiceDate] Between #" & mStart & "# And #" & mEnd & "# And [Paid] = Yes")) Then
PaidYes = "00"
Else
PaidYes = DSum("Price", "tblInvoices", "[Source] = '" & mSource & "' And [InvoiceDate] Between #" & mStart & "# And #" & mEnd & "# And [Paid] = Yes")
mpY = "Yes"
End If
If IsNull(PaidNo = DSum("Price", "tblInvoices", "[Source] = '" & mSource & "' And [InvoiceDate] Between #" & mStart & "# And #" & mEnd & "# And [Paid] = No")) Then
PaidNo = "00"
Else
PaidNo = DSum("Price", "tblInvoices", "[Source] = '" & mSource & "' And [InvoiceDate] Between #" & mStart & "# And #" & mEnd & "# And [Paid] = No")
mP = "No"
End If


pOpen = "T:\MY FOLDER\XL Files\REPORTS\WCBYS\"
fOpen = "REPORT TEMPLATE" & ".xlsx"
pSave = "T:\MY FOLDER\XL Files\REPORTS\WCBYS\"
fSave = "From " & Format(Me.cboStartDate, "dd-mm-yy") & " To " & Format(Me.cboEndDate, "dd-mm-yy") & ".xlsx"
Set apXL = CreateObject("Excel.Application")
Set xLWB = apXL.Workbooks.Open(pOpen & fOpen)
apXL.ActiveWorkbook.SaveAs pSave & fSave
apXL.Workbooks.Open pSave & fSave, True, False
apXL.Visible = False

Set rs = CurrentDb.OpenRecordset("SELECT tblInvoices.InvoiceDate, tblInvoices.InvoiceNumber, tblInvoices.Customer, tblInvoices.Item, tblInvoices.Ref, tblInvoices.Paid, tblInvoices.PaidDate, " _
& "tblInvoices.Price From tblInvoices " _
& "WHERE tblInvoices.InvoiceDate Between #" & Forms!frmMainMenu!cboStartDate & "# And #" & Forms!frmMainMenu!cboEndDate & "#" & " And Source = '" & mSource & "'")
mTotal = DSum("Price", "tblInvoices", "[InvoiceDate] Between #" & Forms!frmMainMenu!cboStartDate & "# And #" & Forms!frmMainMenu!cboEndDate & "#" & " And Source = '" & mSource & "'") ' new to test
rsQty = rs.RecordCount ' new to test
tQty = DCount("Item", "tblInvoices", "[InvoiceDate] Between #" & Forms!frmMainMenu!cboStartDate & "# And #" & Forms!frmMainMenu!cboEndDate & "#" & " And Source = '" & mSource & "'") ' new to test
SheetQty = "22" ' new to test
AddQty = tQty - rs.RecordCount  ' new to test
If tQty >= rs.RecordCount Then  ' new to test
AddQty = tQty - rs.RecordCount  ' new to test
Else ' new to test
AddQty = "0" ' new to test
End If ' new to test
With xLWB
.Worksheets(1).Cells(12 + AddQty, 1).EntireRow.Insert ' new to test
.Worksheets(1).Cells(12, 1).CopyFromRecordset rs
.Worksheets(1).Cells(rs.RecordCount + 1, 8) = mTotal
.Worksheets(1).Cells(rs.RecordCount + 2, 8) = mpY
.Worksheets(1).Cells(rs.RecordCount + 3, 8) = mP
.Worksheets(1).Cells.EntireColumn.AutoFit
.Worksheets(1).Cells.EntireColumn.HorizontalAlignment = xlLeft
.Worksheets(1).Range("A & rs.RecordCount + 4:" & rs.RecordCount & 9).Borders(xlEdgeBottom).LineStyle = xlContinuous
.Save
End With
xLWB.Close
apXL.Quit
Set apXL = Nothing

**CJ_London** · 03-10-2019, 07:15 AM

doesn't look like you have put it in a function or sub - have you used option explicit?

are you running this in access or excel?

you still haven't explained what 'doesn't work' means

**CJ_London** · 03-10-2019, 07:23 AM

further to above, The only bit of 'doesn't work' I can see relates to the bottom border. Your code is latebinding Excel so I assume this is running in Access. So far as I can see you have not declared 'xlContinuous' anywhere as a constant. If you had Option Explicit at the top of the module, this would have been highlighted when you compiled the code.

**DMT Dave** · 03-10-2019, 07:24 AM

It is a private sub (after update) of a combo box (cboEndDate)

It is access code

I am wanting regardless of how many records to add the full recordset then by counting the records (rs.recordcount) add a further 3 rows and have the totals in 3 rows

Does this explain properly ? forgive me if not and I will try again!!

Kind regards

**CJ_London** · 03-10-2019, 07:30 AM

to get a valid count, you need to .movelast, otherwise you will get zero

Set rs = CurrentDb.OpenRecordset("SELECT.....
rs.movelast
rsQty = rs.RecordCount

or perhaps use the excel currentregion functionality to determine the number of rows once it has been populated

**DMT Dave** · 03-10-2019, 07:47 AM

Ahh ok, i will try to move the recordset to last as suggested, thank you very much

**pbaldy** · 03-10-2019, 01:22 PM

FYI a method of getting the last row after inserting data:

Code:

lngLastRow = xl.Cells.SpecialCells(11).Row

xl being my Excel variable. You can also build sum functions at the bottom of the worksheet after inserting data. Here's an example:

Code:

xlSheet.range("A11").copyfromrecordset rs

lngLastRow = xl.Cells.SpecialCells(11).Row
xl.Cells(lngLastRow + 2, 12) = "=sum(L11:L" & lngLastRow & ")"

**Micron** · 03-10-2019, 03:14 PM

Range("A & rs.RecordCount + 4:"
isn't this going to be interpreted as a literal string?
Shouldn't it be
Range("A" & rs.RecordCount + 4 & ":"

Then in the next part
rs.RecordCount & 9)

what gets appended is (e.g. rs.count is 22) is 229
so "A26:229" ?

**DMT Dave** · 03-11-2019, 12:59 AM

Thank you guy's will try this later today

**DMT Dave** · 03-11-2019, 01:09 AM

Another question regarding the same code, when it outputs to the excel, the Paid column (mP and mPY) i have tried the change from True and False to Yes and No so it appears on the excel sheet as True and False

It still appears as True and False because it's a check box, I have already set the format of the check box in the table as Yes/No

??

Regards

**CJ_London** · 03-11-2019, 03:24 AM

format is a property. it just affects how the data is displayed, not how it is stored. Format properties are not normally exported, the underlying value is which is -1 for true and 0 for false. In Excel format the cells with this custom format

;"Yes";"No"

Excel by recordset

Thread Tools

Excel by recordset

Similar Threads

How to Export subform Recordset to Excel

VBA To Export Recordset To Excel

Export Recordset to Excel

From Recordset to excel sheet

Working with ADO Recordset & Excel

Posting Permissions