create vbs file and write to it

[smiler44]

I'm struggling with writing to a VBS file I'm creating

This works

 Dim fso As FileSystemObject
Dim stream As TextStream
Dim vary As String ' a variable
 Set fso = New FileSystemObject
Set stream = fso.CreateTextFile("c:\directorypath\test.vbs", True)
 vary = "we made it"
 stream.WriteLine vary

how do I write the code below, below the line .vary = "we made it" in the code above into the vbs file?

it is the two "set" lines and the xl.run line I'm struggling with.

 Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Open("C:\Users\home\documents\planning escalations\book1.xlsm")
xl.Run "'book1.xlsm'!macro1"
wb.Close ' this line I can write
xl.Quit ' this line I can write

if only this can be solved

thank you in advance
smiler44

 

[smiler44]

got it. I had the break down the lines of code and ended up with this

 Dim vary1 As String
Dim vary2 As String
Dim vary3 As String
Dim vary4 As String
Dim vary5 As String
Dim vary6 As String
Dim vary7 As String
Dim vary8 As String
Dim vary9 As String
Dim vary10 As String
Dim vary11 As String
Dim vary12 As String
Dim vary13 As String
Dim vary14 As String
 'Set xl = CreateObject("Excel.Application")
 vary1 = "Set xl"
 vary2 = " ="
 vary3 = " CreateObject("""
 vary4 = "Excel.Application"""
 vary5 = ")"
 
 
 'Set wb = xl.Workbooks.Open("C:\Users\home\documents\planning escalations\book1.xlsm")
 
 vary6 = "set wb"
 vary7 = " ="
 vary8 = " xl.Workbooks.Open("""
 vary9 = "C:\Users\home\documents\planning escalations\book1.xlsm"""
 vary10 = ")"
 
 
 'xl.Run "'book1.xlsm'!macro1"
 vary11 = "xl.run "
 vary12 = """"
 vary13 = "'book1.xlsm'!"
 vary14 = "macro1"""
  
 
stream.WriteLine vary1 & vary2 & vary3 & vary4 & vary5
stream.WriteLine vary6 & vary7 & vary8 & vary9 & vary10
stream.WriteLine vary11 & vary12 & vary13 & vary14

smiler44