Powershell scripting support group

[Lana Torrin]

I know we already have a programming thread, but powershell is in its own league when it comes to horrible horrible languages with no consistency and structure.. I cant take the pain anymore, so I figure I'll ask my FHC brosif..

So starting this thread off in the best way possible, this is a 'powershell but also not' question.

How the fuck do i insert a column in excel.. I have the following code creates a COM object, opens a file and opens the appropriate worksheet.. But then I get stuck.

$xl=New-Object -ComObject "Excel.Application"
$xl.Visible=$True 
$Workbook = $xl.Workbooks.Open($filename)
$WorkSheet = $Workbook.WorkSheets.Item("Server List")
$WorkSheet.activate()

I just cant find a good reference for excel COM programming, combined with me not really understanding how powershell interacts with COM stuff..

[IceBlock]

I think COM objects functions are almost equal in every language, VB included. Anyways, here is a site that shows how to add to a cell:
http://www.petri.co.il/export-to-exc...powershell.htm

[Lana Torrin]

I think COM objects functions are almost equal in every language, VB included. Anyways, here is a site that shows how to add to a cell:
http://www.petri.co.il/export-to-exc...powershell.htm

Thats the thing though, that doesn't show how to add a cell, just how to manipulate the ones already in existence. I have an already existing spreadsheet and I need to add a whole column in to the middle of it and have excel update all references and formula exactly like it does when you do it though the interface. I can not find this anywhere on google (and therefor on the internet).

I realize its not specifically a powershell thing, but after spending 4 hours yesterday trying to work out why an SQL statement was returning the correct value, but in some fucked up format that didn't seem to correspond to anything I have ever used before (an array of 2 dimensional arrays.. Seriously, this is how SQL datasets in powershell work.. Fucking PHP returns associative arrays so you don't need to perform a second select query to get a single fucking entry back) im in the mood for blaming powershell for every problem I have ever seen.

Sure its powerful but jesus fucking christ is it THAT hard to keep any form of consistency.. I mean when you ask a question to a powershell expert and the first response is 'what version are you running' something is fucked..

[Xamuni]

use the macro recorder and record the actions that you do through the interface. It should give you a hint how to do it with code.

[Lana Torrin]

use the macro recorder and record the actions that you do through the interface. It should give you a hint how to do it with code.

I have given up for the day.. I realized that the server thats going to be running this script as a task has a different version of powershell and office installed than the test server I have been given.

[IceBlock]

What? When you add a worksheet, all cell exist and you can manipulate them? Or do you mean INSERT column?
http://social.technet.microsoft.com/...-b524c9c4830b/

[Xamuni]

use the macro recorder and record the actions that you do through the interface. It should give you a hint how to do it with code.

I have given up for the day.. I realized that the server thats going to be running this script as a task has a different version of powershell and office installed than the test server I have been given.

anyway, if you want to give it a try again, you might find this useful: http://netoffice.codeplex.com/. It´s incredibly helpful when dealing with Office automation.

[Liare]

i smell a worse-than-failure cooking in here to be honest, why would you want to manipulate a excel document with scripting ? you're not kludging some kind of "database less solution" or something are you ?

[Lana Torrin]

i smell a worse-than-failure cooking in here to be honest, why would you want to manipulate a excel document with scripting ? you're not kludging some kind of "database less solution" or something are you ?

Reporting.

It is mandated that all sites have to report in an excel document (no fucking idea why) and they have to compile daily reports showing trending (includes shit like drive usage and key software installed). For the sites that have only a few servers this can be done by hand, for the sites with a couple of dozen servers I understand they get a few people to do it and script the rest (ie, drive sizing).. We have an environment with over a thousand servers which frequently gets changed by other people (many many project groups making and removing VMs all the time). We automate it.. Except its been known for a long time that the 'just pull the server list from AD' approach that was implemented many years ago misses a tone of servers and grabs a bunch that aren't ours to support (we are also the only site where we have more than 1 company supporting stuff).. So we have to add a few things in now and not break the existing scripts doing it..

I'm actually not horrible at scripting, just at powershell.. But they will NOT implement actual software to do this for 2 reasons. Firstly they do not want to spend money because they cant bill any clients for it (apparently the 3 or 4 days I have spent on the other bits of the script doesn't count because I was officially doing general support duties) and secondly some of our contracts say we cant implement any new software without it first being checked and certified by various government departments, which will take forever. Apparently a script is exempt from this for some reason..

[JForce]

i smell a worse-than-failure cooking in here to be honest, why would you want to manipulate a excel document with scripting ? you're not kludging some kind of "database less solution" or something are you ?

Reporting.

