[RESOLVED] Using variables from a called macro
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:
Code:
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
Code:
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
Re: Using variables from a called macro
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
Re: Using variables from a called macro
Quote:
Originally Posted by
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
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.
Re: Using variables from a called macro
Quote:
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
Re: Using variables from a called macro
Quote:
Originally Posted by
westconn1
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 :)
Re: [RESOLVED] Using variables from a called macro
Quote:
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
