Recent Posts

1001

General Software Discussion / Re: Copying Collapsed Rows/Columns in Excel Misses Data

« Last post by Target on January 16, 2011, 09:56 PM »

You sure they don't just get pasted in a collapsed state??

-Perry Mowbray (January 16, 2011, 09:39 PM)

nope, you get the collpsed data only

note that it can get really confusing when you go the other way, ie copy data TO a sheet with hidden rows or columns

1002

Finished Programs / Re: Utility: Convert Multiple Columns in Excel to a Single Column

« Last post by Target on January 16, 2011, 07:27 PM »

I really don't understand why MS doesn't get .NET into MS Office already. You can use it in SQL Server, so why not Office? VBA is, well, painful in comparison.

-Renegade (January 16, 2011, 06:58 PM)

I couldn't comment on that one, though as with many things MS's reasoning defies most accepted forms of logic. 

VBA got me started on coding and I don't know C (in any variation).  From a user perspective .NET seems to be a significant piece of bloatware (who wants to download and install a huge - up 250M -  piece of extra software just so they can write or run a macro...)

That said I'm not averse to learning C, I just don't have a reason at the moment

1003

Finished Programs / Re: Utility: Convert Multiple Columns in Excel to a Single Column

« Last post by Target on January 16, 2011, 06:28 PM »

It would be interesting to know how you go... I've got lots of Excel and Word toolbars around the building and we're looking at upgrading shortly...

-Perry Mowbray (January 10, 2011, 11:19 PM)

I only have the one, but it's used all over the country.  If and when I do something about it I'll let you know

-Target (January 11, 2011, 12:24 AM)

Turns out the ribbon is easier to work with than I expected - had some downtime on Friday because of a systems outage so I spent some time on this

As usual the VBA help file is, well, less than helpful.  The info may be there, but I clearly don't know the right questions to ask.

A google search turned up several references but this one did the trick. 

Everythings in XML, and while I won't pretend that I know anything about XML structure or syntax, the structure as used is pretty straight foward. 

Greatest aid is the Custom UI Editor for MS Office (get it here).  It will generate sample code, includes a code validator, and will generate the necessary VBA stubs for you (you need to copy and paste into the editor if you're going to use them).  That last bit is worth noting - it took me a while to twig to the fact that there is a parameter you need to add to the called routines to make the buttons work properly.

No doubt there's more to the whole ribbon interface, but I only needed to get a new tab on the bar for my macro's so that's as far as I've taken it (but YMMV)

1004

Finished Programs / Re: Utility: Convert Multiple Columns in Excel to a Single Column

« Last post by Target on January 11, 2011, 04:35 PM »

Why not? The square is not that far away...

-Shades (January 11, 2011, 04:06 PM)

I simply don't have the power

Some people are just too chicken, and always ask Y they must X the road...

-Renegade (January 11, 2011, 04:27 PM)

it's because the cars sometimes don't algebraic in time

1005

Finished Programs / Re: Utility: Convert Multiple Columns in Excel to a Single Column

« Last post by Target on January 11, 2011, 12:32 AM »

On the plus side, at least it wasn't done under the table.

-cranioscopical (January 10, 2011, 11:22 PM)

but we never did get to the root of the problem

1006

Finished Programs / Re: Utility: Convert Multiple Columns in Excel to a Single Column

« Last post by Target on January 11, 2011, 12:24 AM »

It would be interesting to know how you go... I've got lots of Excel and Word toolbars around the building and we're looking at upgrading shortly...

-Perry Mowbray (January 10, 2011, 11:19 PM)

I only have the one, but it's used all over the country.  If and when I do something about it I'll let you know

FYI you're old toolbars will still showup, just not like they used to (they appear under the ADDINS tab of the ribbon, and they can be attached to the quick launch bar)

I have mixed feelings about the 'upgrade' - there are good things (substantially larger worksheets for one), but the interface changes are so drastic that my productivity took a huge hit (this has been discussed elsewhere).  Even 2 months on I still have to go searching for things I used to use all the time

1007

Finished Programs / Re: Utility: Convert Multiple Columns in Excel to a Single Column

« Last post by Target on January 10, 2011, 11:10 PM »

You shouldn't have been on your cell!

I was only there to help her with her division

1008

Finished Programs / Re: Utility: Convert Multiple Columns in Excel to a Single Column

« Last post by Target on January 10, 2011, 09:48 PM »

For anyone that has spent an hour trying to explain over the phone how to open a file... and failed... hehehe -- yeah... it happens... Sometimes ya gotta just pick yer battles.

-Renegade (January 10, 2011, 09:12 PM)

i think you meant bottles...

1009

Finished Programs / Re: Utility: Convert Multiple Columns in Excel to a Single Column

« Last post by Target on January 10, 2011, 09:28 PM »

the code could go in personal.xls to always be available

-AndyM (January 10, 2011, 09:04 PM)

actually the easiest way is to put it in an addin (XLA) - save it to the XLSTART directory and put a button on the toolbar...

don't know about that last bit in versions past 2003 (we finally got 'upgraded' about 2 months ago and I'm still struggling with all those productivity gains...)