It is mandated that all sites have to report in an excel document (no fucking idea why) and they have to compile daily reports showing trending (includes shit like drive usage and key software installed). For the sites that have only a few servers this can be done by hand, for the sites with a couple of dozen servers I understand they get a few people to do it and script the rest (ie, drive sizing).. We have an environment with over a thousand servers which frequently gets changed by other people (many many project groups making and removing VMs all the time). We automate it.. Except its been known for a long time that the 'just pull the server list from AD' approach that was implemented many years ago misses a tone of servers and grabs a bunch that aren't ours to support (we are also the only site where we have more than 1 company supporting stuff).. So we have to add a few things in now and not break the existing scripts doing it..

I'm actually not horrible at scripting, just at powershell.. But they will NOT implement actual software to do this for 2 reasons. Firstly they do not want to spend money because they cant bill any clients for it (apparently the 3 or 4 days I have spent on the other bits of the script doesn't count because I was officially doing general support duties) and secondly some of our contracts say we cant implement any new software without it first being checked and certified by various government departments, which will take forever. Apparently a script is exempt from this for some reason..

Holy sweet mother of god get a good CMDB solution

[Lana Torrin]

i smell a worse-than-failure cooking in here to be honest, why would you want to manipulate a excel document with scripting ? you're not kludging some kind of "database less solution" or something are you ?

Reporting.

It is mandated that all sites have to report in an excel document (no fucking idea why) and they have to compile daily reports showing trending (includes shit like drive usage and key software installed). For the sites that have only a few servers this can be done by hand, for the sites with a couple of dozen servers I understand they get a few people to do it and script the rest (ie, drive sizing).. We have an environment with over a thousand servers which frequently gets changed by other people (many many project groups making and removing VMs all the time). We automate it.. Except its been known for a long time that the 'just pull the server list from AD' approach that was implemented many years ago misses a tone of servers and grabs a bunch that aren't ours to support (we are also the only site where we have more than 1 company supporting stuff).. So we have to add a few things in now and not break the existing scripts doing it..

I'm actually not horrible at scripting, just at powershell.. But they will NOT implement actual software to do this for 2 reasons. Firstly they do not want to spend money because they cant bill any clients for it (apparently the 3 or 4 days I have spent on the other bits of the script doesn't count because I was officially doing general support duties) and secondly some of our contracts say we cant implement any new software without it first being checked and certified by various government departments, which will take forever. Apparently a script is exempt from this for some reason..

Holy sweet mother of god get a good CMDB solution

We have one, its owned by the customer, its shit and the previous contractor basically didn't use it. Also with the dual jurisdiction its confusing as shit to pull meaningful reporting. Plus this is an international company with several government contracts, so one cmdb for everything is never going to happen and its completely unrealistic to force your solution on every customer.

So we are left with shitty Excel reports feeding in to god knows what in a country I don't live in for execs I don't care about.

[Liare]

i smell a worse-than-failure cooking in here to be honest, why would you want to manipulate a excel document with scripting ? you're not kludging some kind of "database less solution" or something are you ?

Reporting.

It is mandated that all sites have to report in an excel document (no fucking idea why) and they have to compile daily reports showing trending (includes shit like drive usage and key software installed). For the sites that have only a few servers this can be done by hand, for the sites with a couple of dozen servers I understand they get a few people to do it and script the rest (ie, drive sizing).. We have an environment with over a thousand servers which frequently gets changed by other people (many many project groups making and removing VMs all the time). We automate it.. Except its been known for a long time that the 'just pull the server list from AD' approach that was implemented many years ago misses a tone of servers and grabs a bunch that aren't ours to support (we are also the only site where we have more than 1 company supporting stuff).. So we have to add a few things in now and not break the existing scripts doing it..

I'm actually not horrible at scripting, just at powershell.. But they will NOT implement actual software to do this for 2 reasons. Firstly they do not want to spend money because they cant bill any clients for it (apparently the 3 or 4 days I have spent on the other bits of the script doesn't count because I was officially doing general support duties) and secondly some of our contracts say we cant implement any new software without it first being checked and certified by various government departments, which will take forever. Apparently a script is exempt from this for some reason..

worse than failure in progress confirmed.

you are using the wrong fucking tool here, a CMDB that's not shit is what you need to be honest, what you are kludging together here might sort of work, up until somebody touches any part of the rickety construction, and you will get stuck with supporting it for life.

[LoudSpeakly]

To answer the original question...

PS C:\Windows\system32> $worksheet.range("A1:D4").cells = "ohhaithar"
PS C:\Windows\system32> $worksheet.range("B:B").insert()

Using PowerShell as some kind of hardmode VBA just seems a world of wrong though.

[Lana Torrin]

i smell a worse-than-failure cooking in here to be honest, why would you want to manipulate a excel document with scripting ? you're not kludging some kind of "database less solution" or something are you ?

Reporting.

