[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