Updating an Excel spreadsheet with information from another source

[wotsit]

Hi all.

I'm after a bit of help please as i'm a bit stuck due to a limited knowledge of scripting.

I am a server engineer and i have been asked by my manager to produce a weekly report on server disk space usage. Our monitoring software kindly produces a printable report and it can also export the data into a csv or xml file. The printable report however is not good enough for them. They would like a spreadsheet which contains every server and their disk and for it to be updated weekly with the latest data which then populates a graph showing trends. I work for a very large company with a fairly big datacentre and it will take a large amount of my time for me to go through and update every server's free disk space every week.
I have created a working spreadsheet which automatically updates the graph when new figures are entered in the next weekly column, but i have a problem. I can update the spreadsheet easily by opening the csv file, highlighting the relevant column and pasting it into the spreadsheet. But whilst testing it, it seems the data in the exported files are not always in the same order, so i can not update it this way.

Is there a way for me to automate this with a script of some kind? Here's an example of Spreadsheet:

A B C D E F
1 Server name Description week1 week2 week3 week4

2 server a C:\NTFS 80% 60% 60%
3 server a D:\NTFS 50% 50% 50%
4 server b C:\NTFS 40% 50% 60%
5 server b D:\NTFS 80% 60% 60%
6 server c C:\NTFS 50% 50% 50%
7 server c D:\NTFS 60% 60% 60%
8 server d C:\NTFS 80% 60% 60%
9 server d D:\NTFS 60% 60% 60%

Is it possible to create a script that will open the exported file (eg. space.csv), look for the row that contains both server a AND c:\ntfs and write the value in F2 in the spreadsheet? So the row in space.csv that contains both server a AND d:\ntfs will write the value in F3, both server b AND d:\ntfs in F5 etc.

Is this possible at all??

Thank you,
H

 

[PHV]

Why using VBScript instead of a VBA module in your working spreadsheet ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

Also, you may be able to do some things using native Excel features, without any code.

it seems the data in the exported files are not always in the same order...

How do YOU know that? Headings, type of data, format of data?????

Also, there are better ways of dealing with external data than copy 'n' paste.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CherryPanda]

Why not simply open XML with Excel? If XML labels are descriptive enough - that might be all that you need. Otherwise, XML could be used as a data source in your Excel spreadsheet.