CODEWITHSUNDEEP
perluser-interfaceexceluser-experiencevba
Slavatron
Slavatron

Reputation: 2358

How can I run a Perl script without using the command line?

I have written a few Perl scripts that allow me to do my job faster. Each of these scripts is run with a command like this:

perl PerlScript.pl InFile > OutFile

Both InFile and OutFile are generally tab-delimited files that the rest of my workplace only sees as Excel file. My coworkers would benefit from using these scripts but they are intimidated by the idea of running programs from a command line. Is there some way to run a Perl script from a more user-friendly interface like a GUI? My ideal solution would allow them to drag an Excel file into a folder and click a button with the mouse which would produce a new Excel file they could drag back to wherever they need it.

I know nothing about Visual Basic other than that it exists but I have an inkling it might be useful to this problem. Is it possible to run Perl scripts within a Visual Basic script? Is it reasonable to think that someone who is afraid of the command line and computer science in general will be able to comfortably run Visual Basic scripts?

NOTE: I use a Mac OS as do most of my coworkers. A Windows-specific solution would be good because we have a few Windows computers dedicated to tasks that, for whatever reason, are easier done on a Windows OS.

Upvotes: 1

Views: 1451

Answers (2)

Axeman
Axeman

Reputation: 29854

You can do this in VBA with the Shell command. This is an example in a Windows environment. I'm not sure how well the commands work in a OSX environment.

Thanks to Onorio Catenacci for some code stolen outright.

Public Const CmdPath = "C:\Windows\system32\cmd.exe"
Public Const PerlExecPath = "C:\path\to\perl\bin\perl.exe"
Public Const ScriptPath = "C:\Temp\script.pl"
Public Const OutputDir = "C:\Temp"

Function FileExists(ByVal FileToTest As String) As Boolean
   FileExists = (Dir(FileToTest) <> "")
End Function

Function GetWorkbookIfLoaded(ByVal FileName As String) As Workbook
   Dim lWbName As String
   lWbName = Dir(FileName)
   If lWbName = "" Then
       lWbName = FileName
   End If
   On Error Resume Next
   Set GetWorkbookIfLoaded = Workbooks(lWbName)
   On Error GoTo 0
End Function

Function DeleteFile(ByVal FileToDelete As String)
   Dim lUserOK As Boolean
   Dim lReturn As Boolean
   Dim lWbName As String
   Dim oBk As Workbook
   
   lUserOK = True
   lWbName = Dir(FileToDelete)
   
   lReturn = (lWbName = "")
   
   If Not lReturn Then
      Set oBk = GetWorkbookIfLoaded(lWbName)
      
      If Not oBk Is Nothing Then
         oBk.Close
         Set oBk = GetWorkbookIfLoaded(lWbName)
         lUserOK = (oBk Is Nothing)
      End If
      
      If lUserOK Then
          SetAttr FileToDelete, vbNormal
          Kill FileToDelete
          lReturn = True
      End If
      
   End If
   DeleteFile = lReturn
   
End Function

Sub CreateNewExcel()
    Dim lProceed    As Boolean
    Dim lRetVal     As Double
    Dim lMsgResult  As VbMsgBoxResult
    Dim lOutputPath As String
    Dim lCommand    As String
    
    lOutputPath = OutputDir & "\output.tab"
    lCommand _
        = CmdPath & " /C " & PerlExecPath & " -e " _
        & Chr(34) & "print qq[${\(scalar localtime)}\t1\t2\t3\t4\n]" _
        & Chr$(34) & " > " & lOutputPath
        
    lProceed = DeleteFile(lOutputPath)
    If Not lProceed Then Exit Sub
    
    DoEvents
    
    lRetVal = Shell(lCommand, vbHide)
    Application.Wait (Now + TimeValue("0:00:01"))
    
    If lRetVal = 0 Or Not FileExists(lOutputPath) Then
        MsgBox ("Failed to run script")
    Else
        Workbooks.Open (lOutputPath)
    End If
       
End Sub

I had to throw in an Application.Wait call because the test for the file existence would sometimes catch the not-yet-deleted file, or not catch the not-fully-created file.

I did find some stuff on executing stuff in an Apple environment elsewhere on StackOverflow.

Upvotes: 0

Mark Setchell
Mark Setchell

Reputation: 207425

This sounds like a perfect application for an OSX Folder Action. Basically, you start the Automator and tell it you want to create a Folder Action and it gives you a split screen where the things you can do are on the left and you develop your script on the right.

Drag the action Run shell script from the left side to the right and select which folder it is to apply to at the top. Then change it from running bash to using the Perl interpreter in /usr/bin/perl and paste your script in.

Then all you need to do is drop a file on the folder and it will run the Perl script on it.

You could share that folder with your Windows users using Samba in System Preferences-> Sharing so they benefit from the power of OS X.

Upvotes: 1

Related Questions