Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VBA to VB Script - Code modify

Posted on 2014-01-21
4
Medium Priority
?
608 Views
Last Modified: 2014-01-21
Hello Experts,

First off, I am new to VBScript so please bare with me.  I have a VBA script in MS Word, that I need to have it recoded to work in a VB script.  I was wondering if someone could help me out.  Here are the 4 scripts:

Option Explicit


Sub SetBold()

    Const Mark As String = "BHN-"
    
    Dim Rng As Range
    Dim Rstart As Long, Rend As Long
    Dim Fnd As Boolean
    
    Rstart = 1
    Do
        Rstart = GetStart(Mark, ActiveDocument.Range(Rstart), Rstart)
        If Rstart = 0 Then Exit Do
        
        Set Rng = ActiveDocument.Range(Start:=Rstart + Len(Mark) + 1)
        Rend = GetStart(") -", Rng, Rstart + Len(Mark) + 1)
        Fnd = CBool(Rend)
        If Rend = 0 Then Rend = Rstart + Len(Mark) - 1
        
        Set Rng = ActiveDocument.Range(Start:=Rstart, End:=Rend)
        Rng.Bold = True
        Rstart = Rend + 1
    Loop While Fnd
End Sub
Sub SetRestBold()

    Const Mark As String = "Score:"
    
    Dim Rng As Range
    Dim Rstart As Long, Rend As Long
    Dim Fnd As Boolean
    
    Rstart = 1
    Do
        Rstart = GetStart(Mark, ActiveDocument.Range(Rstart), Rstart)
        If Rstart = 0 Then Exit Do
        
        Set Rng = ActiveDocument.Range(Start:=Rstart + Len(Mark) + 1)
        Rend = GetStart(" - ", Rng, Rstart + Len(Mark) + 1)
        Fnd = CBool(Rend)
        If Rend = 0 Then Rend = Rstart + Len(Mark) - 1
        
        Set Rng = ActiveDocument.Range(Start:=Rstart, End:=Rend)
        Rng.Bold = True
        Rstart = Rend + 1
    Loop While Fnd
End Sub
Sub SetBoldLevel6()

    Const Mark As String = "LEVEL 6 Events:"
    
    Dim Rng As Range
    Dim Rstart As Long, Rend As Long
    Dim Fnd As Boolean
    
    Rstart = 1
    Do
        Rstart = GetStart(Mark, ActiveDocument.Range(Rstart), Rstart)
        If Rstart = 0 Then Exit Do
        
        Set Rng = ActiveDocument.Range(Start:=Rstart + Len(Mark) + 1)
        Rend = GetStart("LEVEL 6 Events:", Rng, Rstart + Len(Mark) + 1)
        Fnd = CBool(Rend)
        If Rend = 0 Then Rend = Rstart + Len(Mark) - 1
        
        Set Rng = ActiveDocument.Range(Start:=Rstart, End:=Rend)
        Rng.Bold = True
        Rstart = Rend + 1
    Loop While Fnd
End Sub
Sub SetBoldLevel5()

    Const Mark As String = "LEVEL 5 Events:"
    
    Dim Rng As Range
    Dim Rstart As Long, Rend As Long
    Dim Fnd As Boolean
    
    Rstart = 1
    Do
        Rstart = GetStart(Mark, ActiveDocument.Range(Rstart), Rstart)
        If Rstart = 0 Then Exit Do
        
        Set Rng = ActiveDocument.Range(Start:=Rstart + Len(Mark) + 1)
        Rend = GetStart("LEVEL 5 Events:", Rng, Rstart + Len(Mark) + 1)
        Fnd = CBool(Rend)
        If Rend = 0 Then Rend = Rstart + Len(Mark) - 1
        
        Set Rng = ActiveDocument.Range(Start:=Rstart, End:=Rend)
        Rng.Bold = True
        Rstart = Rend + 1
    Loop While Fnd
End Sub

Open in new window




Below is the VBScript that I am working with.  Notice I added a section for insert VBA code.

'Language = VBScript
'Script to process, format, and email the Weekly Event Doc


Sub FormatReport

Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Open("C:\Documents and Settings\lspaz\Desktop\Test Report Automation\XXX.docx")
***INSERT VBA CODE
objWord.Quit

Open in new window


Since I am only bolding specific text..  Perhasps I should simplify this and write it in VB Script?
Thanks
0
Comment
Question by:Maliki Hassani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 2000 total points
ID: 39797890
The following shows all working ... of course you will need to pick out the global section everytie you want to run one of the subs but essentially the process is get rid of all typing and reference objects to an instance of word removing all named parameters and using commas where multiple parameters are required to get the right parameter.

