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. 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!