Setting the range of an existing excel workbook to the first empty row

[iojbr]

Hi:

I used the following code to export data from an Access Table ("Invoice Data") to an existing .xlsm workbook ("Control Chart Test.xlsm). The first row that will receive the data from "Invoice Data" will be A14. How do I define the range so that data will be inserted into the first empty row from A14 on everytime this subroutine is executed, rather than having it overwrite existing data. Hope I was clear enough. Thanks in advance.


Private Sub Command6_Click()

Dim Cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim oExcelApp As Excel.Application
Dim lngFieldCounter As Long
Dim blnFileExists As Boolean
Dim blnExcelRunning As Boolean
Dim oTargetSheet As Excel.Worksheet
Set Cnn = CurrentProject.Connection
Set rs1 = New ADODB.Recordset
rs1.Open "Select [Run Date],[Field5] from [Invoice Data]", Cnn, adOpenDynamic, adLockOptimistic
Set oExcelApp = GetObject("", "Excel.Application")
If Err.Number <> 0 Then
blnExcelRunning = False
Set oExcelApp = CreateObject("Excel.Application")
Else
blnExcelRunning = True
End If
If Dir("C:\XL Test\Control Chart Test.xlsm") <> "" Then
blnFileExists = True
oExcelApp.Workbooks.Open FileName:="C:\XL Test\Control Chart Test.xlsm"
Else
oExcelApp.Workbooks.Add
End If
If IsEmpty("BRK2") = False Then
On Error Resume Next
Set oTargetSheet = oExcelApp.ActiveWorkbook.Sheets("BRK2")
If Err.Number <> 0 Then
Set oTargetSheet = oExcelApp.ActiveWorkbook.Sheets.Add
oTargetSheet.Name = "BRK2"
End If
Else
Set oTargetSheet = oExcelApp.ActiveWorkbook.Sheets.Add
End If
oTargetSheet.Range("A14").CopyFromRecordset rs1

rs1.Close
If blnFileExists Then
oExcelApp.ActiveWorkbook.Save
Else
oExcelApp.ActiveWorkbook.SaveAs FileName:="C:\XL Test\Control Chart Test.xlsm"
End If

oExcelApp.ActiveWorkbook.Close

If Not blnExcelRunning Then
oExcelApp.Quit
Set oExcelApp = Nothing
End If

Set rs1 = Nothing
ExportToExcel = True

End Sub

 

[PizMac]

this is excel 2003 but does it help?

NoRowsinMySheet = MySheet.UsedRange.Rows(MySheet.UsedRange.Rows.Count).Row()

this will give you the last used row and you can work it from there?

 

[iojbr]

Hi. Thanks for the reply. I am still having problems. In my excel sheet, I want the code to start determining the used range from a14, and not a1. The excel sheet is set up so that a14 will be the first row that contains actual data. Prior to a14 there are cells that contain instructions and labelsseperated by fields that contain no data that I don't want the code to count in determining the used range. I have tried working with the example the example that you gave as well, and I still can't get it to work.

 

[iojbr]

Would this work:

Sub LastCellBeforeBlankInRow()
Dim X As String
X = MyExcelSheet.Range("A13").End(xlToRight).Offset(1, 0).Select
MyExcelSheet.Range(X).CopyFromRecordset rs1
End Sub

'With the Excel sheet I am trying to export to A13 contains a column heading, and a14 represents the first cell that will conatin actual data. I appreciate the help.

 

[PizMac]

you always want to start in A14 ?
your e.g. might work - haven't you tested it?

I was copying from another excel sheet but can't you just do
MyExcelSheet.Range("A14").CopyFromRecordset rs1

won't it insert the full file starting at A14 if that's what you want?