Never really done anything before beyond the odd batch file but have a severe need to automate many parts of my job, due to much of it being repetetive shite and my working 70-80 hours a week partially because of it.
Have just downloaded Powershell and written something to download that day's excel updates from sharepoint, but need to automate opening multiple files at once, running various queries (yes I know it should be databases!) on the data - counts of how many instances of a certain value in a column and sending to a csv/xls, identifying certain defined entries and copying and pasting into another worksheet, etc etc. Not a scooby on how to do this though - VBA seems to only be useful if you're already working within excel (or maybe I should be using this to open said files and manipulate them?) so I'm assuming I should be using powershell or vbscript to open and manipulate the files 'externally'???
Any advice would be tops, and a life-saver - got no fucking life doing said hours, and any hours freed up by working smart, i.e. automation, would be a hell of a boon.
Never really done anything before beyond the odd batch file but have a severe need to automate many parts of my job, due to much of it being repetetive shite and my working 70-80 hours a week partially because of it.
Have just downloaded Powershell and written something to download that day's excel updates from sharepoint, but need to automate opening multiple files at once, running various queries (yes I know it should be databases!) on the data - counts of how many instances of a certain value in a column and sending to a csv/xls, identifying certain defined entries and copying and pasting into another worksheet, etc etc. Not a scooby on how to do this though - VBA seems to only be useful if you're already working within excel (or maybe I should be using this to open said files and manipulate them?) so I'm assuming I should be using powershell or vbscript to open and manipulate the files 'externally'???
Any advice would be tops, and a life-saver - got no fucking life doing said hours, and any hours freed up by working smart, i.e. automation, would be a hell of a boon.
I've done this on the mac using applescript, does it have to be done on a PC? My wife uses Excel extensively, but only Mac based.
The last one I've done for her, gets data out of SAP, manipulates lots of sheets and data and pivot tables and in several files etc and finally sends out an email with the new data.
All run by a cron script, saves her about an hour each morning.
On the Win side, you need to talk to one of the other guys, only done manipulation and linking etc in vb there, not any serious scripting.
I've had a couple beers, so sorry if the following makes little sense.
You should be able to access each Excel spreadsheet via the Excel COM API using powershell, vbs or any other windows based scrpting language - there is quite a lot of documentation out there - but you need to make sure the machine accessing the excel documents has Excel installed.
Have a search on google for vbs and excel and you should find plenty of examples:
Ig, I reckon you will be better of just doing it all from within Excel mate. You'll have much better control over errors and debugging of code (unlike external script debugging which can be a pain in the arse) and you will get intellisense (hinting of methods, events etc).
Just out of interest, is there any way of Automating stuff on a mac for example. When I switch the mac on. I would like it to Start Daylite, Log In to the online database at the office, then sync the online database with my local machine.
This could give me an extra 10 minutes in bed.
The Software we use is called Daylite. By Marketcircle
Just out of interest, is there any way of Automating stuff on a mac for example. When I switch the mac on. I would like it to Start Daylite, Log In to the online database at the office, then sync the online database with my local machine.
This could give me an extra 10 minutes in bed.
The Software we use is called Daylite. By Marketcircle
Ig, I reckon you will be better of just doing it all from within Excel mate. You'll have much better control over errors and debugging of code (unlike external script debugging which can be a pain in the arse) and you will get intellisense (hinting of methods, events etc).
Cheers, Weak Sea. I was under the impression though that one of the main things I want to do (open a whole load of spreadsheets from outside excel and do counts of stuff within them then export said data to another spreadsheet) couldn't be done within excel, but had to be done externally, as I'd have to run a macro individually on each spreadsheet? Or can I write a macro that'll do all that for me? Thinking about it, I guess I could - the Open command's within Excel after all...
Just dipped the toenail of my little toe into this scripting lark, but loving it already. Wish I had the spare time to give it beans - I'm a lazy highfalutin barsteward at the best of times (not in work so much as hating unnecessary effort), so the idea of scripting things (which is fucking cool when you work on it for ages and then hey presto! it works as if by magic) to make my life easier, just dovetails nicely! I had a couple of Eureka! *punches air triumphantly* moments just mapping a drive to sharepoint and writing a script to copy that day's updates to a specified folder, yesterday. Ubergeeky but ace.
I've had a couple beers, so sorry if the following makes little sense.
You should be able to access each Excel spreadsheet via the Excel COM API using powershell, vbs or any other windows based scrpting language - there is quite a lot of documentation out there - but you need to make sure the machine accessing the excel documents has Excel installed.
Have a search on google for vbs and excel and you should find plenty of examples:
Rich = Star, consider thyself repped, cheers for the links mate, will doubtless be on your back for more info, soon as I get some time to get onto it (which doesn't include now as I've just finished a 14 hr 'shift' - this be GF/TNF time baby!)
