Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Save Macro (Word XP)

    I would like to know how to create a "save" file macro. I have a file extracted from Access and save it as an *.rtf file. I then open the *.rtf file in Word and save it as a .txt file [no other editing is needed----these files are used as data import to a billing system]. So for example, I open in Word c:dataimportjcc_2005_04_12.rtf and save it to a network drive, say J:timeloadjcc_2005_04_12.txt.

    The file name does not change from the .rtf to .txt conversion---just the drive path. But the filenames are different and will be different each day as the filename has the current date embedded in it (i.e., five to ten files a day go through this process--the first three charteracters of the filename are initials and this is the only change in the filenames created daily). The drive paths will not change---they are consistent day-to-day. There is also a file conversion dialog box that pops up that I click ok and use the windows format [default] during the save as process.

    I would like to create a macro that is available in Word as a menu selection, that would save the open .rft file as a .txt in the network directory path specificed and also automatically reply to the file conversion dialog box if needed. The last step of the macro would be to close the .txt document that has just been created in the network directory. I don't need to worry about over writing a file in the network drive as the filenames are unique.

    Can someone help get me started? I am a newbie in MS word VBA....so pls. gear your comments accordingly......thanks.



    I would like to attach this

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save Macro (Word XP)

    How much do you want to automate? Will the user already have opened the .rtf file when the macro is run, or should the macro prompt the user to select an .rtf file, or should the macro process all .rtf files in the source folder?

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Save Macro (Word XP)

    Hans,
    Thanks for the "thinking" questions about my design. Given your questions, I went back into the lounge archives searching and found a post you authored <post#=445489>post 445489</post#>. that would be ideal...except it converts an .rtf file to a .doc file. I have also played around with the Word macro recorder and it appears that I need the file form wdFormat Text and not use the wdFormatDocument in your post. If you review this post, is there anyother changes I need to make to your macro in the post to accomplish the goal in my original post? Also, is the process the same in Word as it is in Excel to add the macro to a menu selection? THANKS.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save Macro (Word XP)

    See if this does what you want:

    Sub ProcessFiles()
    ' Edit as needed, but keep trailing backslashes
    Const strSourcePath = "c:dataimport"
    Const strTargetPath = "J:timeload"
    Dim strFile As String
    Dim strSave As String
    Dim doc As Document
    Dim blnConfirm As Boolean

    On Error GoTo ErrHandler

    blnConfirm = Options.ConfirmConversions
    Options.ConfirmConversions = False

    strFile = Dir(strSourcePath & "*.rtf")
    Do While Not strFile = ""
    Set doc = Documents.Open(FileName:=strSourcePath & strFile, AddToRecentFiles:=False)
    strSave = Replace(strTargetPath & strFile, ".rtf", ".txt")
    doc.SaveAs FileName:=strSave, FileFormat:=wdFormatText
    doc.Close SaveChanges:=wdDoNotSaveChanges
    strFile = Dir
    Loop

    ExitHandler:
    Set doc = Nothing
    Options.ConfirmConversions = blnConfirm
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    To create custom menu or toolbar items, select Tools | Customize...
    Activate the Commands tab.
    Select Macros in the list of Categories.
    Select the macro in the list of Commands, and drag it to the desired position in a menu or on a toolbar.
    Right-click the new item to set its caption etc.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Save Macro (Word XP)

    Hans,
    Worked like a charm. I have thought of one more enhancement. After the first loop runs, I would like to move the *.rtf files to another directory. For example:

    Original Directory: c:timeloadsdatabases
    New Directory: c:timeloadsdatabasesprocessed_rtf_files

    I have tried adding a second loop after the first loop ends, but I keep generating file not found or path error messages. I can't seem to figure out how to use the Name command when looping though the original directory as the filename is different on each pass of the loop. I don't want to re-name the rtf file, just move it to the new path [directory] noted above. I have only used the name and kill commands when the file names were static and I knew what they'd be at the time the macro executed. Can you help me in my development? Maybe I can't do the file move to another directory in the same module as the rtf to txt process...thanks.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save Macro (Word XP)

    You can use code like this:

    ' Only needed if not declared before
    Const strFolder1 = "c:timeloadsdatabases"
    Const strFolder2 = "c:timeloadsdatabasesprocessed_rtf_files"
    Dim strFile As String
    strFile = Dir(strFolder1 & "*.rtf")
    Do While Not strFile = ""
    Name strFolder1 & strFile As strFolder2 & strFile
    strFile = Dir
    Loop

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Save Macro (Word XP)

    Hans,
    Worked like a charm---no surprise there. One final question--I hope--can you help me understand what the line strFile = Dir does? I can't find any help on this. If I assume that there are 5 *.rtf files in the folder, that after the 5th and last *.rtf file has been processed, that the Dir value is null and that is what causes the loop to stop. Is the Dir a value or counter [numeric counter, 5, 4, 3, 2, 1] Sorry to be dense on this question, but I am really trying to "learn" as I go and not just check into the lounge for the "quick fix".....thanks. Jim

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save Macro (Word XP)

    The first time, Dir is called with a wildcard argument. This initializes the loop and returns the first file name that matches the argument. Each subsequent call strFile = Dir finds the next file that matches the argument of the first call; when there are no more matches, it returns an empty string ""; we test for that in Do While Not strFile = "".

  9. #9
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Save Macro (Word XP)

    Hans,
    As always, I am grateful for your guidance and explanation. Again thank you for all the endless hours that you provide for the Lounge to support those like me trying to leverage the Office software and VBA to its fullest extent. Take care.

  10. #10
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Save Macro (Word XP)

    Hans,
    I once again am stifled by VBA. I am trying to rename all the files in a directory again, except this time the extension of the file names is a nonstandard convention. The re-naming logic is the same for all the files in the directory [i.e., these are text files created by a windows based legal software program].

    Original File C:timeload1-041205.68
    renamed file: I:timeloadconverted68-1-041205.txt

    Here is the logic [i.e., my manual processing via windows explorer]. The extension of the original file [68 in my example; the actual number will vary but it is always two digits] is moved to the front of the file name. I then insert a - between the newly moved two digits and the next digit. Lastly, I change the file extension from the number to txt. I have tried a loop to concatenate the new filename, but I can't seem to get it to work. Can I rename files via a Word vba code that have a non standard file extension--a two digit number? I have tried following the logic in your earlier post in this thread but no luck. THANKS.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save Macro (Word XP)

    Try something like this:

    ...
    Dim intPos As Integer
    Dim strNewName As String

    strFile = Dir(strFolder1 & "*.*")
    Do While Not strFile = ""
    ' Find position of . in filename
    intPos = InStrRev(strFile, ".")
    If intPos = 0 Then
    MsgBox "Cannot process " & strFile, vbInformation
    Else
    ' Construct new file name
    strNewName = Mid(strFile, intPos + 1) & "-" & Left(strFile, intPos) & "txt"
    Name strFolder1 & strFile As strFolder2 & strNewName
    End If
    strFile = Dir
    Loop

  12. #12
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Save Macro (Word XP)

    Hans,
    Thanks! It worked like a charm...can you explain how the strFile = Dir(strFolder1 & "*.*") statement works...is this how you get around the nonconventional file extension issue? I have also never seen the intPos =InStrRev (strFile, ".") line before either...thanks for your patience...I really am trying to master this stuff....take care.

  13. #13
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Save Macro (Word XP)

    If you highlight Dir in your code and hit F1, you will see that Dir has many interesting options. Hans is giving it a pattern that matches all file names and extensions (*.*), rather than one specific to a particular file type (e.g., *.TXT or *.DOC).

    InStrRev is a function that locates a character by searching from the END of a text string. (This was added in VB6/Word 2000. All versions had InStr, which searches from left to right.) The function gives you a number, the position of the character you were looking for, which you can use to chop out an interesting part of the string. The code stores this number in intPos. If the number is zero, the character isn't in there. You can learn more about the InStrRev function by highlighting it and hitting F1. It should also have a link to InStr, so you can see how the two differ and when you might want to use one or the other.

  14. #14
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Save Macro (Word XP)

    Thank you...I also didn't know about the F1 option...I am learning everyday thanks to Woody's Lounge...take care.

Posting Permissions

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