Option Explicit

Dim objWord
Dim objDoc

Set objWord = getObject(,"Word.Application")
If objWord Is Nothing Then
	Set objWord = CreateObject("Word.Application")
	Set objDoc = objWord.Documents.Open("C:\Documents and Settings\lspaz\Desktop\Test Report Automation\XXX.docx")
Else
	Set objDoc = objWord.activedocument
End If

	SetBold
	SetRestBold
	SetRestBold
	SetBoldLevel6
	SetBoldLevel5

'objWord.Quit 

Sub SetBold()

    Const Mark = "BHN-"
    
    Dim Rng
    Dim Rstart
    Dim Rend
    Dim Fnd
    
    Rstart = 1
    Do
        Rstart = GetStart(Mark, objdoc.Range(Rstart), Rstart)
        If Rstart = 0 Then Exit Do
        
        Set Rng = objdoc.Range(Rstart + Len(Mark) + 1)
        Rend = GetStart(") -", Rng, Rstart + Len(Mark) + 1)
        Fnd = CBool(Rend)
        If Rend = 0 Then Rend = Rstart + Len(Mark) - 1
        
        Set Rng = objdoc.Range(Rstart, Rend)
        Rng.Bold = True
        Rstart = Rend + 1
    Loop While Fnd
End Sub

Sub SetRestBold()

    Const Mark = "Score:"
    
    Dim Rng
    Dim Rstart
    dim Rend
    Dim Fnd
    
    Rstart = 1
    Do
        Rstart = GetStart(Mark, objdoc.Range(Rstart), Rstart)
        If Rstart = 0 Then Exit Do
        
        Set Rng = objdoc.Range(Rstart + Len(Mark) + 1)
        Rend = GetStart(" - ", Rng, Rstart + Len(Mark) + 1)
        Fnd = CBool(Rend)
        If Rend = 0 Then Rend = Rstart + Len(Mark) - 1
        
        Set Rng = objdoc.Range(Rstart, Rend)
        Rng.Bold = True
        Rstart = Rend + 1
    Loop While Fnd
End Sub
Sub SetBoldLevel6()

    Const Mark = "LEVEL 6 Events:"
    
    Dim Rng
    Dim Rstart
    dim Rend
    Dim Fnd
    
    Rstart = 1
    Do
        Rstart = GetStart(Mark, objdoc.Range(Rstart), Rstart)
        If Rstart = 0 Then Exit Do
        
        Set Rng = objdoc.Range(Rstart + Len(Mark) + 1)
        Rend = GetStart("LEVEL 6 Events:", Rng, Rstart + Len(Mark) + 1)
        Fnd = CBool(Rend)
        If Rend = 0 Then Rend = Rstart + Len(Mark) - 1
        
        Set Rng = objdoc.Range(Rstart, Rend)
        Rng.Bold = True
        Rstart = Rend + 1
    Loop While Fnd
End Sub
Sub SetBoldLevel5()

    Const Mark = "LEVEL 5 Events:"
    
    Dim Rng
    Dim Rstart
    dim Rend
    Dim Fnd
    
    Rstart = 1
    Do
        Rstart = GetStart(Mark, objdoc.Range(Rstart), Rstart)
        If Rstart = 0 Then Exit Do
        
        Set Rng = objdoc.Range(Rstart + Len(Mark) + 1)
        Rend = GetStart("LEVEL 5 Events:", Rng, Rstart + Len(Mark) + 1)
        Fnd = CBool(Rend)
        If Rend = 0 Then Rend = Rstart + Len(Mark) - 1
        
        Set Rng = objdoc.Range(Rstart, Rend)
        Rng.Bold = True
        Rstart = Rend + 1
    Loop While Fnd
End Sub

Private Function GetStart(ByVal Txt, _
                          Rng, _
                          Rstart)
    With Rng.Find
        .Execute Txt,,,,,,True
        If .Found Then GetStart = .Parent.Start
    End With
End Function

Open in new window


Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 39797916
Please note I added the missing getStart function and some code at the start to get an open document to make testing easier but deleting lines 6,7 & 10-12 will of course re-instate your code for the specific document in a new instance of word

Chris
0
 

Author Comment

by:Maliki Hassani
ID: 39797975
That is brilliant!  I was having such a hard time  Thank you so much..  It works, I will let you know if I have any questions though!
0
 

Author Closing Comment

by:Maliki Hassani
ID: 39798472
Thank you!
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question