Author Topic: [TUTORIAL] Use Word to make dbr edits  (Read 176 times)

0 Members and 1 Guest are viewing this topic.

Not Yet Rated!

Offline undefind

  • Full Member
  • *
  • Topic Author
  • Posts: 107
  • Country: us
  • Karma: +2/-0
  • Gender: Male
    • View Profile
    • Awards
  • Time Zone: -1
[TUTORIAL] Use Word to make dbr edits
« on: 10 August 2019, 00:40:52 »
The below code will loop through all the files within a selected folder whose name begins with "mi_" and ends with ".dbr". It will then search for all occurrences of the word "Rare" and replace it with "Quest". This is the same as using art manager to change the "ItemClassification" from "Rare" to "Quest".

Warning: If you are unfamiliar with VBA coding you should use this on a test project first. If you have questions on how to get started give me a shout. If enough people need to know I will edit this tutorial for beginners.


Spoiler for Hiden:
Sub LoopAllExcelFilesInFolder()


Dim wd As Document
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

  Application.ScreenUpdating = False
 
Restart:   Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show = 0 Then myPath = ""
       
        myPath = .SelectedItems(1) & "\"
        If myPath = "" Then GoTo NextCode
    End With


NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings
 
  myExtension = "mi_*.dbr*"
  myFile = Dir(myPath & myExtension)

  Do While myFile <> ""
    Set wd = Word.Documents.Open(FileName:=myPath & myFile)
    DoEvents
   
    On Error Resume Next
   
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = "itemClassification,Quest"
        .Replacement.Text = "itemClassification,Rare"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    ActiveDocument.Save
    ActiveWindow.Close

    wd.Close SaveChanges:=True

    DoEvents

    myFile = Dir
   
  Loop
 
GoTo Restart

ResetSettings:

    MsgBox "Task Complete!"

    Application.ScreenUpdating = True

End Sub


If you hit cancel the subroutine will end (fixed). You can modify this code to suit your needs, which I am more than happy to help with if needed.

« Last Edit: 11 August 2019, 00:20:57 by undefind »
My mods and some other mods

https://mega.nz/#F!v5tGhawJ!BeMd_SSWpWZAeG4h3F2YFg

ATTENTION! .arz must be the same name as the name of the mod. i.e. custommaps --> DropSounds + Massbosses + Mis are epic + affixes are common

Database needs to have that long name (or change folder name)

Also on Steam

Not Yet Rated!

Offline undefind

  • Full Member
  • *
  • Topic Author
  • Posts: 107
  • Country: us
  • Karma: +2/-0
  • Gender: Male
    • View Profile
    • Awards
  • Time Zone: -1
Re: [TUTORIAL] Use Word to make dbr edits
« Reply #1 on: 10 August 2019, 17:51:26 »
fixed the code so hitting "Cancel" exits the subroutine.
My mods and some other mods

https://mega.nz/#F!v5tGhawJ!BeMd_SSWpWZAeG4h3F2YFg

ATTENTION! .arz must be the same name as the name of the mod. i.e. custommaps --> DropSounds + Massbosses + Mis are epic + affixes are common

Database needs to have that long name (or change folder name)

Also on Steam

Not Yet Rated!

Offline WNG

  • Lalyia - Ranger Lv. 85
  • Forum Moderator
  • Full Member
  • *
  • Posts: 490
  • Country: ca
  • Karma: +6/-0
  • Gender: Male
  • Jack of All Fails
    • View Profile
    • Awards
  • Time Zone: -4
Re: [TUTORIAL] Use Word to make dbr edits
« Reply #2 on: 10 August 2019, 20:48:04 »
I was actually searching for something like this but I wasn't sure how to proceed. I figured VBA would be the simplest but still I can't quite figure how to make it work.

How hard would it be to do the following :

I have a large volume of .dbrs I want to create copies of. All those .dbrs have a prefix and a suffix. I want to copy all those .dbrs and create a copy with a different prefix in the filename, and would appear in the same folder.

Not Yet Rated!

Offline undefind

  • Full Member
  • *
  • Topic Author
  • Posts: 107
  • Country: us
  • Karma: +2/-0
  • Gender: Male
    • View Profile
    • Awards
  • Time Zone: -1
