Retrieve variable from Excel, for further use in VBScript

[Razzy]

Hi!

I run a macro in Excel by using a bat file and VBScript.

In the macro, I call up folder placement as a variable. I would like to use this variable further in VBScript. The variable in question is "myPath".

See attached code.


1. Run.bat

cscript script.vbs "C:\Users\%username%\Documents\Makro.xlsm"

2. script.vbs

Dim args, objExcel

Set args = wscript.Arguments
Set objExcel = CreateObject("Excel.Application")

objExcel.workbooks.Open args(0)
objExcel.visible = True

objExcel.Run "Macro"

objExcel.Activateworkbook.Save
objExcel.Activateworkbook.Close(0)
objExcel.Quit

I will run more code from her, but I need the variabel myPath from Excel.

3. Makro.xlsm

Sub Macro()

Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
  Application.DisplayAlerts = False

'Pop-velger
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With
 
'If cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
  myExtension = "*.xlx*"

'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
  Do While myFile <> ""
 
   [B] [/B][COLOR=rgb(97, 189, 109)][B]'xlsx, this section is under construction - not the question[/B]
      Workbooks.OpenText Filename:=myPath & myFile, _
        Origin:=65001, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, _
        Comma:=False, Space:=False, Other:=False, 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)), TrailingMinusNumbers:=True
    ActiveWorkbook.SaveAs Filename:=myPath & myFile, _
        FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close[/COLOR]
   
    'Ensure Workbook has closed before moving on to next line of code
      DoEvents

    'Get next file name
      myFile = Dir
  Loop
    
ActiveWorkbook.Save
Application.Quit
    
ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    ActiveWorkbook.Save
    Application.Quit
 
End Sub

Reference: Loop Through All Excel Files In A Given Folder — The Spreadsheet Guru

 

[Legacy 456155]

Here is a most basic example that should provide you with enough info to get it done.

VBS:

Dim xl, MyVariable

Set xl = CreateObject("Excel.Application")
xl.workbooks.Open "C:\Book1.xlsm"
xl.Run "MyMacro", MyVariable
xl.Quit
msgbox MyVariable

VBA:

Public Sub MyMacro(ByRef arg)
    arg = "Hello World! I was passed by reference from Excel."
End Sub

ByRef is the default passing mechanism in VBA, so you don't have to have it, but I usually put it in there to communicate my intentions.

 

[Razzy]

Here is a most basic example that should provide you with enough info to get it done.

VBS:

Dim xl, MyVariable

Set xl = CreateObject("Excel.Application")
xl.workbooks.Open "C:\Book1.xlsm"
xl.Run "MyMacro", MyVariable
xl.Quit
msgbox MyVariable

VBA:

Public Sub MyMacro(ByRef arg)
    arg = "Hello World! I was passed by reference from Excel."
End Sub

ByRef is the default passing mechanism in VBA, so you don't have to have it, but I usually put it in there to communicate my intentions.

Thanks for the reply my friend!

I tried your suggestion and it worked, but the macro somehow disappeared from "Run - popup window" inside Excel when I changed from "Sub" to "Public Sub (ByRef arg)". So I was scared and tested another solution.

I added this at the end of VBA:

Shell "wscript C:\Users\FR\Documents\Script\test.vbs " & myPath & " ", vbNormalFocus

and this in VBS:

wscript.Arguments(0)

Actually this because I had to remove the last "\"

left(wscript.Arguments(0),len(wscript.Arguments(0))-1)

and it worked great!

By adding the shell to the macro, it automatically started up the next script - luxury.

Thx again!