vb.net - open existing excel file using windows file dialog box and assign value to a cell

Hi:

I am looking to add a button to a VB.Net application that will allow the user to browse a standard Windows file directory dialog box, open an existing excel spreadsheet, make the spreadsheet active and then assign a value to a cell.  I've been able to do all of the above individually, but can't seem to get the whole sequence of events to occur.

Thanks,  Dave
dmborlandAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tobin46Commented:
I'll show you two ways, first to create new workbook:

I'm assuming you've adding the com reference.

Private Sub btnExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcel.Click
     RUN_EXCEL_FUNCTIONS()
End Sub

Private Sub RUN_EXCEL_FUCNTIONS()
     Dim xlApp as Excel.Application  
     Dim xlBook as Excel.Workbook
     Dim xlsheet as Excel.worksheet

     xlApp = Ctype(CreateObject("Excel.Application"), Excel.Application)
     xlBook = Ctype(xlApp.Workbooks.Add, Excel.workbook)
     xlsheet = Ctpye(xlbook.worksheet(1), excel.worksheet)

     xlsheet.application.visible = True
     
     'Insert the Data from the form here
     xlsheet.cells(1,1) = "First Cell"
     and so on.....
     
     'Save the sheet
     xlsheet.saveas("C:\.........")

     'Excel will be running and left open....

__________________________________________________________________________________________________
** Now to browse for a file, simple add a 'OpenFileDialog' to our form, name it 'browsefile' or whatever...have a textbox, and a button to call the browsefile object.
Private Sub btnBrowse(ByVal sender as System.object.........) handles btnbrowse.click
        browsefile.showdialog()
End Sub

'At this point you will be prompted with a Open File dialog box to search for the file.  
'Then go to the FileOK method of the browsefile dialog...you can do two things here, the openfile method or the following...I do things differently

Private Sub browsefile_FileOk(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles browsefile.FileOk
        Dim filename as string
        filename = browsefile.filename
        Systems.Diagnostics.Process.Start(filename)
End Sub
'The file is now open
'You can make your changes or insert data as needed.  Create function the sets the xls.activesheet or whatever you want, tons of option

xlapp.workbooks(1).activate <-activates the first sheet

Your question wasn't very clear as to what you wanted to do once the excel file was open...but you can modify cell or insert values into cells, using  Excel Application Object functions used above.  

Check this site out:  http://msdn.microsoft.com/office/understanding/excel/default.aspx

dmborlandAuthor Commented:
Hi:  Thanks for your help, but I'm still stuck.

I understand everything up until the solid line.  Starting with the ** and down 5 lines must leave out some steps that I don't automatically understand.  

I've added a new form called browsefile.

I've placed a button and text box on that form.

I've added the following code to the button click event:
     browsefile.showdialog() - the resuilt is a squiggly under the browsefile indicating something must have to be defined somewhere.

Can you offer further guidance?


tobin46Commented:
HI:

If you added an OpenFileDialog to the project, go to the properties dialog of the OpenFileDialog object and rename it 'browsefile'.  

This is the following code that I used.  I used an OpenFileDialog object that I inserted into the project and named 'openexcel'.

Private Sub btnExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcel.Click
        openexcel.ShowDialog()
    End Sub

    Private Sub openexcel_FileOk(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles openexcel.FileOk
        Dim filename As String  <----filename string
        filename = openexcel.FileName <---- filename will be the selected filename within the OpenFileDialog 'openexcel'
        System.Diagnostics.Process.Start(filename)  <----Once the file is selected, I use the system.diagnostics.process.start to open the file.  

    End Sub

Then the file you selected will be opened.    

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tobin46Commented:
Well, I was the only response, and it was a valid response at that.  

Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.