Automatic Charts in Excel

  • Thread starter Thread starter fredm54
  • Start date Start date
F

fredm54

Hello !!!

I have an external program that generates data files in CSV format
Column A contains my data and Column E contain time of day values i
24hr:min:sec format (22:12:30) The CSV gets updated every 10 second
and no 2 rows have the same time value. After the CSV is closed I nee
to do the following...

Open a worksheet (excel 2000) with a macro already loaded.
Run the macro which prompts the user to load a CSV file.
Search column E for the time value 22:00:00
If not found then select E1
Search column E for the time value 04:00:00
If not found select the last cell in column E
Create a range between these 2 cells in column E
Now offset -4 columns and create a similar range in column A
Now create a line chart (on a new sheet) with these 2 ranges wit
column E being the Category X Axis labels and column A being the dat
range.
Also the chart title needs to be the name of the CSV file that wa
loaded (without the .CSV extention)

Now for a bonus, can the worksheet be saved with just the chart sheet
I then need to email this to several recipients and all that data migh
tend to make the file quite large (not to mention it will probabl
confuse some!). I use Outlook 2000 and will send this to a persona
distribution list that I have in my address book.

If the chart can't be saved seperately then could we delete all th
other cells (except for the 2 ranges) and save the file with a modifie
name (append a character or something)?

I hope I included enough information and also that someone will se
this as any easy task!

thanks!

Fre
 
Someone could prove me wrong, but you are more likely to get help if
you get going on the task yourself and ask for help when you get stuck.
If this is all new to you, consider hiring someone who will provide a
solution that both works and serves as a learning tool.

You can get XL to generate a lot of code through its macro recorder.
Turn it on (Tools | Macro > Record new macro...), do whatever it is you
want through the GUI, and turn off the recorder. With a few
exceptions, XL will give you starter code, which one can further
optimize / generalize.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
I agree. That's exactly what I did just a few days ago in automating a
workbook that pulled in it's own data and then created various charts
(including positioning and overlapping). Helped me learn a little bit more
about Visual <cough> Basic (it ain't BASIC like what I grew up learning!).

This page was helpful to me in positioning:
http://peltiertech.com/Excel/ChartsHowTo/ResizeAndMoveAChart.html
More good chart stuff on his site here:
http://www.geocities.com/jonpeltier/Excel/Charts/index.html#hdrFormat

Good luck!
--
Toby Erkson
Oregon, USA
Excel 2002 in Windows XP

....
 
Back
Top