1

I would like some help with a database that I'm working on. The database is a pretty basic interface that runs a number of functions by clicking different command buttons along the side. The commands are reasonably simple but I'm not great with VBS/VBA. Someone else built the database before I started working here!

I've written a Powershell script which filters and sorts data into a more readable list. Because I'm slightly more familiar with it than Visual Basic, I wanted to just keep that script and run it through Access using VB. The script works fine but I'm trying to add it to the database as another clickable 'command button'. The VBS script I have to run the PS script is:

Set objShell = Wscript.CreateObject("Wscript.Shell")
objShell.Run("powershell.exe -noexit P:\TestScripts\runtest.ps1")

Again, this code runs fine, it opens Powershell command line, prompts the user for info and runs the script. When I add it to Access as an 'Event Procedure' (in the property sheet for the command button, I have "On Click" set to "Event Procedure" so I could add the VBS code) and then work through the database, there's no response when I press that command. Do I need to change my VBS script? I'd assume that there wasn't much difference for a Run command between VBS and VBA. The code in Access looks like:

Private Sub Command58_Click()

Set objShell = Wscript.CreateObject("Wscript.Shell")
objShell.Run ("powershell.exe -noexit P:\TestScripts\runtest.ps1")

End Sub

Apologies for the long message, I just wanted to try and give as much info as possible! I'd appreciate any help. There may very well be a simple solution to all this but my lack of experience is definitely holding me back! Thanks.

-J

Improve this question
1

2 Answers 2

Reset to default
2

That's as convoluted as it can be. First you create a PS script, then you create a VB script to call Powershell to run the PS script - and now you want to run VBA to run the VB script to run your PS script.

What you miss is to use C# to run a program that uses automation to open Access to run VBA to call your nested scripts.

Use the Shell command of VBA to call PS and run the PS script or - better - take that little time it can be to convert your PS script to VBA.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Eh... thanks, I guess? I realise it was convoluted but in essence I just wanted to know if I could run a PS script in VBA the same as VBS. The over the top sarcasm is much appreciated. I said clear as day I wasn't very good with the different languages. This forum is here for everyone, from beginners to experts so try easing up a little next time.
Sorry, that was not intended, but you wrote you are only slightly more familiar with PS than VBA, and the PS script just does some filtering and sorting. But VS is not VBA, so using VS to run a PS script just adds yet another layer for no purpose. So why not post your PS script and explain what it does and have it converted to VBA?
@jam1989 "I WANTA USE ALL DA CODEZ!!!" Don't assume because VBScript does it one way that the same way should be used in VBA, use the right tool for the job.
No, I get it now! I didn't realise that it was that easy to run PS scripts in Access. You're both right, I was way overcomplicating it. I also could try and convert my PS code to VBA for sake of ease. For now though, the Shell function is working great so thanks @Gustav for the suggestion. I'd rather try and chip away at learning VBA properly and converting my PS code myself. If I need help with it, I'm sure I'll be back! Thanks again :)
0

The WScript object isn't available under Access (it's provided by the w|cscript.exe script host). So use 

Private Sub Command58_Click()
  Set objShell = CreateObject("Wscript.Shell")
  objShell.Run ("powershell.exe -noexit P:\TestScripts\runtest.ps1")
End Sub

See here.

Improve this answer

2 Comments

Thanks for your help :)
Why use Windows Scripting Host reference when VBA built-in Shell() function will do? With Gustav on this, it's pointless adding an extra layer into VBA to do what VBA can already do without it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.