In message <
[email protected].
com> of Sun, 13 Nov 2011 08:24:34 in microsoft.public.excel.programming,
JingleRock said:
Walter,
Thanks for your response.
In theory, your plan sounds great. The part about recording an Excel
macro to paste from the clipboard is straight-forward.
However, the part about recording a macro in Word VBA, using keyboard
instructions in Word, and then converting it to run in Excel VBA is
problematic, for me.
Any additional tips?
Also, I don't know if you saw the following:
I now see that the three numbers I want to copy/paste are three cells
in a 4x5 table in my .rtf file.
I don't know if the above makes any difference.
That is relevant to how you select the area to be copied.
You first need to be able to fill the clipboard manually.
You then need to record a macro. If you get to that point and show it
here, I MAY be able to give some advice on conversion.
You should be able to construct a macro to construct a minimal file.
To get you started, this constructs a table of 4 columns and 5 rows. Is
that what you mean by 4x5?
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 13/11/2011 by IBM
'
ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=5, _
NumColumns:= 4, _
DefaultTableBehavior:=wdWord9TableBehavior, _
AutoFitBehavior:= wdAutoFitFixed
With Selection.Tables(1)
If .Style <> "Table Grid" Then
.Style = "Table Grid"
End If
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = True
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = True
End With
End Sub
To make that macro, I opened word, did Alt+T M R to record a macro,
accepted the default name and changed the location to run to the current
file, created the table, stopped recording, copied the macro here and
readjusted line breaks to fit in 72 columns. In practice, I would
simplify the code first produced by the macro recorder by throwing away
everything not needed to produce the desired result.
I have no notion how to do the same thing in 2010, which, ISTR, uses a
ribbon rather than a top row of menus. You need to get such knowledge.
You may want to start with a simpler problem.
I don't know the relevance of your Sub FindBMark(). Did you succeed in
making it work? If not: to make it do so, I suggest you construct and
save "C:\My Documents\Wordtest.doc" to contain some text indicated by a
bookmark called City. I suggest you then put the macro in another file
and run that macro. Before running the macro, back up Wordtest.doc so
that it can be recreated with minimal effort.
Your copy of FindBMark does illustrate some issues:
1) It is unfriendly to copy and paste default layouts to news and leads
to tedious errors like
Set wordDoc = wordApp.Documents.Open("C:\My Documents
\Wordtest.doc")
2) Rather than use early binding as in
Dim wordApp As Word.Application
I would use late binding to simplify porting code as in
Dim wordApp As Object
or even
Dim wordApp
Word.Application might be known to Word and unknown to Excel.
Your code does not need to rely on it being known to either.
Early binding is said to be use less resources than late; that will not
be an issue. The slow parts of the code will be that to start Word and
access the file.