[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Need help changing simple VBS script to VBA

Posted on 2013-12-17
3
Medium Priority
?
661 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

649 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