[VBA] Changing to directory with variable that was read from a text file

[Tanzil]

Hi VBAers, 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 single number that I've calculated. This allow me to know which directory I am heading and finally plot a scatter diagram for the csv file inside this Folder. I wrote the following vba script but it throws me error. I dont know what I am doing wrong. Can someone give me a little input? Thank You


Source = "C:\Users\tanzil\Desktop\Test\Test.txt"
Open Source For Input As #1
Input #1, ShortText
Close #1


MyVar = ShortText


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")
Set xlmodule = objWorkbook.VBProject.VBComponents.Add(1)


strCode = _
"Sub Curve()" & vbCr & _
"Dim xaxis" & vbCr & _
"Dim yaxis" & vbCr & _
"Dim c" & vbCr & _
"Dim s" & vbCr & _
"Set xaxis = Range(""$A$1"", Range(""$A$1"").End(xlDown))" & vbCr & _
"Set yaxis = Range(""$B$1"", Range(""$B$1"").End(xlDown))" & vbCr & _
"Set c = ActiveWorkbook.Charts.Add" & vbCr & _
"Set c = c.Location(Where:=xlLocationAsObject, Name:=""Result"")" & vbCr & _
"With c" & vbCr & _
".ChartType = xlXYScatterSmoothNoMarkers" & vbCr & _
"End With" & vbCr & _
"Set s = c.SeriesCollection.NewSeries" & vbCr & _
"With s" & vbCr & _
".Values = yaxis" & vbCr & _
".XValues = xaxis" & vbCr & _
".Format.Line.Weight = 1.5" & vbCr & _
"End With" & vbCr & _
"With c" & vbCr & _
".HasTitle = True" & vbCr & _
".ChartTitle.Font.Size = 12" & vbCr & _
".ChartTitle.Text = ""Curve""" & vbCr & _
".Axes(xlCategory, xlPrimary).HasTitle = True" & vbCr & _
".Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = ""Time [s]""" & vbCr & _
".Axes(xlValue, xlPrimary).HasTitle = True" & vbCr & _
".Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = ""Persons""" & vbCr & _
".Axes(xlValue, xlPrimary).MinimumScale = 0" & vbCr & _
".Axes(xlValue, xlPrimary).MinorUnit = 1" & vbCr & _
".Axes(xlValue, xlPrimary).MaximumScale = 50" & vbCr & _
".Axes(xlValue, xlPrimary).MajorUnit = 10" & vbCr & _
".HasLegend = False" & vbCr & _
"End With" & vbCr & _
"c.Export ""C:\Users\tanzil\Desktop\Test\Curve.png""" & vbCr & _
"End Sub


xlmodule.CodeModule.AddFromString strCode
objExcel.Run "Curve"
Set xlmodule = Nothing


objExcel.Quit

[westconn1]

I wrote the following vba script but it throws me error.

what line?
what error?

as far as i can see xlmodule is a reserved word (xl constant), so change variable name
and you have too many line continuations in your string (strcode), remove some, will make the code less readable, but should then work
i can not test the resulted code module

[Kenneth Hobs]

TIP: Use a string array to store the lines of code. Then us Join(yourarray) in the AddFromString line.

[Tanzil]

Hi Westconn1,

Line: "Open Source For Input As #1"
Error Type: VBScript compilation error: Expected end of statement

[Kenneth Hobs]

Could be one of several issues. See if this these concepts don't help.

Sub test_TXTVal()  
  MsgBox 1 + TXTVal(ActiveWorkbook.Path & "\Counter.txt")
End Sub


Function TXTVal(filePath As String) As Long
  'Expect one numeric long type in the file.
  Dim str As Long, hFile As Integer
  'str = 0
  
  'Create TXT file if it doesn't exist and add 0 to it.
  If Dir(filePath) = "" Then
    hFile = FreeFile
    Open filePath For Input As hFile
    Put #hFile, , str
    Close hFile
    TXTVal = str
    Exit Function
  End If
  
  'Get value from TXT file.
  hFile = FreeFile
  Open filePath For Binary Access Read As #hFile
  Get #hFile, , str
  Close hFile
  TXTVal = str
  
End Function

[westconn1]

Line: "Open Source For Input As #1"

i pasted this part of your code and only had path not found error, which of course would be correct, try changing your variable name