Abhi86
05-10-2018, 07:56 AM
Hello friends, I am a beginner in VBA coding. Code which i have attempted consist of two parts (a) Data extraction and (b) Plotting of xy-scatter graph from extracted data. My objective of the coding is listed below:-
Data Extraction :- In this part of code i am trying to extract data from text files having .RS extension kept in a folder, into an excel. I have tried to automate the folder pick up which contain the text file, along with creation of excel file which will be having the extracted data. Based on the number of text files in the folder the cells in excel file will get arranged automatically and data will be extracted. (With this portion of code i am having no problem and i am able to perform the desired function).
Plotting of extracted data into xy-scatter graph :- In this portion of code my objective is to automate the plotting of extracted data on a chart (XY-scattersmooth). Say i am having 5 text file in my folder then my program will extract the data from each of the file and import/paste it into excel, so i have now 5 series to be plotted (This number 5 (Text file) is variable it can go from say 1 to 1000). So to paste all the series on same chart i have done looping. I am facing problem in assigning a variable range to .Values and .XValues as my series ranges are not fixed i can't use the "A2:A245" kind of syntax for defining range. Portion of code where i am facing problem is pasted below;
'**************************************************************
' Chart plotting
sh1.Cells(4, d).Select
Set ch = ActiveWorkbook.Charts.Add
Set ch = ch.Location(Where:=xlLocationAsObject, Name:="NRS-Data")
With ch
.ChartType = xlXYScatterSmooth
End With
a = 4
b = 1
For z = 1 To count
k = sh1.Cells(Rows.count, 1).End(xlUp).Row
If k = 1048576 Then
k = 1
End If
'MsgBox (k)
'**************************************************************
' Problem in this section of code
' I want to assign the range to xaxis and yaxis
' I want to have a variable range as data for my series are in different columns and the number of rows is also a variable
' How to correct it
Set xaxis = Range(Cells(a, b), Cells(k, b)) ' I want to set xaxis as range which is variable in nature
Set yaxis = Range(Cells(a, b + 1), Cells(k, b + 1)) ' I want to set yaxis as range which is variable in nature
Set s = ch.SeriesCollection.NewSeries
With s
.Values = yaxis ' Facing error at this line, which may be because xaxis is not getting set with range
.XValues = xaxis
.Name = sh1.Cells(2, b).Value
' set other series properties
End With
b = b + 2
'**************************************************************
Next z
At first instance i had two separate codes for performing the above written objective perfectly, and its working where as when i am trying to combine the two codes to make a single code for performing both the tasks at a single click its showing absurd graph view.
https://www.excelguru.ca/forums/images/smilies/animated/confused.gifhttps://www.excelguru.ca/forums/images/smilies/animated/confused.gif
I am attaching both the "combined code" file and the seprated "code file for plotting the data" for your refernce.
I would appreciate the help of members to solve this issue.
https://www.excelguru.ca/forums/images/smilies/animated/welcome.gif
Thank you
Data Extraction :- In this part of code i am trying to extract data from text files having .RS extension kept in a folder, into an excel. I have tried to automate the folder pick up which contain the text file, along with creation of excel file which will be having the extracted data. Based on the number of text files in the folder the cells in excel file will get arranged automatically and data will be extracted. (With this portion of code i am having no problem and i am able to perform the desired function).
Plotting of extracted data into xy-scatter graph :- In this portion of code my objective is to automate the plotting of extracted data on a chart (XY-scattersmooth). Say i am having 5 text file in my folder then my program will extract the data from each of the file and import/paste it into excel, so i have now 5 series to be plotted (This number 5 (Text file) is variable it can go from say 1 to 1000). So to paste all the series on same chart i have done looping. I am facing problem in assigning a variable range to .Values and .XValues as my series ranges are not fixed i can't use the "A2:A245" kind of syntax for defining range. Portion of code where i am facing problem is pasted below;
'**************************************************************
' Chart plotting
sh1.Cells(4, d).Select
Set ch = ActiveWorkbook.Charts.Add
Set ch = ch.Location(Where:=xlLocationAsObject, Name:="NRS-Data")
With ch
.ChartType = xlXYScatterSmooth
End With
a = 4
b = 1
For z = 1 To count
k = sh1.Cells(Rows.count, 1).End(xlUp).Row
If k = 1048576 Then
k = 1
End If
'MsgBox (k)
'**************************************************************
' Problem in this section of code
' I want to assign the range to xaxis and yaxis
' I want to have a variable range as data for my series are in different columns and the number of rows is also a variable
' How to correct it
Set xaxis = Range(Cells(a, b), Cells(k, b)) ' I want to set xaxis as range which is variable in nature
Set yaxis = Range(Cells(a, b + 1), Cells(k, b + 1)) ' I want to set yaxis as range which is variable in nature
Set s = ch.SeriesCollection.NewSeries
With s
.Values = yaxis ' Facing error at this line, which may be because xaxis is not getting set with range
.XValues = xaxis
.Name = sh1.Cells(2, b).Value
' set other series properties
End With
b = b + 2
'**************************************************************
Next z
At first instance i had two separate codes for performing the above written objective perfectly, and its working where as when i am trying to combine the two codes to make a single code for performing both the tasks at a single click its showing absurd graph view.
https://www.excelguru.ca/forums/images/smilies/animated/confused.gifhttps://www.excelguru.ca/forums/images/smilies/animated/confused.gif
I am attaching both the "combined code" file and the seprated "code file for plotting the data" for your refernce.
I would appreciate the help of members to solve this issue.
https://www.excelguru.ca/forums/images/smilies/animated/welcome.gif
Thank you