473,320 Members | 2,189 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Saving the database to excel file from vb

Hi all,

I am creating a database using vb6 and excel

I coded in the way to accept data from the user and made it to pass the values to an excel sheet

I manually created an excel file in my computer's D: drive and named it as "Database"

And I coded in a module named "Appending" to open the excel file to save the data which i am passing through code

If I am running the application it asks for the data from the user..when i enter all the data and saving it ya it is successfull and the data get saved in the excel file

i stopped running the application and closed all the user interaction windows. ya i am successfull again

But when i stopped running the application but without closing other user interaction form , i enetered new set of data and tried to save it

It is promptimg me to save the changes as usual
If i gave yes "Save As" dialog box gets opened asking me to save the file as (copy of "filename")

I am not sure where i am going wrong

Following is my code

************************************************** **
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 to save the file without any mess
Thank u
Apr 22 '12 #1
1 1876
Luuk
1,047 Expert 1GB
Probably your Excel-sheet is read-only?
May 5 '12 #2

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

Similar topics

1
by: Helmut Blass | last post by:
hi Access-freaks, in MS Access, I generate an Excel file where the user should have the possibility to determine the excel file name by file dialog. so I use the following command: ...
0
by: dd | last post by:
I am kind of puzzled with this problem. I am trying to open an xls file from C# and then save it as text or comma delimited. Here is the code: Excel.ApplicationClass oExcelApp = new...
0
by: Rich Wallace | last post by:
Hello all, Looking for suggestions and tips if possible. I have an application running on a file server that utilizes the FileSystemWatcher to trap when any Excel files are saved by a user. I...
2
by: sympatico | last post by:
Heres the situation: I currently have an excel file with approximately 3500 project entries with fields such as job number, management, address, project description, type, etc., Is there a way...
1
by: herman404 | last post by:
Hi everyone, I have a script that saves an excel worksheet as a CSV file. However, I need to save it as a tab delimited text file, since csv won't work for our purposes, because of commas in the...
7
by: alwayssmiling | last post by:
Hi frends, In my application, a class supports backend processing. In this class im creating a new excel sheet and im inserting some data into this excel sheet, and im trying to save the file...
1
by: NBKMNDN | last post by:
Hi frends, In my C# application im creating a new excel sheet and im inserting some data into this excel sheet, and im trying to save the file with the help of method SAVE AS. But it doesnot...
9
by: John Bailo | last post by:
I want to generate an Excel file (.xls format) from some database data. I don't want to use Excel.exe because of all the automation and security issues. Does Microsoft document the .xls file...
0
by: Speilman_54 | last post by:
Hi, I'm converting an excel Macro into visual basic 2005 express, as I don't have a copy of VB 6 and trying to make and executable from it, I know this version doesn't have the save file as .exe,...
2
by: radraq | last post by:
Hello, I have just completed a survey in excel which I've uploaded to my website. The propblem is I would like the excel file to open directly online without asking for paswords and save the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.