method range of object globally failed error

[Hema Suresh]

Hi all

I created a database via VB and saved it in excel sheet

I have 10 command buttons and 10 text box controls on the vb form and i coded in the way to get the data from the user

once the user entered the data, i coded in vb to pass all the vaues to an excel sheet and to save it

when i executed the vb application, as i designed, it asks for the data from the user

when i enter the data and save it,ya it successfull

One thing i have to say here is,
if i want to enter next record, i have to stop running the application and i have to close all the user interaction windows..if so,i can add many records and i dont have any problem with that

i added more than 10 records and i found everything was going good and also i can see all the records which i saved in a singl excel sheet as i designed and coded in vb

but when i tried adding the next record by stop running the application but without closing the user interaction windows, i am getting error as "method range of object global failed"

and the following is the row where the error is shown
*************** *************** *************** *******
NextToBlankRow = Sheets("Sheet1" ).Range(BlankRo wCell).Offset(1 , 0).Row
*************** *************** *************** ********
After getting this error,

Sometimes the excel file is opened with the propmpting msg "Do you want to save the changes"

I i give yes,it is opening with the save as dialog box with the file name "Copy of 'filename' ".

I need my data to be saved in the same file name not with othe name

Some other times, when i try to open the excel file rom the local drives, it is not at all opening

the following is the code where i made the excel to get the user's data from vb and to save it

*************** *************** *************** ********

