Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-09-2020, 04:37 PM
Wila616 Wila616 is offline Word to Excel Windows 10 Word to Excel Office 2019
Novice
Word to Excel
 
Join Date: Sep 2020
Posts: 8
Wila616 is on a distinguished road
Default 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.
Attached Files
File Type: docx tool.docx (13.5 KB, 6 views)
File Type: xlsx tool final.xlsx (8.5 KB, 6 views)
Reply With Quote
  #2  
Old 09-09-2020, 06:14 PM
Guessed's Avatar
Guessed Guessed is offline Word to Excel Windows 10 Word to Excel Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #3  
Old 09-09-2020, 06:29 PM
Wila616 Wila616 is offline Word to Excel Windows 10 Word to Excel Office 2019
Novice
Word to Excel
 
Join Date: Sep 2020
Posts: 8
Wila616 is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 09-09-2020, 07:51 PM
Guessed's Avatar
Guessed Guessed is offline Word to Excel Windows 10 Word to Excel Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #5  
Old 09-09-2020, 08:18 PM
Wila616 Wila616 is offline Word to Excel Windows 10 Word to Excel Office 2019
Novice
Word to Excel
 
Join Date: Sep 2020
Posts: 8
Wila616 is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 09-09-2020, 09:10 PM
Guessed's Avatar
Guessed Guessed is offline Word to Excel Windows 10 Word to Excel Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #7  
Old 09-09-2020, 10:28 PM
Wila616 Wila616 is offline Word to Excel Windows 10 Word to Excel Office 2019
Novice
Word to Excel
 
Join Date: Sep 2020
Posts: 8
Wila616 is on a distinguished road
Default

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
Reply With Quote
  #8  
Old 09-09-2020, 10:33 PM
Guessed's Avatar
Guessed Guessed is offline Word to Excel Windows 10 Word to Excel Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #9  
Old 09-09-2020, 10:41 PM
Wila616 Wila616 is offline Word to Excel Windows 10 Word to Excel Office 2019
Novice
Word to Excel
 
Join Date: Sep 2020
Posts: 8
Wila616 is on a distinguished road
Default

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
Reply With Quote
  #10  
Old 09-09-2020, 10:55 PM
Guessed's Avatar
Guessed Guessed is offline Word to Excel Windows 10 Word to Excel Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #11  
Old 09-09-2020, 11:05 PM
Wila616 Wila616 is offline Word to Excel Windows 10 Word to Excel Office 2019
Novice
Word to Excel
 
Join Date: Sep 2020
Posts: 8
Wila616 is on a distinguished road
Default

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....
Reply With Quote
  #12  
Old 09-09-2020, 11:37 PM
Guessed's Avatar
Guessed Guessed is offline Word to Excel Windows 10 Word to Excel Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #13  
Old 09-10-2020, 12:18 AM
Wila616 Wila616 is offline Word to Excel Windows 10 Word to Excel Office 2019
Novice
Word to Excel
 
Join Date: Sep 2020
Posts: 8
Wila616 is on a distinguished road
Default

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.
Attached Files
File Type: docx Experience.docx (11.6 KB, 7 views)
Reply With Quote
  #14  
Old 09-10-2020, 12:51 AM
Guessed's Avatar
Guessed Guessed is offline Word to Excel Windows 10 Word to Excel Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #15  
Old 09-10-2020, 12:56 AM
Wila616 Wila616 is offline Word to Excel Windows 10 Word to Excel Office 2019
Novice
Word to Excel
 
Join Date: Sep 2020
Posts: 8
Wila616 is on a distinguished road
Default

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
Reply With Quote
Reply

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
Word to Excel Merge From Excel With Table in Word - Next Record If Excel Column Same? misscrf Mail Merge 2 10-15-2014 11:51 PM
Word to Excel 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:50 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft