Access VBA creating an Excel spreadsheet with a total line

P

PSULionRP

I have an Access module which dynamically creates an Excel spreadsheet. Now
the Business User has asked that we put a Total Line in the Excel spreadsheet.

How do I within Access VBA determine the range, because the spreadsheet will
never be the same, and then use that range to create a formula to insert to
the local recordset which dynamically creates the Excel spreadsheet with the
Total Line???

Thanks in advance for your review and help and hopeful for a reply.

PSULionRP
 
J

James A. Fortune

I have an Access module which dynamically creates an Excel spreadsheet. Now
the Business User has asked that we put a Total Line in the Excel spreadsheet.

How do I within Access VBA determine the range, because the spreadsheet will
never be the same, and then use that range to create a formula to insert to
the local recordset which dynamically creates the Excel spreadsheet with the
Total Line???

Thanks in advance for your review and help and hopeful for a reply.

PSULionRP
Click to expand...

If the first empty data row indicates the end of the data, combine:

http://groups.google.com/group/microsoft.public.access/browse_frm/thread/aced3b336e50b264

with VBA code in Access something like (air code):

For lngJ = 1 To lngMaxNumberOfRowsWithData
boolContinue = False
For I = 1 To intNumberOfColumnsWithData
If objXL.Cells(I, lngJ).Value <> "" Then
boolContinue = True
Exit For 'Found some data in the row so go to the next row
End If
Next I
If boolContinue = False Then Exit For 'All data columns are empty
Next lngJ
If lngJ <> lngMaxNumberOfRowsWithData Then
MsgBox("Put the SUM formulae in row " & CStr(lngJ))
Else
MsgBox("Put the SUM formulae in row " & CStr(lngJ + 1))
End If

James A. Fortune
[email protected]
 
J

James A. Fortune

I have an Access module which dynamically creates an Excel spreadsheet. Now
the Business User has asked that we put a Total Line in the Excel spreadsheet.
Click to expand...

I should have read that more closely. If you are creating the
spreadsheet, you know where the data ends and can put the SUMs in
right then, correct?.

James A. Fortune
[email protected]
 
A

aleyna

iletisinde şunu yazdı said:
I have an Access module which dynamically creates an Excel spreadsheet. Now
the Business User has asked that we put a Total Line in the Excel
spreadsheet.

How do I within Access VBA determine the range, because the spreadsheet
will
never be the same, and then use that range to create a formula to insert
to
the local recordset which dynamically creates the Excel spreadsheet with
the
Total Line???

Thanks in advance for your review and help and hopeful for a reply.

PSULionRP

__________ ESET NOD32 Antivirus Ak�ll� G�venlik taraf�ndan sa�lanan
bilgiler, vir�s imza veritaban� s�r�m�: 4716 (20091225) __________

�leti ESET NOD32 Antivirus Ak�ll� G�venlik taraf�ndan denetlendi.

http://www.nod32.com.tr
Click to expand...
 
Top