Call vba from bash

Shell Programming and Scripting
1

I am writing a bash function and am having trouble calling a VBA from within it. The line in bold is the path to the VBA. Thank you :).

ngs() {
    call {C:\Users\cmccabe\Desktop\Sanger\NGS_Sanger.xlsx}
   cd 'C:' C:/Users/cmccabe/Desktop/Python27/
    for file in C:/Users/cmccabe/Desktop/Python27/*_sanger.txt
  do
    cat $file >> c:/Users/cmccabe/Desktop/Python27/out.txt
  done
  convert
}
2

For starters, that's an Excel workbook. "Executing" it just means opening in in Excel.

3

That is what is done currently, but what I am trying to do (if possible) is incorporate that VBA in the bash , so it opens automatically if that option is selected. Thank you :).

4

The problem is not running VBA then, it's convincing Excel to open the spreadsheet, run all the vbscript, and quit from the commandline.

5

The stand-alone VBA (NGS_Sanger) when opened asked the user for a file, does several things, exports a text file, and closes. I am trying to call that from a bash menu. If the VBA can be opened from within the bash it will save some steps. Thank you :).

6

That depends on the stand-alone VBA script which we haven't seen. If it takes parameters in a sane manner maybe it can, if it can't it can't.

7

I will post the vba tomorrow. Basically, when the worksheet is opened a prompt asking for new data is displayed. The user loads the file and clicks a button that does a few things and saves a new file. Hopefully, the VB can be called by bash , but we will see. Thank you :).

8

If the VBA is embedded in the excel, it's not exactly standalone.

1 Like
9

That is true, the vba is part of an excel spreadsheet. Thank you :).

10

I suspect it's going to be very difficult to automate.

1 Like
11

Darn excel :frowning: we will see. Thank you :slight_smile:

12

The question is whether it was designed to accept input from anywhere else, or if line 1 is just a hardcoded "create popup box". Given it was designed in Excel, I suspect the latter. Making it do anything else would be a VB question.

13

Since the data is dynamic and changes each the prompt asks the user if this is new data. That is a yes or no and the path to where the data can be found is hardcoded. There might be 1000 files there and the user must select the one to use. then the vba runs and outputa a new file. Thank you :).

14

The question is whether it was designed to accept input from anywhere else, or if line 1 is hardcoded as "create popup box". Given it was designed in Excel, I suspect the latter. Making it do anything else would be a VB question.

Even for a VB question, we can't possibly answer without seeing it.

15

You are correct in that a message box displays when the workbook is opened. The VB is below with the hard-coded path in bold. Can the bash go to the excel file? The user can then run the VB and the output is stored as a text file. Thank you very much :).

VB

 Private Sub Workbook_Open()
Dim sR As String
Dim sFile As String
    Sheets("Sheet1").Activate
    Range("A1").Select
    sR = MsgBox("Clear the sheet for new data?", vbQuestion + vbYesNo + vbDefaultButton2)
    If sR = vbYes Then
        Application.ScreenUpdating = False
        On Error Resume Next
        Application.DisplayAlerts = False
        Sheets("Sheet1").UsedRange.Clear
        ChDrive "N:\path\to\folder"
          ChDir "N:\path\to\folder"
         On Error GoTo ErrHandler:    ' cause an error
        sFile = Application.GetOpenFilename _
            (FileFilter:="Excel Workbooks, *.xlsx", MultiSelect:=False)
        Workbooks.OpenText Filename:=sFile, Origin:=437, StartRow:=1, DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            FieldInfo:=Array(Array(1, 1), Array(2, 1), _
            Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
            Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
            16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), _
            Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array( _
            29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), _
            Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array( _
            42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), _
            Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), Array(53, 1), Array(54, 1), Array( _
            55, 1)), TrailingMinusNumbers:=True
        ActiveWorkbook.Sheets(1).UsedRange.Copy
        ThisWorkbook.Sheets("Sheet1").Range("A1").PasteSpecial
        Application.CutCopyMode = False
        ActiveWorkbook.Close False
        Sheets("Sheet1").Activate
        ActiveSheet.Range("A1").Select
        Application.ScreenUpdating = True
    End If
Exit Sub
ErrHandler:
    ' error handling code
       Application.Quit
    Resume Next
    
End Sub
1 Like
16

You will need cmd.exe probably, to follow Windows file associations, but this can still be done from BASH. Something like cmd /C "start c:\\path\\to\\excel.xmlx" perhaps.

1 Like
17

Thank you very much I will give that a try :).