Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 15th, 2011, 11:26 AM
Authorized User
 
Join Date: May 2011
Posts: 44
Thanks: 7
Thanked 0 Times in 0 Posts
Exclamation Open word document from excel

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

Last edited by cjcant; June 15th, 2011 at 12:00 PM.. Reason: forgot to write a couple of things.
 
Old June 15th, 2011, 08:57 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old June 16th, 2011, 04:20 AM
Authorized User
 
Join Date: May 2011
Posts: 44
Thanks: 7
Thanked 0 Times in 0 Posts
Default error

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

Last edited by cjcant; June 16th, 2011 at 04:25 AM..
 
Old June 16th, 2011, 10:17 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
The Following User Says Thank You to Shasur For This Useful Post:
cjcant (June 17th, 2011)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Office Automation- open word document ayazhoda Access VBA 2 November 4th, 2008 12:01 PM
open word document and insert data PorcupineRabbit Visual Basic 2005 Basics 1 August 9th, 2007 06:31 PM
open, change a excel document from word kareltje Excel VBA 2 February 14th, 2005 11:23 AM
Cannot open Word Document At Client Side leo_ling Classic ASP Basics 6 July 1st, 2004 12:31 AM
How to open word document using ASP and read ksaleemkhan Classic ASP Components 3 April 28th, 2004 04:43 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.