?
Solved

Need help changing simple VBS script to VBA

Posted on 2013-12-17
3
Medium Priority
?
652 Views
Last Modified: 2013-12-17
Hi.

I'm sure this is really simple for those who know, but I'm having some trouble as I'm a week old with VBS and with using VBA to run Shell functions.

I need to rewrite this VBS into VBA format for an Access 2007 application.

Its function: to install an existing font from the application's working folder (not WinDir\Fonts)

O/S: All installed machines (now and future) will be Windows 7 or higher.

Note:
1. All referenced variables, like WSHshell, are declared earlier in the VBA Sub.
2. "Cup" is standard for this app and used throughout.
3. The font already exists in the Working Folder (see objFolder - where the FE lives)

'===========VBS=============
set wshShell = CreateObject ("WScript.Shell")
Cup = wshShell.ExpandEnvironmentStrings("%UserProfile%")

set objShell = CreateObject("Shell.Application")
set objFolder = objShell.Namespace(Cup & "\AppData\Roaming\Cinereo\")
set objFolderItem = objFolder.ParseName("CINEREO.TTF")
objFolderItem.InvokeVerb("Install")

set objShell = nothing
set wshShell = nothing

'=============VBA======== ??????

Thanks to everyone who even takes a look. Your interest is appreciated.
0
Comment
Question by:Cinereo
[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
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 39725044
try this, place the codes in a regular module


Sub runVBSfromVBA()
Dim wshShell As Object, Cup, objShell As Object, objFolder, objFolderItem
Set wshShell = CreateObject("WScript.Shell")
Cup = wshShell.ExpandEnvironmentStrings("%UserProfile%")

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.NameSpace(Cup & "\AppData\Roaming\Cinereo\")
Set objFolderItem = objFolder.ParseName("CINEREO.TTF")
objFolderItem.InvokeVerb ("Install")

Set objShell = Nothing
Set wshShell = Nothing
End Sub


post back the error you will get and specify the line that raised the error


.
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 1000 total points
ID: 39725275
<<
1. All referenced variables, like WSHshell, are declared earlier in the VBA Sub.
2. "Cup" is standard for this app and used throughout.
3. The font already exists in the Working Folder (see objFolder - where the FE lives)
>>

If the variables are already declared in your sub, or in the case of "Cup" as a global variable, and if the font named in this procedure (CINEREO.TTF) is indeed in a folder under your user profile directory -- ie: in a path such as
c:\users\YourUsername\AppData\Roaming\Cinereo\CINEREO.TTF

Then no changes or additions should be needed to the code you originally posted.

VBA uses DIM statements to declare variables and objects; VBS does not use such statements (the type declarations are implied).

Apart from that difference, VBA and VBS are remarkably similar, and porting code from one to the other is a pretty transparent process.
0
 

Author Closing Comment

by:Cinereo
ID: 39725443
The original error message was #91, Object Variable or With Block Variable not set.

Capricorn1's code worked perfectly -  on its own. Which tells me the problem is elsewhere.

So I went scrolling through numerous lines of code (this post is a small section of the entire subroutine). Found where Cup was reassigned its own value PLUS surrounding double quotations. Seems that's used for setting up the shortcuts. But tripped this portion. Just had to move things around.

Issue is now resolved. Capricorn1, thanks so much for the assist.

Mbizup, thanks too for expanding on my understanding of the two types of code. Very helpful.

Cheers,
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

777 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