By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,060 Members | 1,906 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,060 IT Pros & Developers. It's quick & easy.

Getting error "Application defined or object defined error"

P: 21
Hi all
I am trying to create a database using vb6 and excel
I created few controls on the vb form and coded to get the details from the user
I code in the way to pass the data which i got from the user to an excel sheet
I named a function in a module as "Appending" and i made it to receive all the data through arguments
And almost I coded correctly to make the database look in the format i need

Eveeything is OK,but when I am running the program I am getting "Application defined or object defined error"..

This is the line where i am getting the error
******************************
NextToBlankRow = ActiveSheet.Range(BlankRowCell).End(xlDown).Offset (1, 0).Row
**********************************

As I am in the learning phase of VB6, I am not sure where I am going wrong

Please look at my code below and help me out in this

This is the module where i made the procedure to accept all the values from the vb form and to save it in an excel file in the format i need

MY CODE SNIPPET
************************************************** *****

Sub Appending(TBooksNames As String, BooksNames As Variant, TNoOfBooks As String, NoOfBooks As Integer, TSenderName As String, SenderName As Variant, TRecipientName As String, RecipientName As Variant, TGender As String, Gender As String, TStreetNo As String, StreetNo As Variant, TCity As String, City As String, TState As String, State As String, TPostCode As String, PostCode As Long, TDateAndTime As String, DateAndTime As Variant)

Dim oXLApp As Excel.Application
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet
Dim oXLRange As Excel.Range

Dim newFstRow As Integer
Dim newFstCell As Variant
Dim newScndCell As Variant

Dim BlankRow As Integer
Dim BlankRowNum As Integer
Dim BlankRowCell As Variant

Dim NextToBlankRow As Integer
Dim TitleRowCell As Variant


TBksNames = TBooksNames
BksNames = BooksNames
TNoOfBks = TNoOfBooks
NoOfBks = NoOfBooks
TSenName = TSenderName
SenName = SenderName
TRecName = TRecipientName
RecName = RecipientName
TGen = TGender
Gen = Gender
TStrNo = TStreetNo
StrNo = StreetNo
TCit = TCity
Cit = City
TSta = TState
Sta = State
TPstCd = TPostCode
PstCd = PostCode
TDtAndTime = TDateAndTime
DtAndTime = DateAndTime


Set oXLApp = New Excel.Application

Set oXLBook = oXLApp.Workbooks.Open("D:\Hema\Database.xls")
Set oXLSheet = oXLBook.Worksheets(1)
Set oXLRange = oXLSheet.UsedRange


'oXLRange.SpecialCells(xlCellTypeLastCell).Activat e
BlankRow = ActiveSheet.Range("a65536").End(xlUp).Offset(1, 0).Row
'BlankRowNum = oXLApp.ActiveCell.Row + 1
BlankRowCell = "A" & BlankRowNum
NextToBlankRow = ActiveSheet.Range(BlankRowCell).End(xlDown).Offset (1, 0).Row
TitleRowCell = "A" & NextToBlankRow
ActiveSheet.Range(TitleRowCell) = "BOOKS RECORD"

oXLRange.SpecialCells(xlCellTypeLastCell).Activate

newFstRow = oXLApp.ActiveCell.Row + 1

newFstCell = "A" & newFstRow
ActiveSheet.Range(newFstCell) = TBksNames
newScndCell = "B" & newFstRow
ActiveSheet.Range(newScndCell) = BksNames

oXLRange.SpecialCells(xlCellTypeLastCell).Activate

newFstRow = oXLApp.ActiveCell.Row + 1

newFstCell = "A" & newFstRow
ActiveSheet.Range(newFstCell) = TNoOfBks
newScndCell = "B" & newFstRow
ActiveSheet.Range(newScndCell) = NoOfBks


oXLRange.SpecialCells(xlCellTypeLastCell).Activate

newFstRow = oXLApp.ActiveCell.Row + 1

newFstCell = "A" & newFstRow
ActiveSheet.Range(newFstCell) = TSenName
newScndCell = "B" & newFstRow
ActiveSheet.Range(newScndCell) = SenName


oXLRange.SpecialCells(xlCellTypeLastCell).Activate

newFstRow = oXLApp.ActiveCell.Row + 1

newFstCell = "A" & newFstRow
ActiveSheet.Range(newFstCell) = TRecName
newScndCell = "B" & newFstRow
ActiveSheet.Range(newScndCell) = RecName

oXLRange.SpecialCells(xlCellTypeLastCell).Activate

newFstRow = oXLApp.ActiveCell.Row + 1

newFstCell = "A" & newFstRow
ActiveSheet.Range(newFstCell) = TGen
newScndCell = "B" & newFstRow
ActiveSheet.Range(newScndCell) = Gen


oXLRange.SpecialCells(xlCellTypeLastCell).Activate

newFstRow = oXLApp.ActiveCell.Row + 1

newFstCell = "A" & newFstRow
ActiveSheet.Range(newFstCell) = TStrNo
newScndCell = "B" & newFstRow
ActiveSheet.Range(newScndCell) = StrNo


oXLRange.SpecialCells(xlCellTypeLastCell).Activate

newFstRow = oXLApp.ActiveCell.Row + 1

newFstCell = "A" & newFstRow
ActiveSheet.Range(newFstCell) = TCit
newScndCell = "B" & newFstRow
ActiveSheet.Range(newScndCell) = Cit


oXLRange.SpecialCells(xlCellTypeLastCell).Activate

newFstRow = oXLApp.ActiveCell.Row + 1

newFstCell = "A" & newFstRow
ActiveSheet.Range(newFstCell) = TState
newScndCell = "B" & newFstRow
ActiveSheet.Range(newScndCell) = State


oXLRange.SpecialCells(xlCellTypeLastCell).Activate

newFstRow = oXLApp.ActiveCell.Row + 1

newFstCell = "A" & newFstRow
ActiveSheet.Range(newFstCell) = TPstCd
newScndCell = "B" & newFstRow
ActiveSheet.Range(newScndCell) = PstCd


oXLRange.SpecialCells(xlCellTypeLastCell).Activate

newFstRow = oXLApp.ActiveCell.Row + 1

newFstCell = "A" & newFstRow
ActiveSheet.Range(newFstCell) = TDtAndTime
newScndCell = "B" & newFstRow
ActiveSheet.Range(newScndCell) = DtAndTime

oXLSheet.Columns("A:C").ColumnWidth = 20
oXLSheet.Rows("1:6").RowHeight = 20

With oXLSheet.Columns("A")
.Font.Bold = True
.Font.Italic = True
.Font.ColorIndex = 25
.Font.Size = 13
End With

With oXLSheet.Columns("B")
.Font.ColorIndex = 50
.Font.Size = 10
End With

With oXLSheet.Columns("A:B")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With

oXLApp.Visible = True

oXLBook.Close

oXLApp.Quit

Set oXLSheet = Nothing
Set oXLBook = Nothing
Set oXLApp = Nothing

End Sub

Please someone help me
Apr 22 '12 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.