[RESOLVED] Using variables from a called macro

[sh1870]

I have many different text files in the same format from which I need to pull some pieces of data. I'm doing this by having a list of filenames in excel which are looped through. The macro then opens the document in Word and calls a macro in Word. This macro then uses the fact that all the documents have the same format to pull the necessary strings out through moving and extending certain character lengths through the document. It does this for 3 strings per document and names them, eg the Year string will be the four character string of 2012 or 2011 or etc.

My problem lies with when the Word routine finishes and back in Excel, I want to put these strings into cells adjacent to the filename. I presume that as the Word macro has finished, the names are forgotten, or the Excel macro doesn't recognise the names as they've been created somewhere else. I've tried putting my Word code into the Excel macro (by using WordApp.Selection...) but this causes Excel to crash without fail.

Any ideas for getting what I need from Word across to Excel? Simple copy and paste looks out as I have more than one piece of data to copy. Here's my code so far; the Excel bit and then the Word bit:

Sub GetDates()

    Dim WordApp As Word.Application
    Dim WordDoc As Word.Document
    Set WordApp = CreateObject("Word.Application")
    WordApp.Visible = True
    Z = Range("A1").End(xlDown).Row
    Path = "C:\"
    For i = 2 To Z
        Set WordDoc = WordApp.Documents.Open(Path & "\" & Cells(i, 1))
        WordApp.Run ("GetDates")
        Cells(i, 2) = GotDate
        Cells(i, 4) = GotTime
        Cells(i, 3) = GotYear
        WordDoc.Close
    Next i
    WordApp.Quit
    
End Sub

Sub GetDates()

    With Selection.Find
        .Text = "Created"
        .Forward = True
        .MatchWholeWord = True
        .Execute
    End With
    Selection.MoveRight Unit:=wdCharacter, Count:=8
    
    Selection.Extend
    With Selection.Find
        .Text = "*"
        .Forward = True
        .MatchWholeWord = True
        .Execute
    End With
    Selection.MoveLeft Unit:=wdCharacter, Count:=14
    
    GotDate = Selection
    Selection.ExtendMode = False
    
    Selection.MoveRight Unit:=wdCharacter, Count:=8    Selection.Extend
    Selection.MoveRight Unit:=wdCharacter, Count:=8   
    GotTime = Selection
    Selection.ExtendMode = False
    
    Selection.MoveRight Unit:=wdCharacter, Count:=54    Selection.Extend
    Selection.MoveRight Unit:=wdCharacter, Count:=4
    
    GotYear = Selection
    
End Sub

[westconn1]

as you do not show where you declare your word variables, we can only assume that you do not
if you declare the variables as public at module level in an object module, you should be able to return there values as properties of the wrddoc object

[sh1870]

as you do not show where you declare your word variables, we can only assume that you do not
if you declare the variables as public at module level in an object module, you should be able to return there values as properties of the wrddoc object

Thanks for the reply.

I had not at time of posting but have since tried it after finding this page on the subject of variable lifetime.

It's still not working, presumably because the Word macro has an End statement causing the variables to be forgotten as the routine heads back to the Excel VBA code.

[westconn1]

It's still not working, presumably because the Word macro has an End statement causing the variables to be forgotten

as long as you return the result before closing the document, it should work fine

declaring variables as public in the thisworkbook code module would work, but it would need the variables to be declared in each document, which may be a problem, so you would have to try declaring them in the template that is used by the opened document

alternatively you can save your variables values to any form of storage and retrieve by excel
or you might even be able to automate the workbook from your word macro to place the values directly in the cells

[sh1870]

as long as you return the result before closing the document, it should work fine

declaring variables as public in the thisworkbook code module would work, but it would need the variables to be declared in each document, which may be a problem, so you would have to try declaring them in the template that is used by the opened document

alternatively you can save your variables values to any form of storage and retrieve by excel
or you might even be able to automate the workbook from your word macro to place the values directly in the cells

Running the Excel code step by step, the variables to put in the cells wouldn't be recognised after the Word code had been called and ran (and before the Word document was closed).

I've managed to get the system working through automating Excel through Word though. Thanks for the suggestions

[westconn1]

Running the Excel code step by step, the variables to put in the cells wouldn't be recognised after the Word code had been called and ran (and before the Word document was closed).

the testing i did was able to return the values to excel without problem