1010

Finished Programs / Re: Utility: Convert Multiple Columns in Excel to a Single Column

« Last post by Target on January 10, 2011, 08:49 PM »

And I suppose you told him you sweated blood to get them there…

no, but it sure gave me pores when she said it...

1011

Finished Programs / Re: Utility: Convert Multiple Columns in Excel to a Single Column

« Last post by Target on January 10, 2011, 05:28 PM »

Nope. Not possible. Like I said, "A fellow I work with is not very techno-savvy."

He'd have to do WAY too much BS to get things working. He can copy, and he can paste, and he can click. Macros? Waaaayyyy out. Get him to copy concatenation formulas (and have dependent cells)? Not gonna fly.

Nope. This was a quick hack to help him get column concatenated quickly without any extra information being needed.

It's hard working with anything in Excel given the funky way that Excel works with the clipboard, so there's that extra step to de-focus/de-select in there, but that's the only thing that's kind of icky.

But normally, yeah -- there are many other ways. This is just designed to work for a particular work-style.

-Renegade (January 10, 2011, 01:28 AM)

Seemed like you were cracking a walnut with a sledgehammer is all, but I figured there was probably a reason.

I still think it could be done without the copy and paste steps via a macro, but I suspect the user will be using this on lots of different source documents

<rant>
Actually this sort of thing really burns me.  I have more than a few colleagues like that (I had someone doing important calculations ask me what the numbers in red were...) and even though they work with Excel all day every day they can't or won't take the time to learn some basics that will make their (working) lives easier
</rant>

1012

Finished Programs / Re: Utility: Convert Multiple Columns in Excel to a Single Column

« Last post by Target on January 10, 2011, 12:45 AM »

sorry, don't mean to critical, but couldn't you have just used a concatenation formula (=columnA & columnB & columnC)?

you could use a VBA macro to fill it in and paste the values (no dependencies, apart from Excel)

1013

General Software Discussion / Re: Word Processor OS

« Last post by Target on January 09, 2011, 11:40 PM »

what about booting to the command line?

you can EDIT, and no doubt there are other CLI editors...

no distractions (unless they waste time drawing ASCII art...)

1014

Living Room / Re: DC Demographics

« Last post by Target on January 09, 2011, 05:41 PM »

all hail dilbert...

1015

General Software Discussion / Re: Ini manipulation tools (text manipulation tools) software

« Last post by Target on January 05, 2011, 11:46 PM »

you can easily do this in AHK (and probably AutoIt)

see iniread and iniwrite in the documentation

1016

Living Room / Re: most weird Ctrl key issue *ever*?

« Last post by Target on January 05, 2011, 07:29 PM »

what about hotkeys?

could another app be stealing using that key assignment?

1017

Living Room / Re: USB hubs - does anything like this exist?

« Last post by Target on December 30, 2010, 05:11 PM »

cool, thanks!!

1018

Living Room / Re: USB hubs - does anything like this exist?

« Last post by Target on December 29, 2010, 07:34 PM »

I wouldn't call it acceptable, there's a risk of destroying the USB device or you may have the two computers fighting each other if the voltage supply isn't exactly the same.
Since you'd be having one powered port anyway, (for external 2.5"), you might as well power them all externally - plus it's so easy

I prefer to leave the USB devices powered and just switch the data lines, there's less chance of a voltage spike being induced into the data lines that way.

-4wd (December 29, 2010, 07:24 PM)

<light goes on>

Did you mean don't ?

I do

Possibly just a couple of CMOS/TTL chips wired to input/output lines, the problem being looking through specs to find what's capable of >480MHz switching.  I'd need to look through my circuit library and check the web to get a better idea and see how critical it'd be.

See my edit to above post also.

now I'm curious about you're beneficial solution (are we going into manufacturing?)

1019

Living Room / Re: USB hubs - does anything like this exist?

« Last post by Target on December 29, 2010, 07:07 PM »

Yes, you can try a 2-pole, 2 position switch and just switch the data lines.  Power the hub from an external source so you're not switching the power lines as well.  Leave the ground connected back to the computers but not the +5V.
You might need some kind of buffering, (not the data type - more to debounce the change-over), but you can certainly try it without first.

-4wd (December 29, 2010, 06:49 PM)

that's more like it

so is it acceptable to leave both power lines connected concurrently?  I haven't looked at the pinouts yet, but I assume this from your comments

and how would you 'buffer' the change over?  I don't anticipate that I would be switching while anything critical was running so I do expect it would be an issue, but now I'm curious

1020

Living Room / Re: USB hubs - does anything like this exist?

« Last post by Target on December 29, 2010, 06:25 PM »

