Insert data after exportation

[yanty]

After I manage to export my data to excel sheet, how do I specify that I would like to insert a text under the last data of the last page? Not at every page but at the page where the last data is at, at the bottom.

this is my code:

Private Sub cmdReport_Click()
Dim oRs As adodb.Recordset
Dim oCnn As adodb.Connection
Dim oApp As Excel.Application
Dim oWB As Excel.Workbook
Dim oSW As Excel.Worksheet
Dim i As Integer
'Connect to your Access db
Set oCnn = New adodb.Connection
oCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
App.Path & "\Car.mdb;Persist Security Info=False"
oCnn.Open

'Create your recordset
Set oRs = New adodb.Recordset
oRs.Open "SELECT Custid, CustCom, CustName FROM Table1 ", oCnn, adOpenKeyset, adCmdText
'Now you will have only 2 columns listed
'Add the field names as column headers (optional)

'Create an instance of Excel and add a new blank workbook
Set oApp = New Excel.Application
Set oWB = oApp.Workbooks.Add
oApp.Visible = True
'Add picture at left header
Set oSW = oWB.Worksheets("Sheet1")
With oSW.PageSetup.LeftHeaderPicture
.FileName = (App.Path & "/pic1.jpg")
.Height = 50
End With
oSW.PageSetup.LeftHeader = "&G"
oSW.PageSetup.HeaderMargin = 30
oSW.PageSetup.TopMargin = 90
'Page Orientation
oSW.PageSetup.Orientation = xlLandscape
'Resize Columns
oSW.Columns("B:B").ColumnWidth = 35
oSW.Columns("C:C").ColumnWidth = 21
'Specify alignment
oSW.Columns.HorizontalAlignment = xlLeft

'Add the field names as column headers (optional)
For i = 0 To oRs.Fields.count - 1
oWB.Sheets(1).Cells(1, i + 1).Value = oRs.Fields(i).Name
Next
oWB.Sheets(1).Range("1:1").Font.Bold = True
oWB.Sheets(1).Cells(2, 1).CopyFromRecordset oRs, , MaxColumns:=3

oRs.Close
Set oRs = Nothing
oCnn.Close
Set oCnn = Nothing
Set oWB = Nothing
Set oApp = Nothing
End Sub

[Webtest]

oWB.Sheets(1).Cells(2, 1).CopyFromRecordset oRs, , MaxColumns:=3

'... Insert new code here
Dim aRow As Long
'Find the bottom Data Row and go down 1 more row
aRow = oSW.UsedRange.Rows.Count + 1
'Use your own Column ID and Text
oSW.Cells(aRow, "C").Value = "MyText"
'... Continue your code

Set oSW = Nothing
oRS.Close

[yanty]

Great! Thanks for the help. :thumb: May I know where I can find reference for the arow function and many more solutions to Excel?

[Webtest]

Yanty ...

aRow is not a function ... it is just a variable of Type Long (per the 'Dim' statement).

If you want to learn VBA, there are a lot of books available. I use (and like!) "Definitive Guide to Excel VBA", Kofler (a! Press) for technical details. "Professional Excel Development", Bullen, Bovey, & Green (Addison Wesley) is good for general and advanced VBA programming concepts and techniques.

Of course, you are always welcome to post on the forums!