Cafe Sports
Asda
Next
GAME
Next
Sponsored Links
  #1 (permalink)  
Old 27th May 2008, 01:59 AM
Big Ig's Avatar
That's not a knife!
 
Join Date: May 2005
Location: Never Never
Posts: 3,312
vCash: 500
Big Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud of
Scripting / automation gurus

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.

Last edited by Big Ig; 27th May 2008 at 02:04 AM.
Reply With Quote
  #2 (permalink)  
Old 27th May 2008, 02:16 AM
I love Stouff
 
Join Date: Dec 2005
Location: Cork, Ireland
Posts: 6,532
vCash: 500
DJLJ has much to be proud ofDJLJ has much to be proud ofDJLJ has much to be proud ofDJLJ has much to be proud ofDJLJ has much to be proud ofDJLJ has much to be proud ofDJLJ has much to be proud ofDJLJ has much to be proud ofDJLJ has much to be proud ofDJLJ has much to be proud ofDJLJ has much to be proud of
Re: Scripting / automation gurus

Originally Posted by Big Ig View Post
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.
Reply With Quote
  #3 (permalink)  
Old 27th May 2008, 02:29 AM
Forumite
 
Join Date: Jul 2005
Location: Right side of the Pennines
Posts: 98
vCash: 500
biglad is likedbiglad is liked
Re: Scripting / automation gurus

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.
Reply With Quote
  #4 (permalink)  
Old 27th May 2008, 08:24 AM
wiiksie's Avatar
Hermano
 
Join Date: Feb 2006
Location: Seasider
Posts: 8,346
vCash: 25
wiiksie rules harder than the Proclaimerswiiksie rules harder than the Proclaimerswiiksie rules harder than the Proclaimerswiiksie rules harder than the Proclaimerswiiksie rules harder than the Proclaimerswiiksie rules harder than the Proclaimerswiiksie rules harder than the Proclaimerswiiksie rules harder than the Proclaimerswiiksie rules harder than the Proclaimerswiiksie rules harder than the Proclaimerswiiksie rules harder than the Proclaimers
Re: Scripting / automation gurus

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).
Reply With Quote
  #5 (permalink)  
Old 27th May 2008, 10:25 AM
discuit's Avatar
Web Slinger
 
Join Date: Aug 2005
Location: L12
Posts: 1,554
vCash: 175
discuit has much to be proud ofdiscuit has much to be proud ofdiscuit has much to be proud ofdiscuit has much to be proud ofdiscuit has much to be proud ofdiscuit has much to be proud ofdiscuit has much to be proud ofdiscuit has much to be proud ofdiscuit has much to be proud ofdiscuit has much to be proud ofdiscuit has much to be proud of
Re: Scripting / automation gurus

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
Reply With Quote
  #6 (permalink)  
Old 27th May 2008, 11:01 AM
I love Stouff
 
Join Date: Dec 2005
Location: Cork, Ireland
Posts: 6,532
vCash: 500
DJLJ has much to be proud ofDJLJ has much to be proud ofDJLJ has much to be proud ofDJLJ has much to be proud ofDJLJ has much to be proud ofDJLJ has much to be proud ofDJLJ has much to be proud ofDJLJ has much to be proud ofDJLJ has much to be proud ofDJLJ has much to be proud ofDJLJ has much to be proud of
Re: Scripting / automation gurus

Originally Posted by discuit View Post
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.
Reply With Quote
  #7 (permalink)  
Old 27th May 2008, 11:43 AM
discuit's Avatar
Web Slinger
 
Join Date: Aug 2005
Location: L12
Posts: 1,554
vCash: 175
discuit has much to be proud ofdiscuit has much to be proud ofdiscuit has much to be proud ofdiscuit has much to be proud ofdiscuit has much to be proud ofdiscuit has much to be proud ofdiscuit has much to be proud ofdiscuit has much to be proud ofdiscuit has much to be proud ofdiscuit has much to be proud ofdiscuit has much to be proud of
Re: Scripting / automation gurus

Cheers mate, i'll look into it, when my brain is not so half cooked after the weekend
Reply With Quote
  #8 (permalink)  
Old 27th May 2008, 11:33 PM
Big Ig's Avatar
That's not a knife!
 
Join Date: May 2005
Location: Never Never
Posts: 3,312
vCash: 500
Big Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud of
Re: Scripting / automation gurus

Originally Posted by wiiksie View Post
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...
Reply With Quote
  #9 (permalink)  
Old 27th May 2008, 11:37 PM
Big Ig's Avatar
That's not a knife!
 
Join Date: May 2005
Location: Never Never
Posts: 3,312
vCash: 500
Big Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud of
Re: Scripting / automation gurus

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.
Reply With Quote
  #10 (permalink)  
Old 27th May 2008, 11:42 PM
Big Ig's Avatar
That's not a knife!
 
Join Date: May 2005
Location: Never Never
Posts: 3,312
vCash: 500
Big Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud of
Re: Scripting / automation gurus

Originally Posted by biglad View Post
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!)
Reply With Quote
  #11 (permalink)  
Old 27th May 2008, 11:44 PM
Big Ig's Avatar
That's not a knife!
 
Join Date: May 2005
Location: Never Never
Posts: 3,312
vCash: 500
Big Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud ofBig Ig has much to be proud of
Re: Scripting / automation gurus

Originally Posted by DJLJ View Post
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?
Reply With Quote
Sponsored Links
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
FAO Betting Guru's 1892-LFCWasBorn FF - Football Forum 16 5th May 2006 01:46 PM



All times are GMT +1. The time now is 10:21 PM.
Powered by vBulletin® Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
SEO by vBSEO 3.1.0
Style Provided By: Wrestling Clique - Wrestling Forums
All contents © the respective posters. None of the content on this forum is the responsibity of TLW.