vba and vbs file name import

[Epidural]

Dear All,

I have an issue with the following
I have a code that saves a file under a name from one of the cells:

Sub zapisintemp()
'
' zapisintemp Macro
'
Dim Path As String
Dim filename As String

'
   Range("A1:H1").Select
   Range(Selection, Selection.End(xlDown)).Select
   Selection.Copy
   Workbooks.Add
   ActiveSheet.Paste
   Cells.Select
   Cells.EntireColumn.AutoFit
   Range("A1").Select
   Application.CutCopyMode = False
   Path = "M:\timeless\exprecup\inventar\IN_temp\import"
   filename = Range("E1")
   ActiveWorkbook.SaveAs filename:=Path & filename &".xls", FileFormat:=xlNormal

End Sub

And another macro that uses a vbs file that converts the file from xls to PAQ ( I use it to transfer data to SAP):
It’s working with a fixed path.

Sub exceltopaq()
'
' exceltopaq Macro
'
 Dim VBScriptFile As String
 Dim scriptArg As String


    scriptArg ="C:\Users\x\Documents\makro\050319_4P92.xls"  

    VBScriptFile ="C:\Users\x\Documents\makro\PAQ_converter.vbs"   

    Shell "wscript " &Q(VBScriptFile) & " " & Q(scriptArg), vbNormalFocus

End Sub


Private FunctionQ(text As String) As String
    Q = Chr(34) & text & Chr(34)
End Function

And now my question is. How to write the code for the second macro to take the file saved from the first macro?

 

[bobsan42]

There are at least 2 ways to do it, depending on your workflow.
One way is to make the first Sub a function and call it within the second one:

Function zapisintemp() As String
Dim Path As String
Dim filename As String
   Range("A1:H1").Select
   Range(Selection, Selection.End(xlDown)).Select
   Selection.Copy
   Workbooks.Add
   ActiveSheet.Paste
   Cells.Select
   Cells.EntireColumn.AutoFit
   Range("A1").Select
   Application.CutCopyMode = False
   Path = "M:\timeless\exprecup\inventar\IN_temp\import"
   filename = Range("E1")
   ActiveWorkbook.SaveAs filename:=Path & filename &".xls", FileFormat:=xlNormal
   zapisintemp = Path & filename &".xls"
End Function

Sub exceltopaq()
 Dim VBScriptFile As String
 Dim scriptArg As String
    scriptArg =zapisintemp  
    VBScriptFile ="C:\Users\x\Documents\makro\PAQ_converter.vbs"   
    Shell "wscript " &Q(VBScriptFile) & " " & Q(scriptArg), vbNormalFocus
End Sub

The second way is to set a string type variable - the first sub puts the filename into the variable, the second gets it from it.

 

[Epidural]

Works like a charm.

Thank you bobsan42

 

[bobsan42]

You're welcome.
I'm glad it worked.