namelesstavern.net

So I'm trying to write a script that would open multiple spreadsheets gather some info from them, and use it to create another spreadsheet. Basically use schedules from other departments to create schedule for my department, and use existing inventory, other department needs and minimum storage size as variables for determining new weeks production needs. This info is spread among 5 different spreadsheets and takes me 4 hours each week to sift through, I want to automate this process, and I'm fairly decent with writing VB programs, but have not dealt with writing programs that deal with read/write to Excell.

Anybody recommend any websites or tutorials for this subject?

sounds more liked you need to shift from excel to Access, or some other real database

Using VB and going directly from excel, I dunno. Export the data a comma or space delimited text file version of a spreadsheet though, and then if data was in regularly located columns or rows, doing something in Java wouldn't be too bad.

-Arlos

Data is always located in same columns and rows i just need to grab from one, use it to compare, than place result into another. Switching into Access is not an issue I already asked for it, but I can only control my file not others, and they like their Excell

it's easy to import excel files into Access tho

I used to do something similar via BrioQuery

import the spreadsheets as .csv and do all manor of SQL magic on them, and spit it back out as csv aka spreadsheet

Yeah. In Java I'd take one of those csv files and import it in, just off the cuff for simplicity, with each value in the row as a string inside an array, all stored inside one big 2-dimensional array. Then just repeat the process for each file, storing each file into its own object. Once that's done, you can do whatever kind of manipulations you want with it, by going to the specific cell locations you want to check, and once you know which column you want, extract it out and store it in a final 2d array. Once done, spit that final 2d array back out as a csv.

That's just an off the cuff hack. I am sure there's a more elegant way of doing it.

-Arlos

It can actually be a bit easier then that, its been a couple years since I wrote anything windows based, but you can get Java to interface directly with an excell spreadsheet without any need for exporting it. This would allow you to simply specify the spreadsheets you intent to use and pull the relevant data from each.

If you know anything about simply java coding, check out http://jexcelapi.sourceforge.net/ Its an API for interfacing with excell.

Could also check this quick example to give you an idea for doing it in .net

http://authors.aspalliance.com/aldotnet ... excel.aspx

Well, it's still the same concept, it's just reading directly from the excel file.

-Arlos

Thanks Gidan, that's what I was looking for, never used Java before but I'm sure i can figure it out.

You can do all of that from within Excel itself so long as the file names/locations don't change from day to day on the network.