[RESOLVED] subclassing excel

oh boy, this is taking me a long time to solve but i have learned alot in the process... i have a problem here, i want to put my lblFishvalue.caption into the end of file on the column "E" my problem is i dont know what function or object should i use, i have been reading those objects for more than an hour now, with no success... did i miss something out? tnx in advance.... :confused:

Code:

---

Private Sub CmdPrint_Click()
Dim iRow As Long
    Dim iCol As Long
        Dim newcell As String
            Dim lastcol As Range
            Dim currentcell As Range
        Dim xls As excel.Application
    Dim XLSbook As excel.Workbook
Dim xlssheet As excel.Worksheet

 

Set xls = New excel.Application
Set XLSbook = xls.Workbooks.Open(App.Path & "\book1.xls")

DoEvents
xls.Visible = True

For iRow = 0 To MSFlexGrid1.Rows - 1
    For iCol = 0 To MSFlexGrid1.Cols - 1
    MSFlexGrid1.Col = iCol
    MSFlexGrid1.Row = iRow
    newcell = Chr(iCol + 65) & iRow + 1
    xls.Worksheets("sheet1").Range(newcell).Value = MSFlexGrid1.Text
    Next
Next
'AutoFormat
xls.Selection.AutoFormat Format:=xlRangeAutoFormatList1

'Center all in A
With xls
    .Columns("A:A").Select
    Selection.HorizontalAlignment = xlCenter
    End With

'center all in B
With xls
    .Columns("B:B").Select
    .Selection.HorizontalAlignment = xlCenter
End With

'Center all in E
xls.Columns("E:E").Select
        With xls.Selection
        .HorizontalAlignment = xlCenter
        .Selection.Style = "currency" 'Set the currency
End With

'last column of E put the lblfishvalue
    Set currentcell = xls.Column("e2").Select
        lastcol = xlssheet.Range("E2").End(xlDown).Column
    xls.ActiveCell.Value = lblFishValue.Caption

'Save workBook as ReadOnly File with password
XLSbook.SaveAs ("C:\Documents and Settings\coa\My File.xls"), , , ("123"), (True), , xlShared, (xlUserResolution)

'close
Set XLSbook = Nothing
Set xls = Nothing

End Sub

---

my problem is here in this set of codes:

Code:

---

'last column of E put the lblfishvalue
    Set currentcell = xls.Column("e2").Select
        lastcol = xlssheet.Range("E2").End(xlDown).Column
    xls.ActiveCell.Value = lblFishValue.Caption

---

Quote:

---

lastcol = xlssheet.Range("E2").End(xlDown).Column

---

will always return 5 as that is the column for range E
you really want the row
lastcol = xlssheet.Range("E2").End(xlDown).row, which will return the lastrow in column e,
you could use select
xlssheet.Range("E2").End(xlDown).select
but it is better to work directly with range, try
Range("E2").End(xlDown).Offset(1) = lblfishvalue

note you are using xlssheet as a sheet object, but i don't see anywhere you set it
you are working with xls as an instance of excel, but not specifying which book and sheet, which may work, but is very prone to failure

Quote:

---

With xls
.Columns("A:A").Select
Selection.HorizontalAlignment = xlCenter
End With

---

should be
xlsbook.sheets("sheet1").Columns("A:A").HorizontalAlignment = xlCenter
or if you set a sheet object, you can just use it
xlssheet.Columns("A:A").HorizontalAlignment = xlCenter
if you are doing this in some application outside of excel, excel constants (like xlcenter) ,may not be valid and need to be declared, or else use literals

tnx for the help men, can i ask one more thing again... what the subno. 1 in
Range("E2").End(xlDown).Offset(1) = lblfishvalue

and what is an offset... every word for me in learning this language is very important for future reference.... tnx again... ^_^

it is not working still, i will keep in mind those notes that you have given me...

offset is from the cell position
activecell.offset(1) , or (1,0) is 1 row down same column
activecell.offset(1 ,-2) ,would be 2 columns left next row down
invalid offsets will generate and error

so the Range("E2").End(xlDown).Offset(1) = lblfishvalue
means put the label in the cell below the last cell with data in column E
xldown may not work as expected
try
Range("E2").SpecialCells(xlCellTypeLastCell).offset(1) = lblfishvalue
the value for xlCellTypeLastCell = 11, see my previous remark about excel constants
too declare as a constant
const xlCellTypeLastCell as long = 11
as you are using early binding it may not be neccessary, but always is when using late binding

so basically offset is like a range method where offset is focusing on an active cell, while range is determining the range of the cells, now i do understand it more clearly.... tnx mate.... i could have given you another rating but it says that i should spread around the rating first.... :(

if this issue is ok now, please mark thread resolved