Re: [TUTORIAL] Use Word to make dbr edits
« Reply #3 on: 10 August 2019, 23:23:53 »
not hard. can you give me a specific example?

or better yet, send me all the files with what you want done to them and i can show you how i would do it.

edit: im actually getting ready to do something similar with affixes right now. getting ready to write the code.
« Last Edit: 10 August 2019, 23:25:39 by undefind »
My mods and some other mods

https://mega.nz/#F!v5tGhawJ!BeMd_SSWpWZAeG4h3F2YFg

ATTENTION! .arz must be the same name as the name of the mod. i.e. custommaps --> DropSounds + Massbosses + Mis are epic + affixes are common

Database needs to have that long name (or change folder name)

Also on Steam

Not Yet Rated!

Offline WNG

  • Lalyia - Ranger Lv. 85
  • Forum Moderator
  • Full Member
  • *
  • Posts: 490
  • Country: ca
  • Karma: +6/-0
  • Gender: Male
  • Jack of All Fails
    • View Profile
    • Awards
  • Time Zone: -4
Re: [TUTORIAL] Use Word to make dbr edits
« Reply #4 on: 11 August 2019, 00:45:47 »
Here's an example :

There would be two parameters : the tag to search for, and the tag to replace it with.

A folder contains those .dbrs :

Code: [Select]
item_common.dbr
item_epic.dbr
item_legendary.dbr

I want to copy those files, and rename their copy by replacing the tag "item" with "armor". As a result, the content of the folder after executing the code would look like :


Code: [Select]
item_common.dbr
item_epic.dbr
item_legendary.dbr
armor_common.dbr
armor_epic.dbr
armor_legendary.dbr

Not Yet Rated!

Offline undefind

  • Full Member
  • *
  • Topic Author
  • Posts: 107
  • Country: us
  • Karma: +2/-0
  • Gender: Male
    • View Profile
    • Awards
  • Time Zone: -1
Re: [TUTORIAL] Use Word to make dbr edits
« Reply #5 on: 11 August 2019, 01:52:21 »
seems easy enough. give me a few minutes.
My mods and some other mods

https://mega.nz/#F!v5tGhawJ!BeMd_SSWpWZAeG4h3F2YFg

ATTENTION! .arz must be the same name as the name of the mod. i.e. custommaps --> DropSounds + Massbosses + Mis are epic + affixes are common

Database needs to have that long name (or change folder name)

Also on Steam

Not Yet Rated!

Offline undefind

  • Full Member
  • *
  • Topic Author
  • Posts: 107
  • Country: us
  • Karma: +2/-0
  • Gender: Male
    • View Profile
    • Awards
  • Time Zone: -1
Re: [TUTORIAL] Use Word to make dbr edits
« Reply #6 on: 11 August 2019, 03:13:58 »
Here's an example :

There would be two parameters : the tag to search for, and the tag to replace it with.

A folder contains those .dbrs :

Code: [Select]
item_common.dbr
item_epic.dbr
item_legendary.dbr

I want to copy those files, and rename their copy by replacing the tag "item" with "armor". As a result, the content of the folder after executing the code would look like :


Code: [Select]
item_common.dbr
item_epic.dbr
item_legendary.dbr
armor_common.dbr
armor_epic.dbr
armor_legendary.dbr

Code: [Select]
Sub CopyandRename()

Dim wd As Document
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

Application.ScreenUpdating = False
 
 
Restart:   Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show = 0 Then myPath = ""
       

        myPath = .SelectedItems(1) & "\"
        If myPath = "" Then GoTo NextCode
    End With


NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings
 
 
 
 
  myExtension = "item_*.dbr"
  myFile = Dir(myPath & myExtension)

  Do While myFile <> ""
    Set wd = Word.Documents.Open(FileName:=myPath & myFile)
    DoEvents
   
    If myFile = "item_common.dbr" Then
        ActiveDocument.SaveAs2 myPath & "armor_common.dbr"
    ElseIf myFile = "item_epic.dbr" Then
        ActiveDocument.SaveAs2 myPath & "armor_epic.dbr"
    ElseIf myFile = "item_legendary.dbr" Then
        ActiveDocument.SaveAs2 myPath & "armor_legendary.dbr"
    End If


    ActiveWindow.Close

    DoEvents

    myFile = Dir
   
  Loop
 
