Using VBA to import Word doc into Excel Spreadsheet 1

[tearsmith]

All,

I am fairly new to VBA and I have a question that is probably pretty easy, maybe you could help?

I need to copy/paste a word document into an excel worksheet. I would like to have SHEET2, CELL A1 have the word IN RUN underlined. When a user clicks on IN RUN, I want file c:\My Documents\PPO IN RUN.doc to open and page 2 - 6 to be pasted into cell B1.

I tried a basic macro record, but it only records the copy and paste and doesn't pick up that you have to go to Word and copy a certain file.

Help is greatly appreciated.

Thanks
tearsmith

 

[SkipVought]

Hi,

So you want the CONTENTS of the document to be pasted in B2.

You do not want the file to be inserted in B2.

I am assuming that you are in Excel to begin with.

What you need to do is set a document object using the CreateObject method.

First, you'll need to set a reference to the Word Object Library. In the Excel VB Editor, menu item Tools/References - scroll down to Microsoft Word x.y Object Library and check the box.

Sub CopyDoc()
    Dim docAppl As Word.Application, docMyDoc As Word.document
    MyFileName = "C:\test1.doc"
    Set docAppl = CreateObject("Word.Application")
    Set docMyDoc = Documents.Open(MyFileName)
    With docMyDoc
        .Range(Start:=0, End:=.Characters.Count).Copy
    End With
    [B1].Select
    ActiveSheet.Paste
    
    Set docMyDoc = Nothing
    Set docAppl = Nothing
End Sub

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[tearsmith]

Thank you very much for your reply. I just want to clarify... how do I make the words IN RUN in cell A1, run the macro I've just written?

Thanks,
Tearsmith

 

[SkipVought]

I think that it would be better to use a command button from the Control toolbox toolbar.

But if you want to use A1, you can use the Worksheet_SelectionChange event. The only problem with the selection change event is that if you want to then the macro again, you have to select some other cell and then select A1.

To get to the worksheet events, right click the sheet tab and select view code.

In the code window select Worksheet from the Object drop down and SelectionChange from the Procedure drop down.

In the worksheet selection change procedure, enter your macro name

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   MyMacro
End Sub

Hope this helps


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[tearsmith]

SkipVought,

You are the man! I've figured out the macro and assigned it a button on the toolbar. It did seem easier than using an object in A1. One question, this module is named CopyDoc. I need the same function on 2 other sheets. The command is exactly the same, but the Word document I am referencing is different. When I edit the macro, call is CopyDoc 2 and change the file name, it runs beautifully. Only problem, the CopyDoc original macro is then gone. How can I add the second and third worksheet functions to the same macro so that all three copy/paste commands will be done at the same time? Do I just insert between Set docAppl = Nothing and End Sub, and repeat the commands again with a different file destination each time?

Thanks
Tearnie

 

[SkipVought]

Sub CopyDoc()
    Dim docAppl As Word.Application, docMyDoc As Word.document
    Set docAppl = CreateObject("Word.Application")
for i = 1 to 3
  select case i
    case 1
      MyFileName = "C:\test1.doc"
      DestSheet = "Sheet1"
    case 2
      MyFileName = "C:\test1.doc"
      DestSheet = "Sheet2"
    case 3
      MyFileName = "C:\test1.doc"
      DestSheet = "Sheet3"
  end select
    Set docMyDoc = Documents.Open(MyFileName)
    With docMyDoc
        .Range(Start:=0, End:=.Characters.Count).Copy
    End With
    Worksheets(DestSheet).[B1].Select
    ActiveSheet.Paste
next    
    Set docMyDoc = Nothing
    Set docAppl = Nothing
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com