Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XCL 2010 VBA: I want to copy/paste from a rtf file to a spreadsheet
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XCL 2010 VBA: I want to copy/paste from a rtf file to a spreadsheet
On Nov 12, 7:35*pm, JingleRock wrote:
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
XCL 2010 VBA: I want to copy/paste from a rtf file to a spreadsheet
In message
s.com of Sat, 12 Nov 2011 17:35:57 in microsoft.public.excel.programmin g, JingleRock writes 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"? -- Walter Briscoe |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
XCL 2010 VBA: I want to copy/paste from a rtf file to a spreadsheet
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 Briscoe 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
XCL 2010 VBA: I want to copy/paste from a rtf file to a spreadsheet
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
XCL 2010 VBA: I want to copy/paste from a rtf file to a spreadsheet
In message .
com of Sun, 13 Nov 2011 08:24:34 in microsoft.public.excel.programming, JingleRock writes 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 Briscoe 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. -- Walter Briscoe |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
XCL 2010 VBA: I want to copy/paste from a rtf file to a spreadsheet
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
XCL 2010 VBA: I want to copy/paste from a rtf file to a spreadsheet
JingleRock presented the following explanation :
On Nov 12, 7:35*pm, JingleRock wrote: 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. 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! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2010 code wont copy/paste to values | Excel Programming | |||
For dates, copy/paste special/values for 2006 gives me 2010--Why | Excel Worksheet Functions | |||
Copy and paste of a spreadsheet | Excel Discussion (Misc queries) | |||
copy multiple worksheets of a workbook, and paste onto a Word document ( either create new doc file or paste onto an existing file.) I need this done by VBA, Excel Macro | Excel Programming | |||
Copy and paste from one spreadsheet to another | Excel Worksheet Functions |