Excel file created in VB won't open in Excel

I have created a file in VB that writes numbers and formulas etc into an excel file. If I do not set the visible property of the file to false, I can see all the data successfully going into Excel. I can retrieve the results of a formula I have written into a cell. When I save the file from VB, it is totally unuseable when I open it in Excel. Can you shed some light on what I need to do to make the file available from Excel? Thanks.

I haven't know the whole meaning!Would u write or save the excel file when u are reading it at the same time??

well, what do you mean the file is "completely unusable"? Can you open the file at all? If you can...is there anything in it? Can you post some code so we can see what you're doing?

Sorry for the lack of detail. I've written some code to create an Excel file from VB. When I issue the Application.Save method, my Excel file saves, yet there is nothing in it when I try opening it inside Excel. Here is my code. The objects that I used are self explanatory.

Option Explicit
Dim objExcel As Object


Private Sub cmdCalculate_Click()
Set objExcel = CreateObject("excel.sheet")
objExcel.Application.Visible = True

Rem Fill in the row labels.
objExcel.Application.Cells(1, 1).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()
objExcel.Application.Save App.Path & "\Expenses.xls"
End Sub

Private Sub cmdQuit_Click()
objExcel.Application.Quit
Set objExcel = Nothing
End
End Sub

I hope some of you can detect where my code is lacking. Also, is there a resource I can use to find the various properties and methods I need to be able to use in order to edit my Excel file from VB?

Thanks in advance.
me

U don't close the objExcel.Application!!..

a question - are you writing this in ASP, VBScript, or VB? if so, try using the set objExcel = new excel.application syntax instead of ("CreateObject").

This code is running in VB.
Your suggestion makes sense.
The set objExcel = new excel.application is accepted, yet the program generates an error on the first objExcel.Application.Cells statement following it. The error is:
Application defined or object defined error.

I just can't seem to find much documentation to refer to regarding connecting to Excel and creating a new file (that I am able to open in Excel later.

You need to reference the Microsoft Excel Object Library. (Choose
"references" in VB or Access.) Then dimension a workbook like this:

dim xlb as Excel.workbook

Then:


set xlb = Workbooks.Open(filname)


Give it a try.

Try this :--

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") '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, 1).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


This method works no problem. The reason why yours will not open is that you are saving a session of excel as "expenses" as oposed to the workbook or sheet.

Hope this helps

Hi there,

I've tried the codes examples above, but when i try to execute the examples I've get an error message.

Microsoft Development Environment

An unhandled exception of type
'System.RunTime.InteropServices.COMException' occured in example.exe

Additional information: Old format or invalid type Library

I am using Windows 2000, Visual Basic .NET og Office XP