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(BlankRowCell).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(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\DB.xls")
Set oXLSheet = oXLBook.Worksheets(1)
Set oXLRange = oXLSheet.UsedRange


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

oXLRange.SpecialCells(xlCellTypeLastCell).Activate

newFstRow = oXLApp.ActiveCell.Row + 1

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

oXLRange.SpecialCells(xlCellTypeLastCell).Activate

newFstRow = oXLApp.ActiveCell.Row + 1

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


oXLRange.SpecialCells(xlCellTypeLastCell).Activate

newFstRow = oXLApp.ActiveCell.Row + 1

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


oXLRange.SpecialCells(xlCellTypeLastCell).Activate

newFstRow = oXLApp.ActiveCell.Row + 1

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

oXLRange.SpecialCells(xlCellTypeLastCell).Activate

newFstRow = oXLApp.ActiveCell.Row + 1

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


oXLRange.SpecialCells(xlCellTypeLastCell).Activate

newFstRow = oXLApp.ActiveCell.Row + 1

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


oXLRange.SpecialCells(xlCellTypeLastCell).Activate

newFstRow = oXLApp.ActiveCell.Row + 1

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


oXLRange.SpecialCells(xlCellTypeLastCell).Activate

newFstRow = oXLApp.ActiveCell.Row + 1

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


oXLRange.SpecialCells(xlCellTypeLastCell).Activate

newFstRow = oXLApp.ActiveCell.Row + 1

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


oXLRange.SpecialCells(xlCellTypeLastCell).Activate

newFstRow = oXLApp.ActiveCell.Row + 1

newFstCell = "A" & newFstRow
Sheets("Sheet1").Range(newFstCell) = TDtAndTime
newScndCell = "B" & newFstRow
Sheets("Sheet1").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

Set oXLSheet = Nothing

oXLBook.Close

'oXLApp.ActiveWorkbook.SaveAs ("D:\Hema\Database.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.Application

'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