Text box info from vb to Excel

[viperfire]

Ok this is my problem, I have a program that calculates costs and displays the costs in 3 text boxes. I need to be able for VB to open a new excel sheet and put the info from the text boxes in to the cells.

This is the code I have:

Dim x1app As Excel.Application
Dim x1book As Excel.Workbook
Dim x1sheet As Excel.Worksheet

Set x1app = New Excel.Application
Set x1book = x1app.Workbooks.Add
Set x1sheet = x1book.Worksheets.Add

x1sheet.Cells(1, 1).Value = Text1.Text
x1sheet.Cells(2, 1).Value = Text2.Text

x1sheet.Cells(3, 1).Formula = "r1c1 +r2c1"
Text3.Text = x1sheet.Cells(3, 1)


But when I run it I get an error saying: Invalid use of new key word and the line
Set x1app = New Excel.Application is highlighted.

Please can some one help

[packman]

Try this:
Set x1app = CreateObject("Excel.application")

in place of this:
Set x1app = New Excel.Application

[viperfire]

it worked!!!!!

Thanks mate I really appreciate it.

[tomjess]

I have used the code in this thread and it seems to work, the only problem I have is that I can't find the excel sheet, called Book1, it has opened. If I try again it opens another excel sheet, Book2, which also can't find.
It is only when I shut down my computer, it asks me do I want to save the changes in Book1 and Book2.
Any ideas to where they are and how I can find them.

[da_silvy]

Couldn't test it sorry, (Still yet to install Office after a clean format), but this should solve your problems:

Dim x1app As Excel.Application 
Dim x1book As Excel.Workbook 
Dim x1sheet As Excel.Worksheet 
 
Set x1app = CreateObject("Excel.application") 
Set x1book = x1app.Workbooks.Add 
Set x1sheet = x1book.Worksheets.Add 
 
x1app.Visible = True
 
x1sheet.Cells(1, 1).Value = Text1.Text 
x1sheet.Cells(2, 1).Value = Text2.Text 
 
x1sheet.Cells(3, 1).Formula = "r1c1 +r2c1" 
Text3.Text = x1sheet.Cells(3, 1)

[tomjess]

da_silvy

Thank you

steve

[tomjess]

Thanks for the help above.

How do I go about calling a particular .xls file, ie say I have a Test.xls and I want to bring that up.

What coding would need to be put in the above coding for that to work

Thanks guys

[Patoooey]

Dim objExcel As Excel.Application
    
    On Error Resume Next
    Set objExcel = GetObject(",Excel.Application")
    If Err.Number <> 0 Then
        Set objExcel = CreateObject("Excel.Application")
    End If
    Err.Clear
    On Error GoTo 0
    
    objExcel.Visible = True
    objExcel.WindowState = xlNormal
    objExcel.Workbooks.Open FileName:="C:\Book5.xls"

[tomjess]

Hi Guys and Gals

I know that I can use .text for a text box to take through to Excel. What can I use to take the text thats in a Combobox through to Excel?

Below is what I am using

objExcel.Cells(23, 9).Value = txtLast10.Text

objExcel.Cells(5, 10).Value = cboDept1.????

Thanks once again

[Patoooey]

objExcel.Cells(5, 10).Value = CboDept1.List(0) will give you the 1st item in the combobox.(the list in combos start at zero)

If you want the 10th item, objExcel.Cells(5, 10).Value = CboDept1.List(9)

[tomjess]

Thanks for that coding

But what if the user has decided to use say one the items in the combox. Say he also has 10 items in the combox. I will not know which one he has selected, so I want to have the one he has selected to go through to Excel. Does that make sense? I hope so.

Thanks

[da_silvy]

objExcel.Cells(5, 10).Value = CboDept1.List(CboDept1.ListIndex)

.ListIndex gets the index of the currently selected file

Cheers