Sub Appending(TBook sNames 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.Applicati on
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.Applicati on

Set oXLBook = oXLApp.Workbook s.Open("D:\Hema \DB.xls")
Set oXLSheet = oXLBook.Workshe ets(1)
Set oXLRange = oXLSheet.UsedRa nge


oXLRange.Specia lCells(xlCellTy peLastCell).Act ivate
'BlankRow = ActiveSheet.Ran ge("a65536").En d(xlUp).Offset( 1, 0).Row
BlankRowNum = oXLApp.ActiveCe ll.Row + 1
BlankRowCell = "A" & BlankRowNum
NextToBlankRow = Sheets("Sheet1" ).Range(BlankRo wCell).Offset(1 , 0).Row
TitleRowCell = "A" & NextToBlankRow
Sheets("Sheet1" ).Range(TitleRo wCell) = "BOOKS RECORD"

oXLRange.Specia lCells(xlCellTy peLastCell).Act ivate

newFstRow = oXLApp.ActiveCe ll.Row + 1

newFstCell = "A" & newFstRow
Sheets("Sheet1" ).Range(newFstC ell) = TBksNames
newScndCell = "B" & newFstRow
Sheets("Sheet1" ).Range(newScnd Cell) = BksNames

oXLRange.Specia lCells(xlCellTy peLastCell).Act ivate

newFstRow = oXLApp.ActiveCe ll.Row + 1

newFstCell = "A" & newFstRow
Sheets("Sheet1" ).Range(newFstC ell) = TNoOfBks
newScndCell = "B" & newFstRow
Sheets("Sheet1" ).Range(newScnd Cell) = NoOfBks


oXLRange.Specia lCells(xlCellTy peLastCell).Act ivate

newFstRow = oXLApp.ActiveCe ll.Row + 1

newFstCell = "A" & newFstRow
Sheets("Sheet1" ).Range(newFstC ell) = TSenName
newScndCell = "B" & newFstRow
Sheets("Sheet1" ).Range(newScnd Cell) = SenName


oXLRange.Specia lCells(xlCellTy peLastCell).Act ivate

newFstRow = oXLApp.ActiveCe ll.Row + 1

newFstCell = "A" & newFstRow
Sheets("Sheet1" ).Range(newFstC ell) = TRecName
newScndCell = "B" & newFstRow
Sheets("Sheet1" ).Range(newScnd Cell) = RecName

oXLRange.Specia lCells(xlCellTy peLastCell).Act ivate

newFstRow = oXLApp.ActiveCe ll.Row + 1

newFstCell = "A" & newFstRow
Sheets("Sheet1" ).Range(newFstC ell) = TGen
newScndCell = "B" & newFstRow
Sheets("Sheet1" ).Range(newScnd Cell) = Gen


oXLRange.Specia lCells(xlCellTy peLastCell).Act ivate

newFstRow = oXLApp.ActiveCe ll.Row + 1

newFstCell = "A" & newFstRow
Sheets("Sheet1" ).Range(newFstC ell) = TStrNo
newScndCell = "B" & newFstRow
Sheets("Sheet1" ).Range(newScnd Cell) = StrNo


oXLRange.Specia lCells(xlCellTy peLastCell).Act ivate

newFstRow = oXLApp.ActiveCe ll.Row + 1

newFstCell = "A" & newFstRow
Sheets("Sheet1" ).Range(newFstC ell) = TCit
newScndCell = "B" & newFstRow
Sheets("Sheet1" ).Range(newScnd Cell) = Cit


oXLRange.Specia lCells(xlCellTy peLastCell).Act ivate

newFstRow = oXLApp.ActiveCe ll.Row + 1

newFstCell = "A" & newFstRow
Sheets("Sheet1" ).Range(newFstC ell) = TState
newScndCell = "B" & newFstRow
Sheets("Sheet1" ).Range(newScnd Cell) = State


oXLRange.Specia lCells(xlCellTy peLastCell).Act ivate

newFstRow = oXLApp.ActiveCe ll.Row + 1

newFstCell = "A" & newFstRow
Sheets("Sheet1" ).Range(newFstC ell) = TPstCd
newScndCell = "B" & newFstRow
Sheets("Sheet1" ).Range(newScnd Cell) = PstCd


oXLRange.Specia lCells(xlCellTy peLastCell).Act ivate

newFstRow = oXLApp.ActiveCe ll.Row + 1

newFstCell = "A" & newFstRow
Sheets("Sheet1" ).Range(newFstC ell) = TDtAndTime
newScndCell = "B" & newFstRow
Sheets("Sheet1" ).Range(newScnd Cell) = DtAndTime

oXLSheet.Column s("A:C").Column Width = 20
oXLSheet.Rows(" 1:6").RowHeigh t = 20

With oXLSheet.Column s("A")
.Font.Bold = True
.Font.Italic = True
.Font.ColorInde x = 25
.Font.Size = 13
End With

With oXLSheet.Column s("B")
.Font.ColorInde x = 50
.Font.Size = 10
End With

With oXLSheet.Column s("A:B")
.HorizontalAlig nment = xlCenter
.VerticalAlignm ent = xlCenter
End With

oXLApp.Visible = True

Set oXLSheet = Nothing

oXLBook.Close

'oXLApp.ActiveW orkbook.SaveAs ("D:\Hema\Datab ase.xls")

oXLApp.Quit

Set oXLBook = Nothing
Set oXLApp = Nothing

End Sub

*************** *************** *************** *******

i am new to vb so i am not sure where i am going wrong

please anyone help in this

all i need is,the users data to be saved even if other user interction windows are opened

please help me out

Thank you

[rekedtechie]

'... im not sure, but maybe..

Dim oXLApp as Excel.
Application
Set oXLApp = New Excel.Applicati on

'you create another instance of *.xls when you open your *.xls database.

'thats why when you close your *.xls file the another instance asking you if you want to save the copy.

[rekedtechie]

in access there is record count, wherein your *.xls database dont have this..

try to use a certain cell to hold the total rows occupied by data (RecordCount),

then if you want to add new record,

get the total row first,
then total row + 1 (for the row to movenext)
then put your record,
update the cell containing the row count,
then save.

*****
a cell that contains the row count..
(rs.RecordCount in access)

row count + 1
(rs.Movenext in access)

[Hema Suresh]

Thank you Rekedtechie

I will try it