Getting Files from list and jumbles them

[macdadi112]

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]

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

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]

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

[macdadi112]

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

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]

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

[macdadi112]

I get an Microsoft Visual Basic For Appliocations error:

Compile Error:

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

[gmayor]

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.