#1
|
|||
|
|||
Word to Excel
Hi Office Gods, so I have a word document that has specific tool labels like the attached. I could copy paste it manually but sometimes our tool document would reach Q.100. I am clueless about vb macros and it really takes most of my time copy pasting those. So please lend me your expertise on this one. |
#2
|
||||
|
||||
Your sample file is obviously a sample file and probably doesn't correspond closely with a real life document. Do you want a solution that WILL work with a real life document or just this sample doc?
For instance, are there Questions that use manual numbering rather than automatic paragraph numbers? Are question 'blocks' always separated by empty paragraphs? Are there question blocks with no tools appended on the end?
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
|||
|
|||
Hello Sir, so basically those gibberish stuffs are somewhat kind of question or statements with tool as their idetifier or reference to a question number 1,2,3, etc or Q.1,Q.2,Q.3 etc. But that's exactly the format of the word file. My excel file was not right column A should be 1,2,3 corresponding on the numbering of the word file. Sorry for that one.
|
#4
|
||||
|
||||
OK, as a barebones solution, this macro will put the results into your VBA Immediate Window. You can then copy/paste that output it to Excel and do a global search and replace to remove the (tool: ) bits.
Code:
Sub ToolTime() Dim sNum As String, sTool As String, iPos As Integer Dim aPara As Paragraph, sParaText As String, sData As String For Each aPara In ActiveDocument.Paragraphs If aPara.Range.ListFormat.ListString <> "" Then sNum = aPara.Range.ListFormat.ListString sParaText = aPara.Range.Text iPos = InStr(sParaText, "(tool:") If iPos > 0 Then sTool = Mid(sParaText, iPos) sData = sData & sNum & vbTab & sTool End If Next aPara Debug.Print sData End Sub
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#5
|
|||
|
|||
Thank you, by VBA immediate window you mean a popup window right? I tried running the code but it doesn't do anything. I mean no popup window shows on my vba, despite the correct paratext.
|
#6
|
||||
|
||||
The Immediate Window is a split window in the VBA Editor. You can display it by pressing Ctrl-G or choose View > Immediate Window.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#7
|
|||
|
|||
got to run it! it looks great, but the output seems to mistakenly increment tool. instead of 2,3 it's still at 1. Is this a problem of my document?
1. tool: TAKEN 1. tool: GREEN 1. tool: FILE 4. tool: BRAVO 5. tool: SELECT 6. tool: CRAZE 7. tool: BEAN |
#8
|
||||
|
||||
Yes, it is a problem with your document. There is a VERY REAL reason I told you your demonstration document was not REAL ENOUGH.
It is likely that the paragraphs you see as numbered (2&3) are not 'automatically' numbered. The macro is looking for automatic numbered paragraphs like the sample document you provided. Another possibility is that you have three tool paras under #1 and nothing that matches the exact tool pattern under #2 or #3
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#9
|
|||
|
|||
I see, thank you so much for the help. Would it be possible to have something like if those numbers would be like Q.1, Q.2, Q.3? I remembered we have this type of numberings
|
#10
|
||||
|
||||
It is easy to modify the code but no-one can suggest the modifications without knowing what they are trying to match to. Flawless code needs to be based on realistic sample documents or very simple formatting rules.
Do you have the power to define the Question Numbers via styles? Note that the tools are identified by finding a string "(tool:". The addition of an innocuous space or some other character means tools can easily be missed.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#11
|
|||
|
|||
Honestly, these test papers/documents just get passed to me and I can't tell them to have it formatted correctly. Most of the time my indicators would be these type of question formats. The only thing stays constant is the tool reference.
1. (question) tool: Answer1 2. (question) tool: Answer2 3. (question) tool: Answer3 and so on.... Q.1 (question) tool: Answer1 Q.2 (question) tool: Answer2 Q.3 (question) tool: Answer3 and so on.... |
#12
|
||||
|
||||
If both numbering series are using AUTOMATIC numbers then the code will pick up both patterns.
If the question number series uses manual numbering, the code needs to be adjusted for the particular pattern that identifies 'what constitutes a question'. Also the 'what constitutes a tool' needs a rigid pattern that fits your data sets. The sample you provided was brief and the patterns the code looked for was consistent on the three items the sample contained. Real world documents from many authors are rarely like that. You have a few options: 1. Provide samples which contain all the variations that the code has to identify 2. Modify the input files to match the patterns the code is looking for before running the code 3. Learn how to modify the code yourself - there are plenty of resources available
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#13
|
|||
|
|||
1. Provide samples which contain all the variations that the code has to identify
Here's an actual short file from them with the Q.(number) variation. 2. Modify the input files to match the patterns the code is looking for before running the code Would it be possible if something like I'll be asked if what pattern am I going to use like Q.(number) Q.1, Q.2, etc, or the (number). 1., 2., etc., pattern? Since these are the most commonly used pattern in our files. I'd say 95% of all the files are in these pattern. 3. Learn how to modify the code yourself - there are plenty of resources available I'll defnitely learn to edit these codes in my free time. |
#14
|
||||
|
||||
Wila616
In terms of identifiable patterns, this second sample file is almost the polar opposite of what you posted as your first sample document. You are just wasting my time with samples that bear no resemblance to an actual document. I'm in no hurry to give you a solution that fits this particular example since you obviously don't care about wasting anyone's time. If I get bored over the weekend I might check back on this but its going to be hard to muster the excitement to help you when you won't help yourself.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#15
|
|||
|
|||
Oh. Sorry but that's the actual file from them. Thank you for all the help though I'll try to figure this out with the codes you supplied. Thank you again
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Word & Excel 2010 - Best Options To Auto Insert MySQL & Excel Data? | Hoser | Word | 1 | 03-17-2017 03:47 PM |
Embeding Excel Docs in Word - Receiving Memory Error Message if Excel is open | kdash | Word | 0 | 05-06-2015 09:38 AM |
Populate Word Drop-down list with Excel column then auto fill form fields with Excel data | Faldinio | Word VBA | 7 | 10-19-2014 06:03 AM |
Merge From Excel With Table in Word - Next Record If Excel Column Same? | misscrf | Mail Merge | 2 | 10-15-2014 11:51 PM |
Open Word w Excel & fill Word textboxes w info from Excel fields runtime error 4248 | Joe Patrick | Word VBA | 2 | 01-30-2012 07:23 AM |