Question Locked

Calling a script from an excel Command Button

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,

Is it possible to call a vb script from an excel with the use of a command button? If yes, please give a sample code.

Example:
Private Sub CommandButton5_Click()
'Call the script
???
End Sub

Please help :) I'd really really appreciate it.

Regards,
Darling

5 replies

by bizzo

Sub Button1_Click()
Shell ("wscript.exe script.vbs")

End Sub

by darling.marianne.velasco.yao

Hi Bizzo,

Thanks for the help, it works.
I don't mean to puch my luck but I have another question.

The script that I've used see below:
'Create objects
Set oShell = Wscript.CreateObject("Wscript.Shell")
Set oFso = Wscript.CreateObject("Scripting.FileSystemObject")
Set oUserFile = oFso.OpenTextFile(fList,1)

Where flist is actually hard coded.
Const fList = "C:\Documents and Settings\dy51968\Application Data\Microsoft\Internet Explorer\Quick Launch\Users.txt" 'File containing list of {domain\Name id} to check

Is there a way to make this dynamic, that I could get the value of what's written in the Users.txt file from a textbox from my calling program?

Again, thanks for taking the time to answer my question :)

by darling.marianne.velasco.yao

Hi Bizzo,

After I've been able to call the script successfully, an error occured at other part of my program:
sample code
Workbooks(Trim(TextBox1.Value)).Worksheets("VR Questionnaire Export").Range("B1").Value = Trim(TextBox1.Value)

I got a run-time error '9' Subscript out of range. Suddenly it cannot reference the worksheet with the given file name in the textbox1. I use this to open files dynamically so it doesn't have to be hard coded in the program.
Sample Code:
Workbooks.Open FileName:=Trim(TextBox3.Value) + Trim(TextBox2.Value) + ".xls"

Where TextBox3 contains the directory.

Please HELP!

by bizzo

But this is a little beyond my understanding of VBA now.

The sample code you've given looks fine, the only thing I can suggest is, does TextBox1 contain just the filename or path and filename?

This discussion is closed to new replies.