• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 671
  • Last Modified:

Need help changing simple VBS script to VBA

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
Cinereo
Asked:
Cinereo
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
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
 
mbizupCommented:
<<
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
 
CinereoAuthor Commented:
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now