Consulting

Page 1 of 3 1 2 3 LastLast
Results 1 to 20 of 59

Thread: Splitting a document into multiple documents

  1. #1
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location

    Splitting a document into multiple documents

    I have 56 documents each of which contains between 5 and 12 "activities" for a grade and unit. I need to split these out into new individual files/documents by lesson. Some lessons contain 2 activities and the remainder have only one activity.

    Within the document each activity is formatted in the following manner:

    ----------------------------------------
    NL_G5_LC_00202_LessonObjectTitle
    Grade 5, Unit 3, Lesson 1
    Activity Title
    Inquiry Flipchart p. 12

    Directed Inquiry

    <snip -- removed 2-3 pp of activity text>



    My Notes
    <page break>
    ------------------------------------------

    I looked at Tinbender's solution for Michelle_S (http://www.vbaexpress.com/forum/showthread.php?t=35900&highlight=split+document), however, I have the same text denoting each break ("My Notes" or the <page break>) instead of separate codes.

    Some other requirements. I need to use the first line of text as the new filename; to that I need to append the grade, unit, and lesson numbers from the second line in the form "G5 U3 L1", so the filename reads: "NL_G5_LC_00202_LessonObjectTitle G5 U3 L1". Finally, the first line needs to be deleted.

    When the lesson object code has a "C" in the 8th position (as above), the inquiry has two activities. When that code is an "I" there is only one activity.

    Is it easier for the VBA to test the code and if a "C" is detected then find the 2nd instance of the "end of activity" to set the range? Or, is there a better way to handle that?

    Let me know if you need more information.

    Thanks in advance!

    Ron
    Windermere, FL

  2. #2
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    Hi, All,

    Well, to help all y'all help me, I recorded a macro and then annotated it so you can see the process that I'm trying to automate.

    [vba]Sub Macro5()
    '
    ' Macro5 Macro
    '
    '
    ChangeFileOpenDirectory _
    "C:\Documents and Settings\mckenzier\Desktop\Inquiry Support\G5\U07\"
    Documents.Open FileName:="G5U07_InquirySupport.doc", ConfirmConversions:= _
    True, ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
    PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
    WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""
    ActiveWindow.ActivePane.View.Zoom.Percentage = 100

    ' The first lesson is a "C", so, I page down until I find the 2nd instance of
    ' My Notes. In wdVBA how do I read the first line and test the 8th character
    ' to see if it's a C or an I?
    Selection.MoveDown Unit:=wdScreen, Count:=15, Extend:=wdExtend
    Selection.Copy
    Selection.Copy
    Documents.Add DocumentType:=wdNewBlankDocument
    Selection.PasteAndFormat (wdPasteDefault)
    ' I go back to the top to get the first line
    Selection.HomeKey Unit:=wdStory
    ' There is a caption that I don't need ( LO name: ) so I highlight and delete it.
    Selection.MoveRight Unit:=wdWord, Count:=3, Extend:=wdExtend
    Selection.Delete Unit:=wdCharacter, Count:=1
    ' Then I highlight and cut the portion I need for the filename.
    Selection.EndKey Unit:=wdLine, Extend:=wdExtend
    Selection.Cut
    ' Since there are two activities, I next page down to the next instance of the
    ' name and delete it.
    Selection.MoveDown Unit:=wdScreen, Count:=9
    Selection.EndKey Unit:=wdLine, Extend:=wdExtend
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.Delete Unit:=wdCharacter, Count:=1
    ' The name I cut I now paste into the File Save dialog and select RTF for the
    ' file type.
    ActiveDocument.SaveAs FileName:= _
    "G5 EC 00216 Activity Title 1 G5 U7 L1.rtf", FileFormat:=wdFormatRTF, _
    LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _
    :="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
    SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
    False
    ' I close the new document and proceed to repeat the process; in this case, the
    ' 2nd lesson is also a "C" type, so, it will look very much like the above.
    ActiveDocument.Close
    Selection.MoveDown Unit:=wdLine, Count:=1
    Selection.HomeKey Unit:=wdLine
    Selection.MoveDown Unit:=wdScreen, Count:=11, Extend:=wdExtend
    Selection.MoveUp Unit:=wdLine, Count:=1, Extend:=wdExtend
    Selection.Copy
    Documents.Add DocumentType:=wdNewBlankDocument
    Selection.PasteAndFormat (wdPasteDefault)
    Selection.MoveUp Unit:=wdParagraph, Count:=2
    Selection.HomeKey Unit:=wdStory
    Selection.MoveRight Unit:=wdWord, Count:=3, Extend:=wdExtend
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.EndKey Unit:=wdLine, Extend:=wdExtend
    Selection.Cut
    Selection.MoveDown Unit:=wdScreen, Count:=6
    Selection.EndKey Unit:=wdLine
    Selection.HomeKey Unit:=wdLine, Extend:=wdExtend
    Selection.Delete Unit:=wdCharacter, Count:=1
    ActiveDocument.SaveAs FileName:= _
    "G5 EC 00217 Activity Title 2 G5 U7 L2.rtf", FileFormat:= _
    wdFormatRTF, LockComments:=False, Password:="", AddToRecentFiles:=True, _
    WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
    SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
    False
    ActiveDocument.Close
    ' The 3rd Lesson is an "I" so there is only one activity to process; note that this
    ' is also the last activity in the file. In VBA, how do I detect the EOF and close
    ' things gracefully?
    Selection.MoveDown Unit:=wdScreen, Count:=1
    Selection.HomeKey Unit:=wdLine
    Selection.MoveDown Unit:=wdScreen, Count:=6, Extend:=wdExtend
    Selection.Copy
    Documents.Add DocumentType:=wdNewBlankDocument
    Selection.PasteAndFormat (wdPasteDefault)
    Selection.HomeKey Unit:=wdStory
    Windows("G5U07_InquirySupport.doc [Compatibility Mode]").Activate
    Selection.HomeKey Unit:=wdStory
    Windows("Document13 [Compatibility Mode]").Activate
    Selection.HomeKey Unit:=wdLine
    Selection.MoveRight Unit:=wdWord, Count:=3, Extend:=wdExtend
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.EndKey Unit:=wdLine, Extend:=wdExtend
    Selection.Cut
    ActiveDocument.SaveAs FileName:="G5_EI_00218_Activity Title 3 G5 U7 L3.rtf", _
    FileFormat:=wdFormatRTF, LockComments:=False, Password:="", _
    AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
    EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
    :=False, SaveAsAOCELetter:=False
    If ActiveWindow.View.SplitSpecial <> wdPaneNone Then
    ActiveWindow.Panes(2).Close
    End If
    If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _
    ActivePane.View.Type = wdOutlineView Then
    ActiveWindow.ActivePane.View.Type = wdPrintView
    End If
    ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
    Selection.EndKey Unit:=wdLine, Extend:=wdExtend
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.Delete Unit:=wdCharacter, Count:=1
    ActiveDocument.Save
    ActiveDocument.Close
    ActiveDocument.Close
    End Sub
    [/vba]

    I'm somewhat adept in Excel VBA but this is my first venture into the jungle of Word VBA.

    Thanks,
    Ron
    Windermere, FL

  3. #3
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    Any recommended texts on Word VBA programming? Other resources, beyond VBAExpress?

    A question I forgot to ask, above.

    ' And, in VBA, how do I drop an anchor (begin of range) and then find the end
    ' of the range by searching for the 'n-th' occurrence of 'My Notes' where
    ' n= 1 or 2 ??


    Thanks,
    Last edited by RonMcK; 02-23-2011 at 02:30 PM. Reason: add query about setting range
    Ron
    Windermere, FL

  4. #4
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    Hi Ron,
    One of my few ventures into Word!
    Can you mock up a small document on which we can run your code and see the output?
    Regards
    Malcolm
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    Malcolm,

    I'll mock up a file, create a macro that works with it, and post those for your use. I have to deal with a conference call, right now.
    Ron
    Windermere, FL

  6. #6
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Sorry, that recorded macro was kind of making my eyes bleed. I think you need to look up working with ranges in Word. There are some concepts in the help files which will help. .MoveEnd/.MoveStart and using InStr may help-- but a lot of times that's a lot less efficient than simply using the Find object.

    My advice (even a mock up seems like it's going to be pretty complicated):

    Start by re-recording that macro and throwing out the idea that you can make decisions based on what you see... the whole Selection.MoveDown Unit:=wdScreen is going to be pretty useless in terms of translating what we're seeing... 15 screens is not something you'd ever program in a macro, as your screen resolution is probably different than mine, as well as your zoom percentage.

    You're really performing a "Find", and then selecting something with the mouse (which is not recorded) and copying it. Except for opening functions or clicking on buttons... using the mouse to select stuff is a no-no when you're recording a macro in Word. That action is often skipped, and the translation is tough to wade through.

    I'd also take breaks when you're recording a long macro like that, so that you (and others) can more easily determine your "functions"

    Record
    Open your document
    Stop record

    Record
    Find the text, select it, copy it
    Stop record

    Then you'll probably be able to, with familiarity in Excel VBA, to start to identify the different objects/concepts in the Word object model vs. the Excel object model. You'll also be able to do a little bit of basic clean up to the recorded macro (especially one of this length) in terms of just doing some With... End With statements.

    But take heart, the Word jungle is no scarier than the Excel jungle... the tigers are just different colors.

    Grin.

  7. #7
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Re-read a couple of the comments/questions in the code.

    1. String functions can help (look up InStr, Mid, Left, Right, Len, etc), but I still think you want to investigate Find and Range objects.

    2. Moving down line by line with the selection object is not something you should generally rely on when recording a macro. Better to hold down the CTRL key when you move down, since you can duplicate moving your "range" by paragraph, but moving by line is much less trustworthy, especially if you are deleting words in the middle of your move down process).

    Hope that helps.

  8. #8
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    Frosty,

    Thanks for the advice and guidance. I'll use that and the mock up I'm creating to see if I can clarify the process and perhaps even get the beginning of a 'real' VBA solution for you, Malc, and the lurkers to work with.

    Thanks, again!
    Ron
    Windermere, FL

  9. #9
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    My two cents.

    jason is right, this needs some clarity of purpose. Tell us what you want to happen. Every step.

    Avoid using View (ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader). Especially with headers. You can fully action in a header without ever viewing it.

    Avoid using Activate. It is rarely, if ever, actually needed..

    REALLY avoid using Selection.

    Try working it out on paper - yes paper - step by step, making no assumptions.

    "The first lesson is a "C", so, I page down until I find the 2nd instance of
    ' My Notes."

    My first lesson is a "C". WE have no idea what that means. What it seems to means is:

    The first paragraph has the letter "C" as character #8.

    Do you require the orginal document to be maintained? By that I mean could you work thusly?

    1. Open document (if not open)
    2. detect My Notes and page break.
    3. extract and delete the chunk from start to My Notes/page break.
    4. save chunk in new file (using your filenaming requirements)

    repeat steps 2 - 4 until there is nothing left.

  10. #10
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    Gerry,

    Thanks for your advice and guidance. I am working on a more cogent explanation, I'll be sure it addresses your questions as well as those of Frosty and Malc. I'm also working on code based on what I'm learning; I appreciate all y'all helping me make it as robust as possible for this task.

    I like your suggestion of cutting from an original document and pasting into the target documents.

    More news as it develops, film at 11.

    I gather that Frosty := Jason since Malcolm is Malcolm?? (One more handle decoded.)

    Thanks,
    Ron
    Windermere, FL

  11. #11
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    Malcolm, Frosty, Fumei, et al,

    I have a program that is almost working, it just doesn't know how to gracefully end processing one file and move on to the next.

    Below is the new version of my program; attached is a zip file containing that program in a Word file, a Word doc that's my design in pseudo-code, and a sample data file with greeked text.

    As I see it, there are 2 presenting problems for which I covet your expertise and advice. Any other guidance on improving the programming will be appreciated, as well. And, yes, I seem to have used Selection a lot. Please explain alternative methods of doing this that rely less heavily on Selection.

    First, the program as written, copies each activity for a lesson to the lesson's file (the target file) without difficulty until we get to the last activity in the source file. The problems involve processing the last activity in the Unit. Recall that each Lesson consists of one or more activities. The challenge is one of how to recognize and process being at the end of the file.

    First problem: In my Find code block, I first Find the next instance of the phrase My Notes and, then, I do Selection.GoToNext (wdGoToPage) prior to doing HomeKey Unit:=wdStory, Extend:=wdExtend to select the text to cut and paste. When this code executes on the last lesson, because there is no trailing manual page break, the GoToNext goes BACK to the last manual page break causing my code to leave the last page of text in the source file. Bad.

    The program then fails at the point it attempts to find Grade, Unit and Lesson numbers in the orphan text. When I add temp patch code to leap over that GoToNext, when I'm on the 6th activity (the last one in the sample file, a temporary patch), the program correctly cuts and pastes the entire last activity.

    This leads to the 2nd ERROR, my code that attempts to deal with recognizing the end of file keeps finding the one remaining paragraph mark, let's me delete it, but when I loop back, it's obviously still there (and has to be, since there are no zero byte documents; thus it's not closing the Unit source file, and not looping for the next unit's source file to process.

    How do I ascertain when the execute.find fails to find my phrase (My Notes). Seems only reasonable that if the last activity is missing that trailer piece that I should still move to end and then grab whatever text is left in the file/document. This is a boundary condition that I don’t want to leave hanging loose.

    So, here, you are. Ask for whatever details you need and don't find.

    Thanks,
    Ron


    [vba]
    Option Explicit
    Sub Split_Activities_Out2()
    Dim MyDir As String
    Dim NewDir As String
    Dim OrigFile As String
    Dim MySrcFile As String
    Dim MyOutFile As String
    Dim MyOldOutFile As String
    Dim Grade As Long
    Dim Unit As Long
    Dim MaxGrade As Long
    Dim MyGrade As Long
    Dim MyUnit As Long
    Dim MaxUnit As Long
    Dim MyLesson As String
    Dim strText As String
    Dim start As Long
    Dim msg As String ' delete after testing is done

    MyOldOutFile = ""
    Grade = 5 ' change to 1 for production
    MaxGrade = 5
    Unit = 3 ' change to 1 for production

    ' Do While Grade <= MaxGrade
    Select Case Grade
    Case 4
    MaxUnit = 11
    Case 5
    MaxUnit = 15
    Case Else
    MaxUnit = 10
    End Select

    MyDir = _
    "C:\Documents and Settings\mckenzier\Desktop\G1-5 NL 2012 Inquiry Support files for DCD\G5"
    ' MyDir = "C:\Users\Ron\Desktop" ' for laptop
    ' Do While Unit <= MaxUnit
    If Unit < 10 Then
    NewDir = MyDir & "\U0" & Unit
    Else
    NewDir = MyDir & "\U" & Unit
    End If


    ' ChangeFileOpenDirectory newDir ' for real environment
    ChangeFileOpenDirectory MyDir & "\Unit-Test\" ' for development purposes

    ' OrigFile = "G" & Grade & "U0" & Unit & "_InquirySupport.doc"
    OrigFile = "G" & Grade & "U0" & Unit & "_InquirySupport for VBAX.doc"

    ' Documents.Open FileName:=OrigFile, ConfirmConversions:= _
    True, ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
    PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
    WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""
    Debug.Print OrigFile
    ' Make a working copy of the file: (this becomes Documents(2).Name)
    MySrcFile = "IS_G" & Grade & "U0" & Unit & "_WorkingCopy.doc"

    ActiveDocument.SaveAs FileName:=MySrcFile, _
    FileFormat:=wdFormatDocument, LockComments:=False, Password:="", _
    AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
    EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
    SaveAsAOCELetter:=False
    Read_Next_Activity:
    Documents(MySrcFile).Activate ' return to working file
    Get_MyOutFile:
    MyOutFile = ActiveDocument.Paragraphs(1).Range.Text
    On Error GoTo 0
    MyOutFile = Trim(Left(MyOutFile, Len(MyOutFile) - 1))
    ' we need the following bcs some lines begin with CrLf-pair
    If MyLesson = 6 Then Stop
    If Len(MyOutFile) = 0 Then
    If MyOutFile = vbCrLf Then ' How do I trap and process reaching
    GoTo End_of_Unit ' the end of the file? <<< PROBLEM
    End If '
    Selection.Delete Unit:=wdCharacter, Count:=1
    GoTo Get_MyOutFile
    End If
    strText = ActiveDocument.Paragraphs(2).Range.Text
    strText = Trim(Left(strText, Len(strText) - 1))
    Debug.Print MyOutFile, strText
    ' MyOutFile = "LO Name: " & MyOutFile ' used to test the following code
    ' Debug.Print MyOutFile
    If InStr(MyOutFile, "LO Name:") > 0 Then
    MyOutFile = Right(MyOutFile, Len(MyOutFile) _
    - (InStr(MyOutFile, ":") + 1))
    End If
    ' MyOutFile = "NL_G5_LC_00202_LessonObjectName"
    ' strText = "Grade 5, Unit 3, Lesson 1"
    ' note: grade = 1..5, unit = 1..15, lesson = 1..6
    '
    MyGrade = Mid(strText, InStr(strText, ",") - 1, 1)
    Debug.Print strText & "*", Len(strText)
    Debug.Print
    ' If MyGrade <> Grade Then (build an error trap)
    MyUnit = Mid(strText, InStr(strText, "Unit") + 5, 2)
    If Len(MyUnit) = 2 And Right(MyUnit, 1) = "," Then
    MyUnit = Left(MyUnit, 1)
    End If
    MyLesson = Mid(strText, InStr(strText, "Lesson") + 7, 1)
    ' And then we put it all back together to name the output file:
    MyOutFile = RTrim(MyOutFile) & " G" & MyGrade & " U" & MyUnit & _
    " L" & MyLesson
    Debug.Print MyOutFile

    If MyLesson = 6 Then Stop

    ' Delete the first line of the activity
    With Selection
    .Find.ClearFormatting
    .HomeKey Unit:=wdStory
    .EndKey Unit:=wdLine, Extend:=wdExtend
    .Delete
    ' Get from beginning of doc to 1st instance of Find("My Notes")
    .HomeKey Unit:=wdStory
    With .Find
    .Text = "My Notes"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    End With
    .Find.Execute
    .EndKey Unit:=wdLine
    ' If MyLesson = 6 Then GoTo Jump_over
    .GoToNext (wdGoToPage) 'will this get me to manual page break? (yes)
    Jump_over:
    .HomeKey Unit:=wdStory, Extend:=wdExtend
    End With
    ' Is there a way to "drop my anchor" at the beginning of the text and extend the
    ' selection down to the end of the line where FIND locates the desired phrase,
    ' highlighting as I go, instead of resorting to the above which is kinda kludgy?

    ' Cut selected text
    Selection.Cut
    If MyOutFile <> MyOldOutFile Then
    If MyOldOutFile <> "" Then
    Documents(MyOldOutFile & ".rtf").Close
    End If
    Documents.Add DocumentType:=wdNewBlankDocument
    ActiveDocument.SaveAs FileName:=MyOutFile & ".rtf", _
    FileFormat:=wdFormatRTF, LockComments:=False, Password:="", _
    AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
    EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData:= _
    False, SaveAsAOCELetter:=False
    End If
    Documents(MyOutFile & ".rtf").Activate

    Selection.PasteAndFormat (wdPasteDefault)
    ' Save new doc with MyOutFile name, leave open
    ActiveDocument.SaveAs FileName:=MyOutFile & ".rtf", _
    FileFormat:=wdFormatRTF, LockComments:=False, Password:="", _
    AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
    EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData:= _
    False, SaveAsAOCELetter:=False
    MyOldOutFile = MyOutFile
    GoTo Read_Next_Activity
    End_of_Unit:
    ' look at Error in Msg Box use for dev education to see Err # for EOF wdFile
    If Err.Number <> 0 Then
    msg = "Error # " & Str(Err.Number) & " was generated by " _
    & Err.Source & Chr(13) & Err.Description
    MsgBox msg, , "Error", Err.HelpFile, Err.HelpContext
    End If
    Debug.Print MyOutFile & ".rtf" & "*"
    Err.Clear
    ' Increment Unit Counter
    Unit = Unit + 1
    Documents(MySrcFile).Close '// close working copy of unit file
    Documents(MyOutFile & ".rtf").Close '// close the last lesson file

    ' Loop '// get next unit
    Grade_Done:
    ' Increment the Grade Counter
    ' Grade = Grade + 1

    ' Loop

    End Sub
    [/vba]
    Attached Files Attached Files
    Ron
    Windermere, FL

  12. #12
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    If MyLesson = 6 Then Stop
    Error here. What is MyLesson?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  13. #13
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    Malcolm,

    Yikes! Mea culpa. Hmm? Well, the DIM should be to a Long and not a String. This line of code was to alert me when the program started on the 6th (last) Lesson where my trouble is. I wonder why it didn't error for me?

    Sorry,
    Ron
    Windermere, FL

  14. #14
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    VBA will attempt to convert the "easy" stuff when it can. So it will evaluate 6 as "6" if it thinks it can avoid causing an error.

  15. #15
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    Perhaps, that explains why it didn't error on me? So, have you had a chance to look at the revised program, see what I learned, and scope out the 2 remaining challenges?



    Thanks,
    Ron
    Windermere, FL

  16. #16
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    I think this is a good example of when to modularize your code. I would first simplify the example document (it doesn't need to have *that* much greek).

    Honestly, I don't have time to provide you with a full solution, but I think I understand enough to give you some helpful pointers.

    It will help, first and foremost, to break this down into workable units. One of the reasons you're feeling overwhelmed is because you've given yourself an overwhelming task (as evidenced by the massively complex single subroutine in your macro document).

    It looks to me like you need to develop the following functions (which I will give brief samples of).

    1. Identify a range of text in your main document which is a single "Lesson" (these seem to be divided by your manual page breaks). Here is a sample of what I mean... when first learning ranges, it will help you to select and see what you get, but you don't need to select them once you're done
    [vba]
    'selecting always helps when working with ranges
    Sub SelectMyLesson()
    Dim rngSearch As Range
    Set rngSearch = ActiveDocument.Content
    'find it and select it
    FindMyLesson(rngSearch).Select
    End Sub
    'returns an expanded range, selecting the entire document if page break not found
    Function FindMyLesson(rngLookWhere As Range) As Range
    Dim lStart As Long
    lStart = rngLookWhere.start
    With rngLookWhere.Find
    .Text = "^m"
    'if we found our page break,reset the beginning of our found range to the original start
    If .Execute = True Then
    rngLookWhere.start = lStart
    Else
    Set rngLookWhere = ActiveDocument.Content
    End If
    End With
    Set FindMyLesson = rngLookWhere
    End Function
    [/vba]

    2. Now that you have a "lesson" range, you need to identify the type of lesson... which is some string manipulation (the 8th character of a particular line, which it looks like you're pretty close on anyway) -- this can probably be broken into multiple functions as well, but you probably just need to start at the beginning of the range you got from #1, and start doing some string manipulation to identify the lesson type.

    Of particular note in this is area is the Split() function, which allows you to automatically split a line like:
    Grade 5, Unit 3, Lesson 1 into a simple array
    myArray = Split("Grade 5, Unit 3, Lesson 1", ",")
    And then your array would be
    MyArray(1) = "Grade 5"
    MyArray(2) = "Unit 3"
    MyArray(3) = "Lesson 1"
    I think you'll know what to do from there...

    3. A function which takes an identified range of text, and saves it into a new document, with all of the information you've gathered (you're already doing the saving stuff, so I won't demo that).

    4. Separate out your functions/subroutines to deal with the different lesson types (but which still use a single "save this document to here with this name" function).

    Hope this helps. Sorry I can't do more at the moment, but like most people... I have to focus the majority of my time on the coding that pays me

    Identifying the individual parts of your lessons by using ranges will help a bunch... as well breaking your functions into very testable units.

    Remember that with ranges you can .MoveStart and .MoveEnd (and a host of other things) as as test individual characters with all the string commands (if you can't use the .Find object, which is really a lot faster and more powerful).

    And write your routines initially so that you always pass a range in... for example, in the above coding sample I did, rather than just pressing F5 in the SelectMyLesson, you can type FindMyLesson(Selection.Range).select in the immediate window, and move your cursor around in the document to see how it works.

    This makes the coding process a lot easier.

    Hope that helps!

    - Jason

  17. #17
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Quick followup-- if you find yourself using a lot of GoTo commands in your code to jump up and down around your subroutine, it generally means you're going to benefit (in some fashion) from creating additional subroutines.

    In this case... it will help others (or, at least, me) help you for free

  18. #18
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    Jason,

    Thank you very much for the thorough tutorial and for not simply crafting a complete solution. Your guidance (again) will help me extend my knowledge and improve my coding style.

    I'll work more on this over the weekend and post "smaller" questions as they arise.

    Again, many thanks for your help.

    Regards,
    Ron
    Windermere, FL

  19. #19
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Just to be really clear, probably should have done something like this in the sample code:
    set rngLesson = FindMyRange(rngSearch)
    rngLesson.Select

    Instead of just selecting the actual function... that would clarify how to proceed.. since you then would need to play around with rngLesson (the results of FindMyRange).

  20. #20
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    Jason,

    Thanks, again.
    Ron
    Windermere, FL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •