Open word document from excel

cjcant · June 15th, 2011, 11:26 AM

Hi,

I am trying to insert excel data into a word document. Below is my code to show how I am trying to do it:

Code:

Private Sub chkboxTest_Click()
    Dim oXA As Word.Application
    Dim oXB As Word.Document
    Dim rngExcelTest As Word.Range
    Dim strAddCheck As String
    
    
    Set oXA = Word.Application
    If strAddCheck = "added" Then
        Set oXB = oXA.Documents.Open("H:\proposalDocumentDevelopment\HVAC\experiment\excel\LayoutTest1ExcelTest.docm")
    Else
        Set oXB = oXA.Documents.Add("H:\proposalDocumentDevelopment\HVAC\experiment\excel\LayoutTest1ExcelTest.docm")
        strOpenCheck = "added"
    End If
    
    Set rngExcelTest = oXB.Bookmarks("excelTest").Range
    
    'If checked insert into bookmark excelTest
    If Me.chkboxTest.Value = True Then
        rngExcelTest = Cells(14, 4)
        
        oXB.Bookmarks.Add "excelTest", rngExcelTest

    Else 'clear bookmark
        rngExcelTest = "off"
        
        oXB.Bookmarks.Add "excelTest", rngExcelTest
        
    End If
    

End Sub

However, the file which it is inserting data(H:\proposalDocumentDevelopment\HVAC\experimen t\excel\LayoutTest1ExcelTest.docm) is a sort of template and is therefore read only.

I did try having the user searching for a copy of the document but it would just ammend the document without showing it to the user. I also need to update the document several times within the one sitting so I cannot just use the add function as this kept bringing up multiple documents (each one having a different change on it.

I am not very experienced at VBA so could you please include fairly detailed comments.

Please help!

Thanks

Shasur · June 15th, 2011, 08:57 PM

The primary reason the document is Read-Only might be because the code is not releasing the document

Add these lines at the end of your code

oXB.Save

or

oXB.SaveAs("H:\proposalDocumentDevelopment\HVAC\ex periment\excel\LayoutTest1ExcelTest_New.docm")

and then release the object from memory

if not oXB is nothing then set oXB = nothing

HTH

Cheers
Shasur

cjcant · June 16th, 2011, 04:20 AM

Hi, Thanks for getting back to me.

I have come accross 2 problems:

A) the document doesn't open automatically
B) when I try to open it manually the following message appears:

The file LayoutTestExcelTest_New cannot be opened because there are problems with the contents.

And when I say it is a read only document it is because I had set it to be that way so the master copy isn't changed.

It is possible to do it a different way instead. What I am trying to do is to insert data from an excel spreadsheet when the user checks the relevent checkbox. What I had been previously doing was just importing the data from excel and inserting it in but the client wants to be able to view the descriptions (which take up much space) in excel. Is it therefore possible to open an excel document within word and do this.

This might sound like a lot of garbasge to you so just let me know if you need clarification.

Thanks

Shasur · June 16th, 2011, 10:17 PM

The document doesn't open because we didn't close it

You can either do

oXB.SaveAs("H:\proposalDocumentDevelopment\HVAC\ex periment\excel\LayoutTest1ExcelTest_New.docm")
oXB.Close (false)
if not oXB is nothing then set oXB = nothing

(or)
oXB.SaveAs("H:\proposalDocumentDevelopment\HVAC\ex periment\excel\LayoutTest1ExcelTest_New.docm")
oXA.Visible = True

Cheers
Shasur