tuttiwala
asked on
Convert VBA to VBS
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(msoSortOrderDesce nding) <> 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.Sh eets(1).Na me & "$"
objExcel.ActiveWorkbook.Cl ose
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.Mai lMerge
.OpenDataSource strSource, wdOpenFormatAuto, False, False, True, False, "", "", False, "", "", "Provider=Microsoft.Jet.OL EDB.4.0;Pa ssword=""" ";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.Sav eAs strBasePath & strSaveAs & Mid(strSheet, 21, 10) & "Letter.doc"
objWord.ActiveDocument.Clo se 'Closes the Merged Document
objWord.ActiveDocument.Clo se 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.Fi leSystemOb ject")
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
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(msoSortOrderDesce
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.Sh
objExcel.ActiveWorkbook.Cl
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.Mai
.OpenDataSource strSource, wdOpenFormatAuto, False, False, True, False, "", "", False, "", "", "Provider=Microsoft.Jet.OL
.DataSource.firstrecord = wdDefaultFirstRecord
.DataSource.lastrecord = wdDefaultLastRecord
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute
End With
objWord.ActiveDocument.Sav
objWord.ActiveDocument.Clo
objWord.ActiveDocument.Clo
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.Fi
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.Fi leSystemOb ject")
Set objWord = CreateObject("Word.Applica tion")
...
- 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 ...
- Always use CreateObject("") to create objects
Set fso = CreateObject("Scripting.Fi
Set objWord = CreateObject("Word.Applica
...
- 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 ...
ASKER
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?
Type: Word.Application is not defined
Type: Excel.Application is not defined
Name 'Application' is not declared.
and soo on...
any ideas?
ASKER
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.
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.
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 ...
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 ...
ASKER
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...
but.. it still doesnt see the constants... nor does it like the 'Application.Filesearch' object.
still getting the same errors as above.
hmm...
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
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
ASKER
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
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
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 ...
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 ...
ASKER
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?
we have Visual Studio .Net, so I just plopped the code into the VB .Net Console Application thing.
what should have I done?
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 ...
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 ...
ASKER
i would much rather have the VBS code. appreciate it.
ASKER
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.
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.
ASKER
I will try and see if it works.