Getting Files from list and jumbles them

macdadi112 · #1 05-23-2016, 01:36 PM

Hi,

I was wondering of you could help as I'm sure its possible.

I have a list in a file such as sample_list which contains the following:

example1, answer1
example2, answer2
example3, answer3
example4, answer4
example5, answer5
example6, answer6
example7, answer7
example8, answer8
example9, answer9
example10, answer10
e.t.c

What I want to do is to create a macro and when it runs:

1) it lists either the first or second field and places it on the left hand side
2) It then places its corresponding example/answer on the opposite side
3) It repeats this for 10 of the words and then shuffles the words

example:

example8 answer1
example10 answer6
example1 answer8
answer3 example5
example7 answer9
example9 answer7
answer2 answer9
example6 example3
example4 example2
answer5 answer4

Hope this helps.

Thanks

gmayor · #2 05-23-2016, 09:27 PM

Random.org provides a simple way of sorting lists, so with the aid of that web site and a couple of Word macros you can easily achieve the required end result

Code:

Option Explicit
Private pWebAddress As String
#If Win64 Then
    Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, _
                                                                                          ByVal lpOperation As String, ByVal lpFile As String, _
                                                                                          ByVal lpParameters As String, ByVal lpDirectory As String, _
                                                                                          ByVal nShowCmd As Long) As Long
#Else
    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, _
                                                                                  ByVal lpOperation As String, ByVal lpFile As String, _
                                                                                  ByVal lpParameters As String, ByVal lpDirectory As String, _
                                                                                  ByVal nShowCmd As Long) As Long
#End If

Sub Macro1()
Dim dList As New DataObject
Dim orng As Range
    Set orng = ActiveDocument.Range
    orng.Style = "Normal"
    With orng.Find
        Do While .Execute(FindText:=Chr(11), ReplaceWith:=Chr(13))
            orng.Collapse 0
        Loop
    End With
    Set orng = ActiveDocument.Range
    With orng.Find
        Do While .Execute(FindText:=", ", ReplaceWith:=Chr(13))
            orng.Collapse 0
        Loop
    End With
    dList.SetText ActiveDocument.Range.Text
    dList.PutInClipboard
    MsgBox "Now paste the clipboard content to https://www.random.org/lists/." & _
           "Copy the result to the clipboard and run Macro2"
    Call NewShell("https://www.random.org/lists/", 3)
lbl_Exit:
    Set orng = Nothing
    Set dList = Nothing
    Exit Sub
End Sub

Sub Macro2()
Dim oPara As Paragraph
Dim i As Long
    ActiveDocument.Range.Paste
    ActiveDocument.Range.Style = "Normal"
    For i = 1 To ActiveDocument.Paragraphs.Count / 2
        Set oPara = ActiveDocument.Paragraphs(i)
        If LCase(oPara.Range.Characters(1)) = "a" Or _
           LCase(oPara.Range.Characters(1)) = "e" Then
            oPara.Range = Replace(oPara.Range, Chr(13), ", ")
            'or without the comma as in your sample
            'oPara.Range = Replace(oPara.Range, Chr(13), ", ")
        End If
    Next i
lbl_Exit:
    Set oPara = Nothing
    Exit Sub
End Sub

Private Sub NewShell(cmdLine As String, lngWindowHndl As Long)
    ShellExecute lngWindowHndl, "open", cmdLine, "", "", 1
lbl_Exit:
    Exit Sub
End Sub

gmaxey · #3 05-24-2016, 04:29 AM

You may need to add a reference to Microsoft Forms. 2.0 object library in order to use the DataObject.

macdadi112 · #4 05-24-2016, 05:26 AM

Quote:

Originally Posted by gmayor

Random.org provides a simple way of sorting lists, so with the aid of that web site and a couple of Word macros you can easily achieve the required end result

Code:

Option Explicit
Private pWebAddress As String
#If Win64 Then
    Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, _
                                                                                          ByVal lpOperation As String, ByVal lpFile As String, _
                                                                                          ByVal lpParameters As String, ByVal lpDirectory As String, _
                                                                                          ByVal nShowCmd As Long) As Long
#Else
    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, _
                                                                                  ByVal lpOperation As String, ByVal lpFile As String, _
                                                                                  ByVal lpParameters As String, ByVal lpDirectory As String, _
                                                                                  ByVal nShowCmd As Long) As Long
#End If

Sub Macro1()
Dim dList As New DataObject
Dim orng As Range
    Set orng = ActiveDocument.Range
    orng.Style = "Normal"
    With orng.Find
        Do While .Execute(FindText:=Chr(11), ReplaceWith:=Chr(13))
            orng.Collapse 0
        Loop
    End With
    Set orng = ActiveDocument.Range
    With orng.Find
        Do While .Execute(FindText:=", ", ReplaceWith:=Chr(13))
            orng.Collapse 0
        Loop
    End With
    dList.SetText ActiveDocument.Range.Text
    dList.PutInClipboard
    MsgBox "Now paste the clipboard content to https://www.random.org/lists/." & _
           "Copy the result to the clipboard and run Macro2"
    Call NewShell("https://www.random.org/lists/", 3)
lbl_Exit:
    Set orng = Nothing
    Set dList = Nothing
    Exit Sub
End Sub

Sub Macro2()
Dim oPara As Paragraph
Dim i As Long
    ActiveDocument.Range.Paste
    ActiveDocument.Range.Style = "Normal"
    For i = 1 To ActiveDocument.Paragraphs.Count / 2
        Set oPara = ActiveDocument.Paragraphs(i)
        If LCase(oPara.Range.Characters(1)) = "a" Or _
           LCase(oPara.Range.Characters(1)) = "e" Then
            oPara.Range = Replace(oPara.Range, Chr(13), ", ")
            'or without the comma as in your sample
            'oPara.Range = Replace(oPara.Range, Chr(13), ", ")
        End If
    Next i
lbl_Exit:
    Set oPara = Nothing
    Exit Sub
End Sub

Private Sub NewShell(cmdLine As String, lngWindowHndl As Long)
    ShellExecute lngWindowHndl, "open", cmdLine, "", "", 1
lbl_Exit:
    Exit Sub
End Sub

It doesn't seem to work.

Is it not possible that one script will work??

gmayor · #5 05-24-2016, 05:57 AM

What happens when you run Macro1? Did you note Greg's comment?
It works here with the sample you provided.

macdadi112 · #6 05-24-2016, 07:02 AM

I get an Microsoft Visual Basic For Appliocations error:

Compile Error:

Only cpmments may appear after End Sub, End Function or End Property.

gmayor · #7 05-24-2016, 09:40 PM

The code should go in a new module as posted. It sounds like you have added the code to the end of a module that alreadly contains code.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
list of Opened excel files	SDondeti	Word VBA	10	05-10-2018 03:52 PM
Change multiple word Files Recipient List (excel)	timpvf	Mail Merge	0	03-12-2015 11:49 AM
Insert Multiple files - from a list	rpothen	Word VBA	1	08-28-2014 02:35 AM
list folders and files using .vbs file	josianne	Excel Programming	3	08-18-2014 03:03 PM
The powerpoint icon on the task bar hides pptx files in the recent files list	Innovationgame	PowerPoint	0	11-13-2013 09:03 AM