A simple toggle switch to the control lines of a Quad Bilateral Switch, (4066 CMOS or 74HC(T)4066 TTL), is a relatively simple way to do it and it would be self-powered.  However, I'd look at making all the ports powered via external means.  You're talking about 12 ports, if they are all occupied at a relatively modest power consumption of 100mA, even with 1 powered that's still 1.1A, over twice the specified current sourcing for 1 USB port - which will be where it's coming from.

A more interesting way would be to use a micro-controller, (eg. PIC, Atmel), to monitor the incoming PC USB data lines and through-switch to the devices - more interesting but maybe not more practical since, (as mwb1100 said), Windows would need time to identify all the devices before data could be sent through, so you'd need to incorporate some sort of RAM buffer I'd think or message back to the OS to hold until device ready is signaled.

-4wd (December 29, 2010, 06:11 PM)

HA! once a tech, always a tech

my electronics knowledge is rudimentary at best so that's possibly beyond my current abilities (seems like it would need some sort of control circuit to make it work) - I was thinking along the lines of a simple (analog) type switch

1021

Living Room / Re: When you make your 100'th Post

« Last post by Target on December 29, 2010, 05:40 PM »

Yeah, it really is too bad that I usually don't wait for a day or two, before answering, but so often "I let my mind wander and it didn't come back" (another thesis by Calvin)!

-Curt (December 29, 2010, 05:37 PM)

that's why it's called a retortoolate...

1022

Living Room / Re: USB hubs - does anything like this exist?

« Last post by Target on December 29, 2010, 05:01 PM »

It sounds like you want to be able to share USB devices between computers.

-4wd (December 29, 2010, 03:49 AM)

that's exactly what i want to do, but seems like this is a niche market

I don't need (or want) to switch monitors, so a KVM would be a bit over the top (wasted)

there are switches (the first 2 options) that share a single device with multiple PC's, but you still need to daisy chain with a hub (or hubs) if you have multiple devices to share

The lindy link is a possible candidate, but only 4 ports

it seems no one has really made what seems like a logical step to me and built a device with penty of ports that does both

as an aside it occurred to me (about 10 minutes after I posted, DUH!) that someone, somewhere must have already done this so I went looking for a DIY version.  There are a number of references of multiple port hubs being constructed by gutting a bunch of cheap hubs and mounting them all in a single case (basically daisy chaining), and it doesn't seem like much of a stretch to extend this concept to add a switch and 2 inputs...

3 unpowered and 1 powered (with a suitable power supply) would give me 12 ports...hmmm 

1023

Living Room / USB hubs - does anything like this exist?

« Last post by Target on December 29, 2010, 12:03 AM »

sorry about the stupid title, but this seems like a logical piece of hardware but I don't seem to be able to find anything like it

I routinely work remotely using a laptop, however laptops generally don't have anywhere near enough USB ports for all the bit's and pieces I use so I use a hub (OK so far).  Trouble with this setup is that it gets pretty untidy

what I want to be able to do is plug in my keyboard, mouse, printer, USB key, RNA token, phone (modem), and external drive and switch them between the laptop and home PC so I'm wondering if there is a hub (7 ports minimum, preferably 10) that works like a KVM switch, ie where I can switch between 2 different inputs (the laptop or my own PC).

To complicate things further, I use a 2.5" 500G external drive that requires a reasonable power supply (it uses 2 ports on the docking station) so ideally the hub needs to be able to provide sufficient power to run the drive...

this seems like a reasonable piece of hardware (and I'm sure I'm not the only one that's had this experience), but I've googled for this several times and come up dry.  I suppose I'd be prepared to pay a reasonable amount for something that is well regarded (I have tried numerous no-name hubs and there is almost always one or two ports that don't work reliably)

Anyone got any idea's?
 

1024

General Software Discussion / Re: search and replace in excel file

« Last post by Target on December 28, 2010, 11:03 PM »

the following should work - note that it will delete the entire row, not just the range containing the data

[Select]

Sub xxx()
Dim rowno As Integer
rowno = 2

Do
    If Cells(rowno, 3) = 150 Then Cells(rowno, 4).Value = Cells(rowno, 4).Value + 70   
    If Cells(rowno, 3).Value = 0 Then Cells(rowno, 3).Value = 150

    If Cells(rowno, 3) = 45 and cells(rowno,5) = 47 Then Rows(rowno).Delete Else rowno = rowno + 1

Loop Until Cells(rowno, 3) = ""End Sub

1025

General Software Discussion / Re: search and replace in excel file

« Last post by Target on December 28, 2010, 09:49 PM »

if I understood what you were trying to do then the macro code might looks something like this (I've taken the same approach as andy, ie no testing)

[Select]

Sub xxx()
Dim rowno As Integer
rowno = 2

Do
    If Cells(rowno, 3) = 150 Then Cells(rowno, 4).Value = Cells(rowno, 4).Value + 70
    If Cells(rowno, 3).Value = 0 Then Cells(rowno, 3).Value = 150
    rowno = rowno + 1
Loop Until Cells(rowno, 3) = ""

End Sub

if you have a substantial number of records this might take a while