macro to take info from Word to Excel

[jlamusga]

Help! I am fairly new to using macros (I did most of my programming in FORTRAN, yikes!) and I need to do a task. I have a form in Word that I go through and fill out different fields in the table with text. I need to be able to copy each specific field to an excel spreadsheet so we can have a database of the info. So far I am just recording a macro and trying to modify it. I can get excel to open, then i am stuck. This is what I have so far:
Sub Testing()
'
' Testing Macro
' Macro recorded 9/21/2006 by xzkryj
'
Selection.HomeKey Unit:=wdStory
Selection.MoveDown Unit:=wdLine, Count:=2
Selection.MoveRight Unit:=wdCell
Selection.Copy
Set appEX = CreateObject("Excel.Application")
appEX.Visible = True
appEX.Workbooks.Open FileName:="D:/AE Service Session/macro.xls"
End Sub

It needs to take the value that it copied and paste it into the first blank cell in column A. Any ideas?
-Joe

 

[jlamusga]

I had one more situation come up. It now pastes a value into the excel spread sheet, but I then want the macro to go back to the word doc and tab over to the next table window with applicable data and perform the same operation. What do I need to add to the macro to get it to go back to the Word doc and perform the operation over again? Is this even possible?

 

[Ziggy12]

How do you do it around the other way

I have a data in excel in cells that needs to be exported to word and the word document needs to have text entered that is not in the spreadsheet i.e. hard coded or input boxed into the excel macro creating the word doc

ziggy.

 

[jlamusga]

I am not sure, I am still very new to macro programming. I need help figuring out how to go back and forth between two applications (word and excel). I have the macro working to take a value from the word doc and go over to excel and paste it, but now i need it to go back to the word doc, get a new value, then go back into the excel sheet and paste it in a different column. I don't even know if this is possible actually.
-JL

 

[Cbrine]

Are you using texasalynn's code? If so, you will need to setup a reference to your excel workbook, and to your word doc. You can then use these references to "switch" back and forth. You can then use

wb.save, ws.range("A1").end(xlup).offset(1,0), etc....

wb. references the excel workbook
ws. references the active excel worksheet after the add.

Sub OpenAndReadWordDoc() 
Dim wrdApp As Word.Application 
Dim wrdDoc As Word.Document 
Dim tString As String, tRange As Word.Range 
dim wb as workbook, ws as worksheet
Dim p As Long, r As Long 

'    Workbooks.Add ' create a new workbook 
Worksheets("Input Data").Activate 
    With Range("A1") 
        .Formula = "Word Document Contents:" 
        .Font.Bold = True 
        .Font.Size = 14 
        .Offset(1, 0).Select 
    End With 
set wb = activeworkbook
set ws = activesheet
   r = 3 ' startrow for the copied text from the Word document 
    Set wrdApp = CreateObject("Word.Application") 
    'wrdApp.Visible = True 
    FilePath = Application.GetOpenFilename("Microsoft Word Document(*.doc), *.doc") 
    Set wrdDoc = wrdApp.Documents.Open(FilePath) 
    ' example word operations 
    With wrdDoc 
        For p = 1 To .Paragraphs.Count 
            Set tRange = .Range(Start:=.Paragraphs(p).Range.Start, _ 
                End:=.Paragraphs(p).Range.End) 
            tString = tRange.Text 
            tString = Left(tString, Len(tString) - 1) 
            ' check if the text has content 
            If tString <> "" Then 
                ' fill into active worksheet 
                ActiveSheet.Range("A" & r).Formula = tString 
                r = r + 1 
            End If 
        Next p 
        .Close ' close the document 
    End With 
'    wrdApp.Quit ' close the Word application 
    Set wrdDoc = Nothing 
    Set wrdApp = Nothing 
    Call TrimChr 
    Call cTransfer 
    Call CSUpdates 
    Worksheets("Input Data").Activate 
    Range("A:A").Clear 
    Worksheets("Start").Activate 
    MsgBox "Done" 
    ActiveWorkbook.Saved = True 
End Sub

HTH
Cal

 

[jlamusga]

I am using cbrine's solution. Right now I am just making seperate macros that the user will have to manually click through. Ideally I could do it in one macro that would just automatically switch between the 2 applications. I am just not sure how to do that.

 

[Cbrine]

OK,
So your macro is created in Word. This code below is an example of setting up references for both word and excel from a word macro. You will need to integrate it with your current code. Once it's added, just us
ThisDoc. to do any processing on your word doc functions.
Wb. to access the excel workbook funcitons.
ws. to access the excel worksheet functions.

Private Sub CommandButton1_Click()
Dim ThisDoc As Document
Dim XlApp As Object
Dim wb As Object, ws As Object

Set ThisDoc = ActiveDocument
Set XlApp = CreateObject("Excel.Application")
XlApp.Visible = True
XlApp.workbooks.Add
Set wb = XlApp.activeworkbook
Set ws = XlApp.activesheet

End Sub

HTH
Cal

 

[jlamusga]

So far I have this:

Sub testmacro()
'
' testmacro Macro
' Macro recorded 9/22/2006 by xzkryj
'
    Dim ThisDoc As Document
    Dim XlApp As Object
    Dim wb As Object, ws As Object
    
    Selection.HomeKey Unit:=wdStory
    Selection.MoveDown Unit:=wdLine, Count:=2
    Selection.MoveRight Unit:=wdCell
    Selection.Copy
    
    Set ThisDoc = ActiveDocument
    Set XlApp = CreateObject("Excel.Application")
    XlApp.Visible = True
    XlApp.Workbooks.Open FileName:="D:\AE Service Session\macro.xls"
    XlApp.ActiveSheet.Range("A" & XlApp.Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Set wb = XlApp.ActiveWorkbook
    Set ws = XlApp.ActiveSheet

It copies the value from the word doc, pastes it into the excel sheet, but now I am stuck on how to use the above code to get it to go back to the word dock. I have tried many different things, but I just don't know. To do operations on the word doc would I just type ThisDoc.Select.Copy or something like that? I am beginning to think I should just invest in a VBA book.
-JL

 

[Cbrine]

Just use the ThisDoc.

ThisDoc. = Your Word document

When you hit the . in the name you will be presented with a list of available methods and properties. Select from this list and it will execute on your word document.

ThisDoc.copy(If this is an legal command)
Would copy your entire document.

HTH
Cal

 

[jlamusga]

It now works completely. Thank you very much for all your assistance!
-Joe

 

[UHsoccer]

Question to Cbrine

I use your code to copy content from all word documents in a folder.

When completed, I look with Task Manager at my processes and see one WINWORD.EXE process for each document that I copied from.

I need to process close to 3,000 documents

How to I kill each process left behind as I execute?