|
|
|
date: Wed, 08 Jun 2005 21:26:24 -0500,
group: microsoft.public.word.word6-7macros
back
Re: Word and Excell
John wrote:
> Jay Freedman wrote:
>
>> On Wed, 08 Jun 2005 21:26:24 -0500, John wrote:
>>
>>
>>> Is it possible for a word macro to open an excel document and get
>>> data out of it? This is in office 2003.
>>>
>>> Thanks
>>>
>>> John
>>
>>
>> Yes, it is possible. See
>> http://www.word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm for
>> example.
>>
>> --
>> Regards,
>> Jay Freedman
>> Microsoft Word MVP FAQ: http://word.mvps.org
>
> Excellent! Thank you. In this part, though:
>
> For Each oSheet In oXL.ActiveWorkbook.Worksheets
> 'put guts of your code here
> 'get next sheet
> Next oSheet
>
> What code am I using? Word or Excel? In word I have:
>
> County of residences
>
> I want to get that info from cells(3,8) in an excel worksheet.
> Actually hundreds of them in one workbook. Can I just use a variable?
> Like x (string) = Cells(3,8) having defined the workbook and
> worksheet? Then just stick the variable in the sentence?
>
> Thanks
>
> John
Hi John,
Assuming your data is all in Sheet1, remove the four lines you quoted and
replace them with something like this:
Dim DataString As String
Dim myRange As Range
DataString = oXL.ActiveWorkbook _
.Worksheets("Sheet1").Range("H3").Value
Set myRange = ActiveDocument.Bookmarks("county").Range
myRange.Text = DataString
ActiveDocument.Bookmarks.Add Name:="county", _
Range:=myRange
This assumes that your Word document (or, more likely, the template on which
the document was based) contains a bookmark named "county" located after the
"County of residence" text; that's where the macro will insert the data that
it gets from the worksheet. You can, of course, use any other name for the
bookmark, as long as you change both locations in the macro where the name
appears. Similarly, if the sheet name is something other than Sheet1, change
the name in the macro.
The expression .Range("H3").Value will retrieve the content of cell H3 [I
assume that's what you meant by cell(3,8)] and place it in the string
variable DataString. You can use a string variable in place of the quoted
literal, and then compute the value of that string as necessary.
--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
date: Thu, 9 Jun 2005 13:32:52 -0400
author: Jay Freedman
Re: Word and Excell
Jay Freedman wrote:
> John wrote:
>> Jay Freedman wrote:
>>
>>> On Wed, 08 Jun 2005 21:26:24 -0500, John wrote:
>>>
>>>
>>>> Is it possible for a word macro to open an excel document and get
>>>> data out of it? This is in office 2003.
>>>>
>>>> Thanks
>>>>
>>>> John
>>>
>>>
>>> Yes, it is possible. See
>>> http://www.word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm for
>>> example.
>>>
>>> --
>>> Regards,
>>> Jay Freedman
>>> Microsoft Word MVP FAQ: http://word.mvps.org
>>
>> Excellent! Thank you. In this part, though:
>>
>> For Each oSheet In oXL.ActiveWorkbook.Worksheets
>> 'put guts of your code here
>> 'get next sheet
>> Next oSheet
>>
>> What code am I using? Word or Excel? In word I have:
>>
>> County of residences
>>
>> I want to get that info from cells(3,8) in an excel worksheet.
>> Actually hundreds of them in one workbook. Can I just use a variable?
>> Like x (string) = Cells(3,8) having defined the workbook and
>> worksheet? Then just stick the variable in the sentence?
>>
>> Thanks
>>
>> John
>
> Hi John,
>
> Assuming your data is all in Sheet1, remove the four lines you quoted
> and replace them with something like this:
>
> Dim DataString As String
> Dim myRange As Range
> DataString = oXL.ActiveWorkbook _
> .Worksheets("Sheet1").Range("H3").Value
> Set myRange = ActiveDocument.Bookmarks("county").Range
> myRange.Text = DataString
> ActiveDocument.Bookmarks.Add Name:="county", _
> Range:=myRange
>
> This assumes that your Word document (or, more likely, the template
> on which the document was based) contains a bookmark named "county"
> located after the "County of residence" text; that's where the macro
> will insert the data that it gets from the worksheet. You can, of
> course, use any other name for the bookmark, as long as you change
> both locations in the macro where the name appears. Similarly, if the
> sheet name is something other than Sheet1, change the name in the
> macro.
>
> The expression .Range("H3").Value will retrieve the content of cell
> H3 [I assume that's what you meant by cell(3,8)] and place it in the
> string variable DataString. You can use a string variable in place of
> the quoted literal, and then compute the value of that string as
> necessary.
Not being an Excel expert, I missed the fact that you can use the .Cells()
property in place of the .Range() property on a worksheet. This line will
work just like the one above, but it's easier to compute the row and column
numbers than to compute the range expression:
DataString = oXL.ActiveWorkbook _
.Worksheets("Sheet1").Cells(3, 8).Value
--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
date: Thu, 9 Jun 2005 13:41:24 -0400
author: Jay Freedman
Re: Word and Excell
Jay Freedman wrote:
> Jay Freedman wrote:
>
>>John wrote:
>>
>>>Jay Freedman wrote:
>>>
>>>
>>>>On Wed, 08 Jun 2005 21:26:24 -0500, John wrote:
>>>>
>>>>
>>>>
>>>>>Is it possible for a word macro to open an excel document and get
>>>>>data out of it? This is in office 2003.
>>>>>
>>>>>Thanks
>>>>>
>>>>>John
>>>>
>>>>
>>>>Yes, it is possible. See
>>>>http://www.word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm for
>>>>example.
>>>>
>>>>--
>>>>Regards,
>>>>Jay Freedman
>>>>Microsoft Word MVP FAQ: http://word.mvps.org
>>>
>>>Excellent! Thank you. In this part, though:
>>>
>>> For Each oSheet In oXL.ActiveWorkbook.Worksheets
>>> 'put guts of your code here
>>> 'get next sheet
>>>Next oSheet
>>>
>>>What code am I using? Word or Excel? In word I have:
>>>
>>>County of residences
>>>
>>>I want to get that info from cells(3,8) in an excel worksheet.
>>>Actually hundreds of them in one workbook. Can I just use a variable?
>>>Like x (string) = Cells(3,8) having defined the workbook and
>>>worksheet? Then just stick the variable in the sentence?
>>>
>>>Thanks
>>>
>>>John
>>
>>Hi John,
>>
>>Assuming your data is all in Sheet1, remove the four lines you quoted
>>and replace them with something like this:
>>
>> Dim DataString As String
>> Dim myRange As Range
>> DataString = oXL.ActiveWorkbook _
>> .Worksheets("Sheet1").Range("H3").Value
>> Set myRange = ActiveDocument.Bookmarks("county").Range
>> myRange.Text = DataString
>> ActiveDocument.Bookmarks.Add Name:="county", _
>> Range:=myRange
>>
>>This assumes that your Word document (or, more likely, the template
>>on which the document was based) contains a bookmark named "county"
>>located after the "County of residence" text; that's where the macro
>>will insert the data that it gets from the worksheet. You can, of
>>course, use any other name for the bookmark, as long as you change
>>both locations in the macro where the name appears. Similarly, if the
>>sheet name is something other than Sheet1, change the name in the
>>macro.
>>
>>The expression .Range("H3").Value will retrieve the content of cell
>>H3 [I assume that's what you meant by cell(3,8)] and place it in the
>>string variable DataString. You can use a string variable in place of
>>the quoted literal, and then compute the value of that string as
>>necessary.
>
>
> Not being an Excel expert, I missed the fact that you can use the .Cells()
> property in place of the .Range() property on a worksheet. This line will
> work just like the one above, but it's easier to compute the row and column
> numbers than to compute the range expression:
>
> DataString = oXL.ActiveWorkbook _
> .Worksheets("Sheet1").Cells(3, 8).Value
>
This is so cool... thanks much
John
date: Thu, 09 Jun 2005 23:07:35 -0500
author: John
|
|
