Adding Data to an Excel Spreadsheet 1

[hawaiian1975]

Okay, I have a form that has a save button. When I click that save button, I want to populate an Excel spreadsheet with the data. The data is in text boxes. I did a search through the MSDN library and did an Internet search also, but I can't find anything that really helps.

 

[BiggerBrother]

try doing a search on this website for save data to excel and you will find a wealth of solutions.

One such thread is : thread222-598558

BB

 

[hawaiian1975]

Thanx, i'll try that..

 

[LPlates]

Here you go...

http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=50048&lngWId=1

 

[hawaiian1975]

Hey LPlates thanx for the link. I just have a question on the code:

Option Explicit
Dim objExcel As Object ' Excel application
Dim objBook As Object ' Excel workbook
Dim objSheet As Object ' Excel Worksheet



Private Sub cmdCalculate_Click()

Set objExcel = CreateObject("excel.application&quot 'Starts the Excel Session
Set objBook = objExcel.Workbooks.Add 'Add a Workbook
Set objSheet = objBook.Worksheets.Item(1) 'Select a Sheet
objExcel.application.Visible = True

Rem Fill in the row labels.
objExcel.application.Cells(1, 2).Value = "Tuition and Fees"
objExcel.application.Cells(2, 1).Value = "Books and Supplies"
objExcel.application.Cells(3, 1).Value = "Board"
objExcel.application.Cells(4, 1).Value = "Transportation"
objExcel.application.Cells(5, 1).Value = "Other Expenses"
objExcel.application.Cells(7, 1).Value = "Total"

Rem Fill in the values
objExcel.application.Cells(1, 3).Value = Text1.Text
objExcel.application.Cells(2, 3).Value = Text2.Text
objExcel.application.Cells(3, 3).Value = Text3.Text
objExcel.application.Cells(4, 3).Value = Text4.Text
objExcel.application.Cells(5, 3).Value = Text5.Text
objExcel.application.Cells(7, 3).Formula = "=sum(c1:c5)"
objExcel.application.Cells(7, 3).Font.Bold = True

Text6.Text = objExcel.application.Cells(7, 3).Value

'objExcel.Application.Visible = False

End Sub

Private Sub cmdSave_Click()
objBook.SaveAs "c:\expenses.xls"
objBook.Close
objExcel.Quit
Set objSheet = Nothing
Set objBook = Nothing
Set objExcel = Nothing

End Sub

Private Sub cmdQuit_Click()
Set objExcel = Nothing
End
End Sub

How do I add to the spreadsheet, without overwriting the data thats already on it?

 

[LPlates]

Ok, try this one..


Dim Excel As Object

Private Sub Command1_Click()
Set Excel = CreateObject("Excel.Application&quot
Excel.Visible = True
Excel.Workbooks.Open App.Path + "\expenses.xls"
End Sub

Private Sub Command2_Click()
Excel.Application.Cells(1, 1).Value = "Ahh, it changed!"
End Sub

 

[LPlates]

Open, modify, save.

Option Explicit

Dim Excel As Object
Dim ExcelSheet As Object

Private Sub Command1_Click()
Dim Name As String
Set Excel = CreateObject("Excel.Application&quot
Excel.Visible = True
Excel.Workbooks.Open App.Path + "\expenses.xls"
Name = Excel.ActiveWorkbook.Sheets(1).Name 'Read the name of the sheet
Set ExcelSheet = Excel.ActiveWorkbook.Sheets(Name)
End Sub

Private Sub Command2_Click()
Excel.Application.Cells(1, 1).Value = "Ahh, it changed!"
End Sub

Private Sub Command3_Click()
ExcelSheet.SaveAs (App.Path & "\expenses2.xls&quot
End Sub

Private Sub Form_Unload(Cancel As Integer)
Set Excel = Nothing
Set ExcelSheet = Nothing
End Sub