X-Y Scatter plot using VBA

[Abhishek Tripathi]

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:-

1.) 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).

2.) 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
'**************************************************************
' Problem in this section of code
' I want to assign the range to xaxis and yaxis
' Range here is variable
' How to correct it
Set xaxis = Range(Cells(a, b), Cells(k, b))
Set yaxis = Range(Cells(a, b + 1), Cells(k, b + 1))
Set s = ch.SeriesCollection.NewSeries
With s
.Values = yaxis
.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.
Macros are attached along with for your reference.[/CODE]

 

[p45cal]

Could you supply a version of your New code-1.xlsm file where you've run the Button1_Click() macro only up to but not including your snippet in your last message? Say with several text files having been processed.
Also (though we could probably work it out) can you tell us what the values of Count, a, b, d, k are at this point?

 

[Abhishek Tripathi]

I am attaching the following files;

• Folder containing the .RS (text) files, on which i am working.
• NewCode.xlsm file which i am using for Data extraction. To use this file without error you may have to change the value variable named sFileSaveName = "C:\Users\11\Desktop" & "\" & FileName. You have to change "C:\Users\11\Desktop" path to your desired path where you want to save the excel file containing extracted data.
  
• Book-4.xlsm file which i am using for chart plotting.
• NewCode-1.xlsm file which i am trying to combine to perform both these task in single go.

"count" i have used to count number of files present in the folder, this helps me in running the loop for processing the data, as the number files in the folder may not remain same all the time, files may increase or decrease, by doing this i have not change the loop counter again.

"a,b,c,d" i am using these variables to refer the cells where i am want to place the extracted data. a is used for referring the row value of cell and b,c,d i am using for referring column value. My text files are having two columns to be extracted, i wanted to have a third column in my excel file which consist of value of second column multiplied by 9.807 to refer this third column i am using d.

"k" i am using to count the values in frequency columns, as my number of rows may also change, so my objective was to read number of rows in column named frequency and used the number of rows to set the range value of series for plotting chart. Although in present code i have used fixed value of column to run the code but i will be using a variable to change the value of column to refer column heading "frequency" for each series.

Please help me in integrating the two code, so that i can perform the two task in one go.
Thank you

 

[p45cal]

Of the 3 columns for each file, which 2 do you want to plot (x and y)?
Try:
Set yaxis = Range(Cells(a, b + 2), Cells(k, b + 2))
and:
b = b + 3
and:
k = sh1.Cells(Rows.count, b).End(xlUp).Row

 

[Luke M]

@Abhishek Tripathi

Please remember to include links to cross-posts in the future.
https://www.excelforum.com/excel-programming-vba-macros/1230213-x-y-scatter-plot-using-vba.html

 

[Abhishek Tripathi]

Of the 3 columns for each file, which 2 do you want to plot (x and y)?
Try:
Set yaxis = Range(Cells(a, b + 2), Cells(k, b + 2))
and:
b = b + 3
and:
k = sh1.Cells(Rows.count, b).End(xlUp).Row

Thanks for the solution