Solved

Need help changing simple VBS script to VBA

Posted on 2013-12-17
3
626 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
3 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 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 250 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now