Solved

Need help changing simple VBS script to VBA

Posted on 2013-12-17
3
640 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 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access / SQL Server - ODBC Problems 16 56
Cross checking component service dlls 2 41
GA Ribbon creator 9 59
Missing DLL in Access 2010 11 23
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

734 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