none
Need help with a Macro for renumbering text string and copying to a excel sheet. RRS feed

  • Question

  • Hi,

         I've been working on this thing for to many days nows to be .... In resuming the problem I'm in word 2003, I was ask to do a macro in word to renumber text contaning series of number. This is a technical document and each text string as to start with 001 to ...  ex.:

    EDS_ETA_001
    other text...

    EDS_ETA_002
    other text..

    EDS_COM_001
    other text..

    EDS_ETA_003
    other text..

    EDS_COM_001
    ...

    They have about 50 different text string so I made a input box to they can enter the string of text to renumber.
    I have tried the put all the string into a file to automate the renumber

    It seem so simple I've tryed code from many people, My best result was from countext by James E. Powell 
    I modified it to do a replacement but ....

    And there the copying the text string in a sheet of excel under the last entries.

    Sorry here for my poor english i'm french speaking

    I will appréciate any help given here

    Thanks

    Denis Baller 


    Denis B.
    Wednesday, October 26, 2011 12:36 PM

Answers

  • It's not really clear what you want to do, but the following routine, or a modification of it, that can be used to maintain a log in an Excel workbook might give you some ideas:

    Dim xlapp As Object
    Dim xlsheet As Object
    Dim myarray As Variant
    Dim i As Long, lognum As Long
    Dim subject As String
    subject = InputBox("Enter the subject.")
    On Error Resume Next
    Set xlapp = GetObject(, "Excel.Application")
    If Err Then
        bstartApp = True
        Set xlapp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    Set xlbook = xlapp.Workbooks.Open("C:\Users\Doug\Documents\FormLog.xls")
    Set xlsheet = xlbook.Worksheets(1)
    With xlsheet.Range("A1")
        i = .CurrentRegion.Rows.Count
        If i = 1 Then
            lognum = 1
        Else
            lognum = .Offset(i - 1, 0).Value
            lognum = lognum + 1
        End If
        .Offset(i, 0).Value = lognum
        .Offset(i, 1).Value = subject
    End With
    xlbook.Save
    xlbook.Close
    If bstartApp = True Then
        xlapp.Quit
    End If

    You might want to use a combobox that is loaded with the text strings such as EDS_ETA and EDS_COM from which the user would select the appropriate string and then if you have a sheet in the workbook for each string, you could pass the string to the command

    Set xlsheet = xlbook.Worksheets(1)

    in place of the 1 and in that way, access and maintain a separate sequence of numbers for each string.


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    • Marked as answer by Denis B_ Sunday, October 30, 2011 8:30 PM
    Sunday, October 30, 2011 5:52 AM