GoTo Restart

ResetSettings:

    MsgBox "Task Complete!"

    Application.ScreenUpdating = True

End Sub

let me know if this works.

sorry it took so long, im drinking and i have 4 kids who wont stfu.
My mods and some other mods

https://mega.nz/#F!v5tGhawJ!BeMd_SSWpWZAeG4h3F2YFg

ATTENTION! .arz must be the same name as the name of the mod. i.e. custommaps --> DropSounds + Massbosses + Mis are epic + affixes are common

Database needs to have that long name (or change folder name)

Also on Steam

Not Yet Rated!

Offline WNG

  • Lalyia - Ranger Lv. 85
  • Forum Moderator
  • Full Member
  • *
  • Posts: 490
  • Country: ca
  • Karma: +6/-0
  • Gender: Male
  • Jack of All Fails
    • View Profile
    • Awards
  • Time Zone: -4
Re: [TUTORIAL] Use Word to make dbr edits
« Reply #7 on: 11 August 2019, 03:41:49 »
I understand a bit of the code, but if I understood it proprely, it might need a bit of a change.

Code: [Select]
myExtension = "item_*.dbr"
  myFile = Dir(myPath & myExtension)

  Do While myFile <> ""
    Set wd = Word.Documents.Open(FileName:=myPath & myFile)
    DoEvents
   
    If myFile = "item_common.dbr" Then
        ActiveDocument.SaveAs2 myPath & "armor_common.dbr"
    ElseIf myFile = "item_epic.dbr" Then
        ActiveDocument.SaveAs2 myPath & "armor_epic.dbr"
    ElseIf myFile = "item_legendary.dbr" Then
        ActiveDocument.SaveAs2 myPath & "armor_legendary.dbr"
    End If

Is there a way for it to replace all characters before the "_" for a set prefix instead of relying on ElseIfs? (since the suffix or number of items can vary)

Not Yet Rated!

Offline undefind

  • Full Member
  • *
  • Topic Author
  • Posts: 107
  • Country: us
  • Karma: +2/-0
  • Gender: Male
    • View Profile
    • Awards
  • Time Zone: -1
Re: [TUTORIAL] Use Word to make dbr edits
« Reply #8 on: 11 August 2019, 03:45:37 »
yeah, was hoping you wouldnt say that.

not sure i have time for that right now. ive been trying to work on something all day and have made little progress. i wanna get this done before i forget what im doing.
My mods and some other mods

https://mega.nz/#F!v5tGhawJ!BeMd_SSWpWZAeG4h3F2YFg

ATTENTION! .arz must be the same name as the name of the mod. i.e. custommaps --> DropSounds + Massbosses + Mis are epic + affixes are common

Database needs to have that long name (or change folder name)

Also on Steam

Not Yet Rated!

Offline WNG

  • Lalyia - Ranger Lv. 85
  • Forum Moderator
  • Full Member
  • *
  • Posts: 490
  • Country: ca
  • Karma: +6/-0
  • Gender: Male
  • Jack of All Fails
    • View Profile
    • Awards
  • Time Zone: -4
Re: [TUTORIAL] Use Word to make dbr edits
« Reply #9 on: 11 August 2019, 03:50:13 »
Uh, no pressure. I wouldn't like to put too much weight on anyone. It's best you resolve your more important matters first.

Not Yet Rated!

Offline undefind

  • Full Member
  • *
  • Topic Author
  • Posts: 107
  • Country: us
  • Karma: +2/-0
  • Gender: Male
    • View Profile
    • Awards
  • Time Zone: -1
Re: [TUTORIAL] Use Word to make dbr edits
« Reply #10 on: 11 August 2019, 03:57:30 »
its not important. im just drunk and everyone wants me to do modding stuff for them.

and the stuff im trying to do is more modding.
My mods and some other mods

https://mega.nz/#F!v5tGhawJ!BeMd_SSWpWZAeG4h3F2YFg

