XCL 2010 VBA: I want to copy/paste from a rtf file to a spreadsheet

J

JingleRock

I get a new .rtf file on a weekly basis; the format is exactly the
same in each. I want to copy/paste three adjacent 8-digit numbers,
using VBA, (each is in a different "cell", as far as doing a manual
copy/paste from the .rtf file to a spreadsheet is concerned) from
each .rtf file to a spreadsheet.

Is it necessary to open the Word .rtf file using XCL VBA? How is it
possible to refer to the locations of the three adjacent 8-digit
numbers?
 
J

JingleRock

I get a new .rtf file on a weekly basis; the format is exactly the
same in each. I want to copy/paste three adjacent 8-digit numbers,
using VBA, (each is in a different "cell", as far as doing a manual
copy/paste from the .rtf file to a spreadsheet is concerned) from
each .rtf file to a spreadsheet.

Is it necessary to open the Word .rtf file using XCL VBA? How is it
possible to refer to the locations of the three adjacent 8-digit
numbers?

I now see that the three numbers I want to copy/paste are three cells
in a 4x5 table in my .rtf file.
 
W

Walter Briscoe

In message <[email protected]
s.com> of Sat, 12 Nov 2011 17:35:57 in microsoft.public.excel.programmin
g said:
I get a new .rtf file on a weekly basis; the format is exactly the
same in each. I want to copy/paste three adjacent 8-digit numbers,
using VBA, (each is in a different "cell", as far as doing a manual
copy/paste from the .rtf file to a spreadsheet is concerned) from
each .rtf file to a spreadsheet.

Is it necessary to open the Word .rtf file using XCL VBA? How is it
possible to refer to the locations of the three adjacent 8-digit
numbers?

I suggest you work out how to fill the clipboard using keyboard
instructions in Word, record a macro in Word and do whatever conversion
is necessary to run that macro from Excel VBA. (CreateObject("Word.Appli
cation") opens Word from either Excel or Word.) You can record an Excel
macro to paste the clipboard. That is what I would do in Office 2003 and
assume the same process will work in 2010.

Where did you find the term "XCL"?
 
J

JingleRock

I suggest you work out how to fill the clipboard using keyboard
instructions in Word, record a macro in Word and do whatever conversion
is necessary to run that macro from Excel VBA. (CreateObject("Word.Appli
cation") opens Word from either Excel or Word.) You can record an Excel
macro to paste the clipboard. That is what I would do in Office 2003 and
assume the same process will work in 2010.

Where did you find the term "XCL"?

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.
Regards,
JingleRock
 
J

JingleRock

I came across the following VBA Code; this may be helpful.

Sub FindBMark()

Dim wordApp As Word.Application
Dim wordDoc As Word.Document
Dim wordRange As Word.Range

Set wordApp = CreateObject("Word.Application")
Set wordDoc = wordApp.Documents.Open("C:\My Documents
\Wordtest.doc")

wordApp.Visible = True

' go to the bookmark named "City."
Set wordRange = wordDoc.GoTo(What:=wdGoToBookmark, Name:="City")
wordRange.InsertAfter "Los Angeles"

' print the document.
wordDoc.PrintOut Background:=False

' save the modified document.
wordDoc.Save

' quit Word without saving changes to the document.
wordApp.Quit SaveChanges:=wdDoNotSaveChanges

Set wordApp = Nothing

End Sub
 
W

Walter Briscoe

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.
 
J

JingleRock

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.
Regards,
JingleRock

PROBLEM RE-STATEMENT:
Manually, I can copy the table of interest (there are 16 tables) in
my .rtf file to my macro spreadsheet; this works fine.
However, I want to automate the above sentence.
I tried opening the .rtf file in Word, and then creating Bookmarks,
but had zero success.
Regards,
JingleRock
 
G

GS

JingleRock presented the following explanation :
I now see that the three numbers I want to copy/paste are three cells
in a 4x5 table in my .rtf file.

If the file is ALWAYS the same, as you say, then why not use normal VB
file I/O to read the file into an array and extract you data from its
relative position in the array? This would certainly be more efficient
than messing around with Word <ugh!>, not to mention considerably
faster to boot!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top