Scripting / automation gurus

[Big Ig]

Any on here?

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.

[DJLJ]

Originally Posted by Big Ig Any on here? 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.

[biglad]

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:

Justkez.com » VBScript and Excel
Microsoft Excel VBScript Script samples

Drop me a PM if you need any help.

Rich.

[wiiksie]

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).

[discuit]

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

Daylite: The #1 Business Productivity Manager for the Mac

[DJLJ]

Originally Posted by discuit 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 Daylite: The #1 Business Productivity Manager for the Mac

To start something on startup, go to System Preferences ->Accounts->Log in items, it will launch whichever items you put here at log in.

In System Preferences->Energy Saver, set it to start at a certain time.

The other 2 things, looks like you'll need to do with Applescript, which isn't difficult. Applescript and Scripting Resources @ MacScripter is a good place for Applescript related questions.

[discuit]

Cheers mate, i'll look into it, when my brain is not so half cooked after the weekend

[Big Ig]

Originally Posted by wiiksie 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...

[Big Ig]

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.

[Big Ig]

Originally Posted by biglad 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: Justkez.com » VBScript and Excel Microsoft Excel VBScript Script samples Drop me a PM if you need any help. Rich.

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!)

[Big Ig]

Originally Posted by DJLJ I've done this on the mac using applescript, does it have to be done on a PC?

It does I'm afraid, work for a third party for Barclays, so Macs - no-no.

What d'you do that has you relying entirely on Macs then mate?