It is mandated that all sites have to report in an excel document (no fucking idea why) and they have to compile daily reports showing trending (includes shit like drive usage and key software installed). For the sites that have only a few servers this can be done by hand, for the sites with a couple of dozen servers I understand they get a few people to do it and script the rest (ie, drive sizing).. We have an environment with over a thousand servers which frequently gets changed by other people (many many project groups making and removing VMs all the time). We automate it.. Except its been known for a long time that the 'just pull the server list from AD' approach that was implemented many years ago misses a tone of servers and grabs a bunch that aren't ours to support (we are also the only site where we have more than 1 company supporting stuff).. So we have to add a few things in now and not break the existing scripts doing it..

I'm actually not horrible at scripting, just at powershell.. But they will NOT implement actual software to do this for 2 reasons. Firstly they do not want to spend money because they cant bill any clients for it (apparently the 3 or 4 days I have spent on the other bits of the script doesn't count because I was officially doing general support duties) and secondly some of our contracts say we cant implement any new software without it first being checked and certified by various government departments, which will take forever. Apparently a script is exempt from this for some reason..

worse than failure in progress confirmed.

you are using the wrong fucking tool here, a CMDB that's not shit is what you need to be honest, what you are kludging together here might sort of work, up until somebody touches any part of the rickety construction, and you will get stuck with supporting it for life.

We have thousands of clients in geographically and politically separate environments covered under hundreds of different NDA criteria about what we can and can't expose outside of their home environment. Any CMDB that even trys to conform to all of these criteria (many of which are conflicting) is going to be an absolute nightmare to manage and be a complete clusterfuck. So instead of setting up a system that will likely cost millions of dollars a year to run and be full of inaccurate and useless data , we generate reports that cost next to nothing and can be billed to the clients as normal running costs.

I know for a fact creating and storing a cmdb for the client I work on outside of their secure (lol) environment would put most of us in prison for a few years. I still don't know how we manage to get around that law with these scripts but we have been told by the client as long as we scrubbed of things like server names (which we do) we are golden.

Edit: Lol tunnel.

Also you seem to be of the misguided assumption that I am in a position that I can change policy of an international company just because I don't like something.

[Liare]

i smell a worse-than-failure cooking in here to be honest, why would you want to manipulate a excel document with scripting ? you're not kludging some kind of "database less solution" or something are you ?

Reporting.

It is mandated that all sites have to report in an excel document (no fucking idea why) and they have to compile daily reports showing trending (includes shit like drive usage and key software installed). For the sites that have only a few servers this can be done by hand, for the sites with a couple of dozen servers I understand they get a few people to do it and script the rest (ie, drive sizing).. We have an environment with over a thousand servers which frequently gets changed by other people (many many project groups making and removing VMs all the time). We automate it.. Except its been known for a long time that the 'just pull the server list from AD' approach that was implemented many years ago misses a tone of servers and grabs a bunch that aren't ours to support (we are also the only site where we have more than 1 company supporting stuff).. So we have to add a few things in now and not break the existing scripts doing it..

I'm actually not horrible at scripting, just at powershell.. But they will NOT implement actual software to do this for 2 reasons. Firstly they do not want to spend money because they cant bill any clients for it (apparently the 3 or 4 days I have spent on the other bits of the script doesn't count because I was officially doing general support duties) and secondly some of our contracts say we cant implement any new software without it first being checked and certified by various government departments, which will take forever. Apparently a script is exempt from this for some reason..

worse than failure in progress confirmed.

you are using the wrong fucking tool here, a CMDB that's not shit is what you need to be honest, what you are kludging together here might sort of work, up until somebody touches any part of the rickety construction, and you will get stuck with supporting it for life.

We have thousands of clients in geographically and politically separate environments covered under hundreds of different NDA criteria about what we can and can't expose outside of their home environment. Any CMDB that even trys to conform to all of these criteria (many of which are conflicting) is going to be an absolute nightmare to manage and be a complete clusterfuck. So instead of setting up a system that will likely cost millions of dollars a year to run and be full of inaccurate and useless data , we generate reports that cost next to nothing and can be billed to the clients as normal running costs.

I know for a fact creating and storing a cmdb for the client I work on outside of their secure (lol) environment would put most of us in prison for a few years. I still don't know how we manage to get around that law with these scripts but we have been told by the client as long as we scrubbed of things like server names (which we do) we are golden.

Edit: Lol tunnel.

Also you seem to be of the misguided assumption that I am in a position that I can change policy of an international company just because I don't like something.

*shrug*

have a CMDB for each client, as long as its the same product integrating information from each of these is rather trivial exercise, the whole "one CMDB to rule them all" shit that ITIL goes on and on about is plain bollocks that have never contacted the real world.

believe me, i know what kind of challenges you have there, we have to maintain similar segmentation within our company structure to the extend that IT is running several networks that are not, and will never be, physically connected, but i still maintain that you are using a baby's hammer on a 7 inch nail, sure it might work but use the proper fucking tool already.