Adding Excel Row through VB Code (1 Viewer)

[jfgambit]

I have code that exports a Query to Excel via VB Code. I was hoping that someone knows of code to add rows in Excel to the beginning(2 Rows) and end of the export(1 Row). I have attached an example of what the final export needs to look like in Excel once completed and below is the code. Additionally, the header rows do not need to export from the query.

Thanks for any help!!

Export Code:
Function ExportSCACOTR()

Dim MyRS As DAO.Recordset
Dim SCACcd As String
Dim MyStr As String
Dim MyQD As QueryDef
txDate = Format(Date, "mmddyy")

'Open query that Groups each individual SCAC code
'Open the query that pulls the On Time Performance information for each Carrier
Set MyRS = CurrentDb.OpenRecordset("qryCarrierSCACGrouping")
Set MyQD = CurrentDb.QueryDefs("qryCarrierExportModule")

'Move to the first SCAC
MyRS.MoveFirst
SCACcd = MyRS("SCAC")

'Select the information based on the SCAC selected by MyRS
MyStr = ("SELECT * from [tempDataExport] where SCAC = '" & SCACcd & "';")
MyQD.SQL = MyStr

'Export the information to an Excel sheet
DoCmd.OutputTo acOutputQuery, "qryCarrierExportModule", "MicrosoftExcel(*.xls)", "C:\Transportation\OTR_" & SCACcd & "_" & txDate & ".xls"

'Close the queries
MyRS.Close
MyQD.Close

End Function

 

[madhouse]

Here is the code I've used to add data to the last row of an Excel Worksheet:

Dim dbs As Database
Dim rs As Recordset
Dim ExpDate As Date
Dim objXL As Excel.Application
Dim objSht As Excel.Worksheet
Dim iRow As Integer
Dim iCol As Integer
Dim FinalRow As Integer
Dim CostCode As Integer
Dim TotalSize As Variant


'********First export the data*********

'---Refer to Microsoft Knowledge Base Article Q319832
'---Always fully qualify the Workbook objects that the code refers to

Set objXL = CreateObject("Excel.Application")

objXL.Application.Workbooks.Open ("D:\Databases\DASD AS4\Libsize.xls")

Set objSht = objXL.Worksheets("Total Size")

FinalRow = objSht.Range("A65536").End(xlUp).Row

iRow = FinalRow + 1
iCol = 2

ExpDate = Date

objSht.Cells(iRow, 1).Value = ExpDate

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblCodes", dbOpenDynaset)

Do While Not rs.EOF
CostCode = rs!CodeRef

TotalSize = CDec(DSum("LibSize", "tblLibs", "LibDesc like '*" & CStr(CostCode) & "*'"))

objSht.Cells(iRow, iCol).Value = Round(TotalSize / GBYTES, 2)
iCol = iCol + 1

rs.MoveNext
Loop
rs.Close

' --- save the updated workbook
objXL.Workbooks("Libsize.xls").Save

Set objSht = Nothing

Set rs = Nothing
Set dbs = Nothing

objXL.Application.Quit
Set objXL = Nothing


The bit of code that determines where the new row will be added is FinalRow = objSht.Range("A65536").End(xlUp).Row . Other examples of the End property are:

This example selects the cell at the top of column B in the region that contains cell B4.

Range("B4").End(xlUp).Select

This example selects the cell at the end of row 4 in the region that contains cell B4.

Range("B4").End(xlToRight).Select

This example extends the selection from cell B4 to the last cell in row four that contains data.

Worksheets("Sheet1").Activate
Range("B4", Range("B4").End(xlToRight)).Select


Hope that helps.