none
Excel and Word in Visual Basic 2008

    Question

  • Hello, can anyone help me.  I can't even get started.  I can't find code that actually works anywhere for excel or word.  All I want from Excel is to be able to take data from cells in an already made sheet and have each cell be a variable in an array.  From word, I can create a word document but it won't let me add text, because it says that it is being used by another process.  Can anybody help, this is very frustrating?  Thanks.
    Wednesday, February 24, 2010 9:07 PM

Answers

  • Hello Rubik,

    Sorry for delay. From what you said, it seems that you are not working with VSTO project. This forum is mainly targeted at VSTO technology. You could refer to this forum's PLEASE READ FIRST to know more about this technology. If I understand you correctly, you are working with Excel Automation and Word Automation. The best place for this issue would be Excel newsgroup and Word newsgroup. You will find appropriate entry links in the about link.

    For Excel, you could refer to the following code to get the range where you want to read data. After getting this range, use foreach loop to access each cell.

                Dim objExcel As New Excel.Application()
                Dim missing As Object = Type.Missing
                Dim wk As Excel.Workbook = objExcel.Workbooks.Open("C:\Temp\1.xlsx", missing, missing, missing, missing, missing,  missing, missing, missing, missing, missing, missing, missing, missing, missing)
                objExcel.Visible = True
                Dim ws As Excel.Worksheet = TryCast(wk.Worksheets(1), Excel.Worksheet)
                Dim range As Excel.Range = ws.Application.get_Range("A1", "D2")

                For Each cell As Excel.Range In range.Cells
                    'access each cell of this range
                    MessageBox.Show(cell.Value2.ToString())
                Next

                Marshal.FinalReleaseComObject(range)
                Marshal.FinalReleaseComObject(ws)
                wk.Close(missing, missing, missing)
                Marshal.FinalReleaseComObject(wk)
                objExcel.Quit()
                Marshal.FinalReleaseComObject(objExcel) 
                GC.Collect()
                GC.WaitForPendingFinalizers()
                GC.Collect()
                GC.WaitForPendingFinalizers()


    For the warning of Word, please ensure that you have release com objects correctly. To release com objects, please refer to this thread: http://social.msdn.microsoft.com/forums/en-US/vsto/thread/a12add6b-99ea-4677-8245-cd667101683e/. When testing, please close all word documents, and see if this warning also shows. You could check Word process in Task Manager. Code like this,

                Dim wordApp As New Word.Application()
                wordApp.Visible = True
                Dim wordTrue As Object = DirectCast(True, Object)
                Dim wordFalse As Object = DirectCast(False, Object)
                Dim fileToOpen As Object = "C:\Temp\11.docx"
                Dim missing As Object = Type.Missing
                Dim doc As Word.Document
                doc = wordApp.Documents.Open(fileToOpen, missing, wordFalse, wordFalse, missing, missing, missing, missing, missing, missing, missing, wordTrue, missing, missing, missing, missing)
                '...
                doc.Close(missing, missing, missing)
                Marshal.ReleaseComObject(doc)
                wordApp.Quit(missing, missing, missing)
                Marshal.FinalReleaseComObject(wordApp)


    If you have any concern for this, please feel free to follow up.

    Best regards,
    Bessie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by rubiks Hugh Tuesday, March 02, 2010 2:29 AM
    • Marked as answer by Bessie Zhao Wednesday, March 03, 2010 5:58 AM
    Monday, March 01, 2010 5:30 AM

All replies

  • Any help would be much appreciated.
    Friday, February 26, 2010 4:27 AM
  • seriously, any help
    Monday, March 01, 2010 2:28 AM
  • Hello Rubik,

    Sorry for delay. From what you said, it seems that you are not working with VSTO project. This forum is mainly targeted at VSTO technology. You could refer to this forum's PLEASE READ FIRST to know more about this technology. If I understand you correctly, you are working with Excel Automation and Word Automation. The best place for this issue would be Excel newsgroup and Word newsgroup. You will find appropriate entry links in the about link.

    For Excel, you could refer to the following code to get the range where you want to read data. After getting this range, use foreach loop to access each cell.

                Dim objExcel As New Excel.Application()
                Dim missing As Object = Type.Missing
                Dim wk As Excel.Workbook = objExcel.Workbooks.Open("C:\Temp\1.xlsx", missing, missing, missing, missing, missing,  missing, missing, missing, missing, missing, missing, missing, missing, missing)
                objExcel.Visible = True
                Dim ws As Excel.Worksheet = TryCast(wk.Worksheets(1), Excel.Worksheet)
                Dim range As Excel.Range = ws.Application.get_Range("A1", "D2")

                For Each cell As Excel.Range In range.Cells
                    'access each cell of this range
                    MessageBox.Show(cell.Value2.ToString())
                Next

                Marshal.FinalReleaseComObject(range)
                Marshal.FinalReleaseComObject(ws)
                wk.Close(missing, missing, missing)
                Marshal.FinalReleaseComObject(wk)
                objExcel.Quit()
                Marshal.FinalReleaseComObject(objExcel) 
                GC.Collect()
                GC.WaitForPendingFinalizers()
                GC.Collect()
                GC.WaitForPendingFinalizers()


    For the warning of Word, please ensure that you have release com objects correctly. To release com objects, please refer to this thread: http://social.msdn.microsoft.com/forums/en-US/vsto/thread/a12add6b-99ea-4677-8245-cd667101683e/. When testing, please close all word documents, and see if this warning also shows. You could check Word process in Task Manager. Code like this,

                Dim wordApp As New Word.Application()
                wordApp.Visible = True
                Dim wordTrue As Object = DirectCast(True, Object)
                Dim wordFalse As Object = DirectCast(False, Object)
                Dim fileToOpen As Object = "C:\Temp\11.docx"
                Dim missing As Object = Type.Missing
                Dim doc As Word.Document
                doc = wordApp.Documents.Open(fileToOpen, missing, wordFalse, wordFalse, missing, missing, missing, missing, missing, missing, missing, wordTrue, missing, missing, missing, missing)
                '...
                doc.Close(missing, missing, missing)
                Marshal.ReleaseComObject(doc)
                wordApp.Quit(missing, missing, missing)
                Marshal.FinalReleaseComObject(wordApp)


    If you have any concern for this, please feel free to follow up.

    Best regards,
    Bessie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by rubiks Hugh Tuesday, March 02, 2010 2:29 AM
    • Marked as answer by Bessie Zhao Wednesday, March 03, 2010 5:58 AM
    Monday, March 01, 2010 5:30 AM