Last row with data in Excel!

[voskouee]

I need to locate using VB the last row that contains data go down 3 rows and start adding a recordset..

i have the recordeset but not the location?

any ideas how can i locate it?

any ideas where can i find help about the Excel Object?

thanks guys

 

[shades]

Howdy. Do you mean VBA? If so, then use something like this, assuming that you data starts in column A:

Sub test()
    Dim LastRow As Long
    Dim NewCell As Range
    LastRow = Cells(65536, 1).End(xlUp).Row
    Set NewCell = Range("A" & LastRow + 3)
End Sub

________
Honda Cb500 Twin History

 

[shades]

BTW, this code can be simplified. But not knowing what else you want to do, this allows some flexibility.
________
Roor bongs

 

[voskouee]

it works but i save it and open up again my template to do the same thing in the next record of the recordset it stucks.

basically i am creating new workbook for every record in my recordset. i think i need to set the group of that... in order to be ok any idea?

xlapp.activesheet.????

thanks man

 

[voskouee]

this is my code..

Sub Debit_Notes()

Dim xlApp As Excel.Application
Dim xlwkbk As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim objRST As Recordset
Dim rec As Recordset
Dim RsCntry As Recordset
Dim lvlColumn As Integer
Dim i As Integer
Dim f As Variant
Dim startrange As Excel.Range
Dim lngColumn As Long
Dim r As Long
Dim CrRow As Long
Dim lngRows As Long
Dim lngCols As Long
Dim icols As Integer
Dim c As Object
Dim firstAddress As Variant
Dim w As Object
Dim rsweek As Recordset
Dim LastRow As Long
Dim NewCell As Range


'Create Sheets of Working Tables
Set rsweek = CurrentDb.OpenRecordset("Select week from week;")
Set rec = Application.CurrentDb.OpenRecordset("SELECT * FROM TDDebitNotes;")
Set RsCntry = CurrentDb.OpenRecordset("SELECT * FROM TD_DB3;")
While Not (rec.EOF)

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.DisplayAlerts = False
Set xlwkbk = xlApp.Workbooks.Open("C:\Input Files\DNote.xlt")


Set objRST = CurrentDb.OpenRecordset("SELECT TYPE,Org,FACCT,CSUB,P_S,SOBP,FPROJ,Cust,Cntry,iet,orgo,schan,Loc,bsac FROM " & rec!SOBPDEBIT & " ;")
xlApp.ActiveSheet.Range("A30").CopyFromRecordset objRST
xlApp.ActiveSheet.Range("C3").Value = RsCntry!Description
xlApp.ActiveSheet.Range("K8").Value = RsCntry!SOBP
xlApp.ActiveSheet.Range("K9").Value = RsCntry!Description
xlApp.ActiveSheet.Range("M4").Value = Format(Date, "DD/MM/YYYY")
xlApp.ActiveSheet.Range("B8").Value = rec!Description
xlApp.ActiveSheet.Range("B9").Value = rec!SOBP & " - " & rec!Prefix
xlApp.ActiveSheet.Range("C18").Value = "PSA CHARGES" & " - " & rsweek!Week
xlApp.ActiveSheet.Range("C25").Value = Format(Date, "MM/YYYY")

xlApp.ActiveSheet.Cells.LastRow
LastRow = Cells(65536, 1).End(xlUp).Row
Set NewCell = Range("A" & LastRow + 2)
NewCell.Value = 444444444 (here am just checking where is the located cell)
LastRow = 0


'Add second part of Debit Note
xlApp.Sheets("Sheet2").Select
xlApp.ActiveSheet.Range("A1:N27").Select
xlApp.Selection.Copy
xlApp.Sheets("Sheet1").Select
xlApp.ActiveSheet.Range("A45").Select
xlApp.ActiveSheet.Paste
xlApp.Sheets("Sheet2").Select
xlApp.ActiveWindow.SelectedSheets.Delete

xlApp.ActiveSheet.Name = "DN" & " - " & rec!SOBP
xlwkbk.Save
xlApp.DisplayAlerts = True
xlApp.ActiveWorkbook.SaveAs FileName:="C:\Output FIles\TD\TD Debit Notes\D.N. " & Format(Date, "DDMMYYYY") & " FROM " & rec!Prefix & " TO " & RsCntry!Prefix & " .xls"
xlApp.quit

rec.MoveNext
Wend

Set xlApp = Nothing
Set xlwkbk = Nothing
Set xlsheet = Nothing

End Sub

 

[shades]

it works but i save it and open up again my template to do the same thing in the next record of the recordset it stucks.

basically i am creating new workbook for every record in my recordset. i think i need to set the group of that... in order to be ok any idea?

xlapp.activesheet.????

thanks man

I was confused. I thought that you wanted to continue adding to the same worksheet (and hence the reason for finding the last row).

It seems that you are setting LastRow = 0 too soon in the code. Thus, it is finding the last row, but the code is not using it. Or have I missed something?
________
VOLCANO DIGITAL

 

[voskouee]

it is one worksheet and then am saving it closing it and i open a new one.. i want the same thing done there.. can you check at my code please?

thanks for the help!

 

[voskouee]

when should i set it to zero? how?
i close the first workbook, set to zero, open new excel?

how?