[VBScript] Read a text file and use the string value to search for directory

[Tanzil]

I got a bunch of directories from external software program. The name always start from 1 and forwards depending on the number of the cycle in external program. Now, assuming that I have a text file (Test.txt) with one random number that I've calculated. My goal is to change to a directory name with this string value and then plot a scatter diagram for the csv file inside this Folder. I wrote the following vbs script to automatically do this, but excel cannot find the file. Problem here is the parentfolder from Result.csv. How do i set the string value (MyVar) so that it can be used as path? Thank you for the input

 

StrFileName = "C:\Users\tanzil\Desktop\Test\Test.txt"

 

 

Set ObjFso = CreateObject("Scripting.FileSystemObject")

Set ObjFile = ObjFso.OpenTextFile(StrFileName)

MyVar = ObjFile.ReadAll

 

 

Set ObjFso = Nothing

WScript.Echo MyVar

ObjFile.AddFromString MyVar

ObjFile.Close

 

 

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.DisplayAlerts = False

 

 

Source1 = "C:\Users\tanzil\Desktop\Test\"

Set objWorkbook = objExcel.Workbooks.Open(Source1 & MyVar &"\Result.csv") ' =====> Issue here

Set xlmodule = objWorkbook.VBProject.VBComponents.Add(1)

 

 

strCode = _

..........................

..........................

xlmodule.CodeModule.AddFromString strCode

objExcel.Run "Evac_Curve"

Set xlmodule = Nothing

objExcel.Quit