Asapkota

Hello all

I need to write a Vb program that takes data from a user form and updates an existing spreadsheet's certain cells with the new data when a command button is pressed. After that it needs to print the spreadsheet. How do I do this. I already made the interface. I need to know how to open an excel spreadsheet and update cells in the spreadsheet from VB and finally print the spreadsheet. Any help is greatly appreciated.

Thinker

Asapkota

I used the following code from the post you gave me

----
Option Explicit

Private Sub Command1_Click()

Dim Index As Integer
Index = 1
Dim obExcelApp As Excel.Application
Dim obWorkBook As Excel.Workbook
Set obExcelApp = CreateObject("Excel.Application")
Set obWorkBook = obExcelApp.Workbooks.Open("c:\tomtest.xls")
obWorkBook.Worksheets(Index).Range("a1") = "thank you"
obWorkBook.Worksheets(Index).Range("b4") = "Very much"
obExcelApp.Visible = True
Set obWorkBook = Nothing
Set obExcelApp = Nothing

End Sub
----

I receive the following error when I run the program

Compile Error:

"User-defined type not defined"

It points to the following code

"Dim obExcelApp As Excel.Application"

What did I forget to do?

Asapkota

I finally got the above code to work. I changed
Dim obExcelApp As Excel.Application
Dim obWorkBook As Excel.Workbook
to
Dim obExcelApp As Object
Dim obWorkBook As Object
and it worked fine.

Now, I have one question

Set obWorkBook = obExcelApp.Workbooks.Open("c:\tomtest.xls")

Why do I have to enter the full path of the excel spreadsheet I want to open. How do I make it open the spreadsheet with just the spreadsheet name. Look for it on the directory where the program is being run from.

Thinker

You should get rid of the Object and put it back the way it was, then go to
the References dialog from the Projects menu, find the Excel Object Library
check it and OK. This will give you early binding, which is much more
efficient than the late binding you have changed to.

The path can be relative to your app's directory. Just to be on the safe
side, you might want to use App.Path & "\" & "mysheet.xls"

Asapkota

Thanks a lot for the help. Everything works fine. How do I print the excel spreadsheet after updating it with new data

Timbo

obWorkBook.ActiveSheet.PrintOut