Solved

Convert VBA to VBS

Posted on 2003-11-18
14
3,689 Views
Last Modified: 2013-12-03
I really need help here. I have this code in VBA and works like a champ.  But I need it in VBS or any other scripting language so that I can run it as a job. (I prefer VBS or ASP). The code to translate is listed below.  Code listed under the 'cmdGenerateLetters' Subroutine is what needs to run when the script is loaded.  


Private Sub cmdGenerateLetters_Click()
    Dim objWord As Word.Application
    Dim objExcel As Excel.Application
    Dim strBasePath, strSource, strSheet, strSaveAs As String
    strBasePath = "C:\Enrollments\"
    strMainDoc = strBasePath & "Template.doc"
    strSaveAs = "GeneratedFiles\"
    With Application.FileSearch
        .NewSearch
        .LookIn = strBasePath
        .SearchSubFolders = False
        .MatchTextExactly = False
        .FileName = "*.xls"
        If .Execute(msoSortOrderDescending) <> 1 Then
            MsgBox "Please make sure only 1 (ONE) Excel File is under the '" & strBasePath & "' Directory.", vbExclamation
            Exit Sub
        Else
            strSource = .FoundFiles(1)
            Set objExcel = New Excel.Application
            objExcel.Workbooks.Open strSource
            strSheet = objExcel.ActiveWorkbook.Sheets(1).Name & "$"
            objExcel.ActiveWorkbook.Close
            Set objExcel = Nothing
        End If
    End With
    strSQL = "SELECT * FROM `'" & strSheet & "'`"
    Set objWord = New Word.Application
    objWord.Documents.Open strMainDoc
    objWord.Visible = False
    With objWord.ActiveDocument.MailMerge
        .OpenDataSource strSource, wdOpenFormatAuto, False, False, True, False, "", "", False, "", "", "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" & strSource & ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Regist", strSQL, , , wdMergeSubTypeAccess
        .DataSource.firstrecord = wdDefaultFirstRecord
        .DataSource.lastrecord = wdDefaultLastRecord
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        .Execute
    End With
    objWord.ActiveDocument.SaveAs strBasePath & strSaveAs & Mid(strSheet, 21, 10) & "Letter.doc"
    objWord.ActiveDocument.Close                      'Closes the Merged Document
    objWord.ActiveDocument.Close wdDoNotSaveChanges   'Closes the Main Document
    objWord.Quit
    Set objWord = Nothing
    If Not MoveFile(strSource, strBasePath, strBasePath & "Archive\") Then
        MsgBox "The Excel File was not moved. Please manually move file into 'Archive' directory.", vbExclamation
    End If
    MsgBox "New Letters have been successfully generated!", vbExclamation
End Sub

Function MoveFile(file, sfol, dfol) As Boolean
    Dim fso As Object
    Dim blnFileMoved As Boolean
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FileExists(file) Then
        MsgBox file & " does not exist!", vbExclamation, "Source File Missing"
        blnFileMoved = False
    Else
        fso.MoveFile (file), dfol
        blnFileMoved = True
    End If
    MoveFile = blnFileMoved
End Function
0
Comment
Question by:tuttiwala
[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
  • 8
  • 6
14 Comments
 
LVL 7

Accepted Solution

by:
wsteegmans earned 500 total points
ID: 9774731
Why not creating a VB-App ... ?

I think you can just copy/paste the code in a VB-Project ... compile it and you have an EXE-file that you can use for your job ...
0
 

Author Comment

by:tuttiwala
ID: 9774751
I havent created too many VB apps... will the code just transfer over?

I will try and see if it works.
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9774796
Some general changes when transporting it to VBS (if the VB-Option won't work ...)

- Always use CreateObject("") to create objects
      Set fso = CreateObject("Scripting.FileSystemObject")
      Set objWord = CreateObject("Word.Application")
      ...
- Remove all your datatypes (everything starting with AS), because script-languages don't use datatypes.

I think you don't have to change much ... Just rename your first function and call it in the first line of your VBS, something like this:
 
Call GenerateLetters

Sub GenerateLetters
      ...
End Sub

Function MoveFile(file, sfol, dfol) As Boolean
      ...
End Sub

But, to alter your code, I should first do it in VBA (Access) because it can also run VBS-code. It is more easy to handle and debug then in a text-file ...
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:tuttiwala
ID: 9774817
i tried it.. and gave me a bunch of errors... starting at the top is:

Type: Word.Application is not defined
Type: Excel.Application is not defined
Name 'Application' is not declared.

and soo on...

any ideas?
0
 

Author Comment

by:tuttiwala
ID: 9774850
hey.. so i switched thiings to createobject. that seemed to help some...

but... now. most of my errors are with regards to constants... i think i need to include some library files..

Here are the remaining errors that I get. I can't seem to figure out how to include libraries.. (like in access or whatever, you can just make sure u include them in your references or what not... but.. how do i do that here?)

Name 'Application' is not declared
Name 'msoSortOrderDescending' is not declared.
Name 'wdOpenFormatAuto' is not declared.
Name 'wdMergeSubTypeAccess' is not declared.
Name 'wdDefaultFirstRecord' is not declared.
Name 'wdDefaultLastRecord' is not declared.
Name 'wdSendToNewDocument' is not declared.
Name 'wdDoNotSaveChanges' is not declared.
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9774871
Indeed ... you have to REFERENCE the Word and Excel Library ... just like you did in VBA
Menu: Project -> References...

When you reference this libraries, you also don't have to use the CreateObject, but you can stay using your first code ...
0
 

Author Comment

by:tuttiwala
ID: 9774954
hmm.. did it.. and your're right. now i can have the 'as word.application' syntax still in there.

but.. it still doesnt see the constants... nor does it like the 'Application.Filesearch' object.

still getting the same errors as above.

hmm...

0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9775053
What are the references now in your VB-Project ...?

I copied and pasted your code in a new VB-Project and compiled it -> no errors ...

The references in my project are:
- Visual Basic for Applications
- Visual Basic Runtime Objects and Procedures
- Visual Basic Objects and Procedures
- OLE Automation
- Microsoft Excel 10.0 Object Library
- Microsoft Word 10.0 Object Library
0
 

Author Comment

by:tuttiwala
ID: 9780086
Are you serious?

Got These
- OLE Automation
- Microsoft Excel 10.0 Object Library
- Microsoft Word 10.0 Object Library
- Visual Basic for Applications 6.0

Can't Find These
- Visual Basic Runtime Objects and Procedures
- Visual Basic Objects and Procedures
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9780115
Using Visual Basic 6.0 (SP5)?

By default, you have
- VB For App
- VB Runttime Obj and Proc
- VB Obj and Proc
- OLE Automation

Strange ... anyways, I will have a look later, so we can maybe just translate it to VBS ...
0
 

Author Comment

by:tuttiwala
ID: 9780803
i think u might beat me for this one... i am using vb.net

we have Visual Studio .Net, so I just plopped the code into the VB .Net Console Application thing.

what should have I done?
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9780838
Oh, that explains a lot ... :-)

Your VBA-code is almost compatible with VB 6 code.

VB.Net is another story ... VB.Net code is really not compatible any more with the 'old' VB and VBA, because VB.Net is really more object oriënted and stuff like that ... So, to easely convert your code, you must use VB 6, or we have to make a VBS-file ...

Come to you later with the VBS-code, if you want ... or if you have VB 6, try that ...
0
 

Author Comment

by:tuttiwala
ID: 9780878
i would much rather have the VBS code. appreciate it.
0
 

Author Comment

by:tuttiwala
ID: 9781132
Hey steegmans! I GOT IT!!!

I just declared those constansts in my code... and played around with the code.. but i got to work in VB.Net! thanks for all the help.

I am gonna give you the points cause you helped out so much. appreciate it. thanks.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

With most software applications trying to cater to multiple user needs nowadays, the focus is to make them as configurable as possible. For e.g., when creating Silverlight applications which will connect to WCF services, the service end point usuall…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

738 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