Tanzil
09-28-2014, 05:00 PM
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
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