Create a chart from a "txt" or a "csv" file
Is is possible to create a chart from an external data source like a text
file or a csv file with VBA and keep the data external? If anyone has any experience with this topic, I would really appreciate some examples. The data I am working with is formatted as such: METER, CHANNEL 12, 8 47, 10 349, 8 TIA... Mark Ivey |
Create a chart from a "txt" or a "csv" file
Chart data can exist in another (closed) workbook, even a CVS, but not in a
text file. If there are no other factors apart from merely keeping the chart & data separate, it would be easier to keep data in an ordinary xls. Manually or programmatically, the csv/xls would need to be open while actually making the chart and linking source data. Thereafter it can remain closed, though you will probably get messages about links. You could link cells in the data file to cells in the chart file. Programmatically that could in theory be done without opening the data file with =['c:\path\fileName.csv]SheetName'!A1 as cell formulas; but why bother, simpler to open the data csv to establish the links then close. If the original data is in a text file probably easiest to copy the data to cells, as source for the chart. Thereafter there are various ways of removing the data from the cells though data would in effect exist thereafter in the chart file. Just a few thoughts Regards, Peter T "Mark Ivey" wrote in message ... Is is possible to create a chart from an external data source like a text file or a csv file with VBA and keep the data external? If anyone has any experience with this topic, I would really appreciate some examples. The data I am working with is formatted as such: METER, CHANNEL 12, 8 47, 10 349, 8 TIA... Mark Ivey |
Create a chart from a "txt" or a "csv" file
Thanks for the reply Peter...
In a nutshell, here is what I am trying to accomplish. I would like to build the chart from this external data (which was generated previously in my code) and then cut and paste the chart as a jpg or something like that (so it will not be dependent on the outside source after creation. Do you have any code samples on how to build a chart from a CSV file? TIA... Mark "Peter T" <peter_t@discussions wrote in message ... Chart data can exist in another (closed) workbook, even a CVS, but not in a text file. If there are no other factors apart from merely keeping the chart & data separate, it would be easier to keep data in an ordinary xls. Manually or programmatically, the csv/xls would need to be open while actually making the chart and linking source data. Thereafter it can remain closed, though you will probably get messages about links. You could link cells in the data file to cells in the chart file. Programmatically that could in theory be done without opening the data file with =['c:\path\fileName.csv]SheetName'!A1 as cell formulas; but why bother, simpler to open the data csv to establish the links then close. If the original data is in a text file probably easiest to copy the data to cells, as source for the chart. Thereafter there are various ways of removing the data from the cells though data would in effect exist thereafter in the chart file. Just a few thoughts Regards, Peter T "Mark Ivey" wrote in message ... Is is possible to create a chart from an external data source like a text file or a csv file with VBA and keep the data external? If anyone has any experience with this topic, I would really appreciate some examples. The data I am working with is formatted as such: METER, CHANNEL 12, 8 47, 10 349, 8 TIA... Mark Ivey |
Create a chart from a "txt" or a "csv" file
Do you have any code samples on how to build a chart from a CSV file?
If the CSV file is open it's be the same as from an ordinary xls Sub MakeChart() Dim cht As Chart Dim sr As Series Dim rngSource 'assumes the csv is open Set rngSource = Workbooks("Test.csv").Worksheets(1).Range("A1:B4") With Range("B2") Set cht = ActiveSheet.ChartObjects.Add(.Left, .Top, 180#, 90#).Chart End With cht.SetSourceData rngSource ' only if the data layout lends itself to above method, ' (there are alternatives to setting the data) ' do whatever else to the chart here cht.CopyPicture Appearance:=xlScreen, _ Format:=xlPicture, _ Size:=xlScreen With cht.Parent ActiveSheet.Cells(.TopLeftCell.Row, _ .BottomRightCell.Column + 1).Select ActiveSheet.Paste End With ' cht.Parent.Delete End Sub The above also includes code to copy and paste the chart as a picture. Might be worth visually checking the chart first and tweaking as necessary, then copy as picture & paste. in another routine. It's also possible to have a normal chart with it's own data not linked to any cells at all. Potentially can be quite a lot of code but with the advantage the chart can be reformatted like a normal chart and, if ever necessary, the data retrieved in case the original data is lost or modified. Of course, simplest of all (to keep the chart totally separate from original data) would be simply to copy the data into the chart file. Regards, Peter T "Mark Ivey" wrote in message ... Thanks for the reply Peter... In a nutshell, here is what I am trying to accomplish. I would like to build the chart from this external data (which was generated previously in my code) and then cut and paste the chart as a jpg or something like that (so it will not be dependent on the outside source after creation. Do you have any code samples on how to build a chart from a CSV file? TIA... Mark "Peter T" <peter_t@discussions wrote in message ... Chart data can exist in another (closed) workbook, even a CVS, but not in a text file. If there are no other factors apart from merely keeping the chart & data separate, it would be easier to keep data in an ordinary xls. Manually or programmatically, the csv/xls would need to be open while actually making the chart and linking source data. Thereafter it can remain closed, though you will probably get messages about links. You could link cells in the data file to cells in the chart file. Programmatically that could in theory be done without opening the data file with =['c:\path\fileName.csv]SheetName'!A1 as cell formulas; but why bother, simpler to open the data csv to establish the links then close. If the original data is in a text file probably easiest to copy the data to cells, as source for the chart. Thereafter there are various ways of removing the data from the cells though data would in effect exist thereafter in the chart file. Just a few thoughts Regards, Peter T "Mark Ivey" wrote in message ... Is is possible to create a chart from an external data source like a text file or a csv file with VBA and keep the data external? If anyone has any experience with this topic, I would really appreciate some examples. The data I am working with is formatted as such: METER, CHANNEL 12, 8 47, 10 349, 8 TIA... Mark Ivey |
Create a chart from a "txt" or a "csv" file
Hi Mark -
It's probably not too complicated, unless your data is much more intricate than you've let on. Record a macro while you open the text file as a new workbook, create a chart from this data, then export the chart to GIF or PNG format (don't use JPG). Or you could copy the chart as a picture and paste it into the sheet. After the chart is created, you can delete the data. If you wanted to get more intricate, you could read the file in VBA, and use the data as arrays to populate the chart, as long as there aren't too many points. But Excel charts work more reliably when the data comes from a worksheet. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Mark Ivey" wrote in message ... Thanks for the reply Peter... In a nutshell, here is what I am trying to accomplish. I would like to build the chart from this external data (which was generated previously in my code) and then cut and paste the chart as a jpg or something like that (so it will not be dependent on the outside source after creation. Do you have any code samples on how to build a chart from a CSV file? TIA... Mark "Peter T" <peter_t@discussions wrote in message ... Chart data can exist in another (closed) workbook, even a CVS, but not in a text file. If there are no other factors apart from merely keeping the chart & data separate, it would be easier to keep data in an ordinary xls. Manually or programmatically, the csv/xls would need to be open while actually making the chart and linking source data. Thereafter it can remain closed, though you will probably get messages about links. You could link cells in the data file to cells in the chart file. Programmatically that could in theory be done without opening the data file with =['c:\path\fileName.csv]SheetName'!A1 as cell formulas; but why bother, simpler to open the data csv to establish the links then close. If the original data is in a text file probably easiest to copy the data to cells, as source for the chart. Thereafter there are various ways of removing the data from the cells though data would in effect exist thereafter in the chart file. Just a few thoughts Regards, Peter T "Mark Ivey" wrote in message ... Is is possible to create a chart from an external data source like a text file or a csv file with VBA and keep the data external? If anyone has any experience with this topic, I would really appreciate some examples. The data I am working with is formatted as such: METER, CHANNEL 12, 8 47, 10 349, 8 TIA... Mark Ivey |
Create a chart from a "txt" or a "csv" file
Peter,
Thank you very much for this example. I have a few more minor touches I need to make to get all the data as I would like. Then I can use this example to get it how I want the final product. Many thanks.... Mark Ivey "Peter T" <peter_t@discussions wrote in message ... Do you have any code samples on how to build a chart from a CSV file? If the CSV file is open it's be the same as from an ordinary xls Sub MakeChart() Dim cht As Chart Dim sr As Series Dim rngSource 'assumes the csv is open Set rngSource = Workbooks("Test.csv").Worksheets(1).Range("A1:B4") With Range("B2") Set cht = ActiveSheet.ChartObjects.Add(.Left, .Top, 180#, 90#).Chart End With cht.SetSourceData rngSource ' only if the data layout lends itself to above method, ' (there are alternatives to setting the data) ' do whatever else to the chart here cht.CopyPicture Appearance:=xlScreen, _ Format:=xlPicture, _ Size:=xlScreen With cht.Parent ActiveSheet.Cells(.TopLeftCell.Row, _ .BottomRightCell.Column + 1).Select ActiveSheet.Paste End With ' cht.Parent.Delete End Sub The above also includes code to copy and paste the chart as a picture. Might be worth visually checking the chart first and tweaking as necessary, then copy as picture & paste. in another routine. It's also possible to have a normal chart with it's own data not linked to any cells at all. Potentially can be quite a lot of code but with the advantage the chart can be reformatted like a normal chart and, if ever necessary, the data retrieved in case the original data is lost or modified. Of course, simplest of all (to keep the chart totally separate from original data) would be simply to copy the data into the chart file. Regards, Peter T "Mark Ivey" wrote in message ... Thanks for the reply Peter... In a nutshell, here is what I am trying to accomplish. I would like to build the chart from this external data (which was generated previously in my code) and then cut and paste the chart as a jpg or something like that (so it will not be dependent on the outside source after creation. Do you have any code samples on how to build a chart from a CSV file? TIA... Mark "Peter T" <peter_t@discussions wrote in message ... Chart data can exist in another (closed) workbook, even a CVS, but not in a text file. If there are no other factors apart from merely keeping the chart & data separate, it would be easier to keep data in an ordinary xls. Manually or programmatically, the csv/xls would need to be open while actually making the chart and linking source data. Thereafter it can remain closed, though you will probably get messages about links. You could link cells in the data file to cells in the chart file. Programmatically that could in theory be done without opening the data file with =['c:\path\fileName.csv]SheetName'!A1 as cell formulas; but why bother, simpler to open the data csv to establish the links then close. If the original data is in a text file probably easiest to copy the data to cells, as source for the chart. Thereafter there are various ways of removing the data from the cells though data would in effect exist thereafter in the chart file. Just a few thoughts Regards, Peter T "Mark Ivey" wrote in message ... Is is possible to create a chart from an external data source like a text file or a csv file with VBA and keep the data external? If anyone has any experience with this topic, I would really appreciate some examples. The data I am working with is formatted as such: METER, CHANNEL 12, 8 47, 10 349, 8 TIA... Mark Ivey |
All times are GMT +1. The time now is 11:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com