ATTENTION! .arz must be the same name as the name of the mod. i.e. custommaps --> DropSounds + Massbosses + Mis are epic + affixes are common

Database needs to have that long name (or change folder name)

Also on Steam

Not Yet Rated!

Offline undefind

  • Full Member
  • *
  • Topic Author
  • Posts: 107
  • Country: us
  • Karma: +2/-0
  • Gender: Male
    • View Profile
    • Awards
  • Time Zone: -1
Re: [TUTORIAL] Use Word to make dbr edits
« Reply #11 on: 11 August 2019, 20:57:27 »
Code: [Select]
Sub CopyandRename()

Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Dim MyLength As Long
Dim MyCopy As String
Dim MyText As String
Dim message As Variable
Dim fso As Object
Dim MyNewFile As String

Set fso = CreateObject("Scripting.FileSystemObject")

Application.ScreenUpdating = False
 
Result = MsgBox("This program will copy and rename .dbr files that you specify. Press OK to continue or Cancel to leave", vbOKCancel)
If Result = vbCancel Then Exit Sub

Restart:   Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

Result = MsgBox("Pick the folder you want to start in or click Cancel if you are done", vbOKCancel)
If Result = vbCancel Then Exit Sub

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show = 0 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
        If myPath = "" Then GoTo NextCode
    End With

NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

  myExtension = "*.dbr"
  myFile = Dir(myPath & myExtension)
 
    Check1 = InputBox("What is the word that exists in all files you want to check for? i.e. common", , "common")
    If Check1 = vbNullString Or Check1 = "" Then
    Result = MsgBox("Clicking Cancel or leaving the text box empty exits this program")
    Exit Sub
    End If
    Check2 = InputBox("What is the next word that exists in all files you want to check for? i.e. epic", , "epic")
    If Check2 = vbNullString Or Check2 = "" Then
    Result = MsgBox("Clicking Cancel or leaving the text box empty exits this program")
    Exit Sub
    End If
    Check3 = InputBox("What is the next word that exists in all files you want to check for? i.e legendary", , "legendary")
    If Check3 = vbNullString Or Check3 = "" Then
    Result = MsgBox("Clicking Cancel or leaving the text box empty exits this program")
    Exit Sub
    End If
    Check4 = InputBox("What is the word you want to add to the beginning of the file name? i.e armor_. The resulting file will look like ""armor_common.dbr"" be sure to include the underscore or any other characters", , "armor_")
    If Check4 = vbNullString Or Check4 = "" Then
    Result = MsgBox("Clicking Cancel or leaving the text box empty exits this program")
    Exit Sub
    End If

  Do While myFile <> ""
    If myFile Like "*" & Check1 & "*.dbr" Then
        MyLength = InStr(1, myFile, Check1) - 1
        MyText = Left(myFile, MyLength)
        MyCopy = Replace(myFile, MyText, Check4)
        MyNewFile = myPath & MyCopy
        fso.copyfile myPath & myFile, MyNewFile
    ElseIf myFile Like "*" & Check2 & "*.dbr" Then
        MyLength = InStr(1, myFile, Check2) - 1
        MyText = Left(myFile, MyLength)
        MyCopy = Replace(myFile, MyText, Check4)
        MyNewFile = myPath & MyCopy
        fso.copyfile myPath & myFile, MyNewFile
    ElseIf myFile Like "*" & Check3 & "*.dbr" Then
        MyLength = InStr(1, myFile, Check3) - 1
        MyText = Left(myFile, MyLength)
        MyCopy = Replace(myFile, MyText, Check4)
        MyNewFile = myPath & MyCopy
        fso.copyfile myPath & myFile, MyNewFile
    End If
           
    DoEvents

    myFile = Dir
   
  Loop
 
GoTo Restart

ResetSettings:

    MsgBox "Task Complete!"

    Application.ScreenUpdating = True

End Sub


Sorry it took so long.
My mods and some other mods

https://mega.nz/#F!v5tGhawJ!BeMd_SSWpWZAeG4h3F2YFg

ATTENTION! .arz must be the same name as the name of the mod. i.e. custommaps --> DropSounds + Massbosses + Mis are epic + affixes are common

Database needs to have that long name (or change folder name)

Also on Steam

Tags: