MS Excel / VBA help thread

How do I get it into excel

With a macro that strips out the text.

Like dis

[code]
Public Sub list_all_shape_items()

Dim myDoc as Visio.Documents
Dim myPage as Visio.Page
Dim shpObj as Visio.Shape
Dim cellObj as Viso.Cell
Dim ShpNo as Integer
Dim Tabchr as String
Dim myFileName as String

For Each myDoc in Documents
    For Each myPage in myDoc.Pages

    myFileName = Replace(myDoc.Name, ".vsd", "") & "-" & myPage.Name
    myFileName = Replace(myFileName, "/", "-")
    Open "C:\Temp\" & myFileName & ".txt" For Output As #1
    Tabchr = "|"

    For ShpNo = 1 to myPage.Shapes.Count

        Set shpObj = myPage.Shapes(shpNo)
        If Not shpObj.OneD Then ''' only list out the 2-D shapes (not lines)
             Print #1, shpObj.Name; Tabchr; shpObj.Style; Tabchr; Replace(shpObj.Text, Chr(10), "[return]"); Tabchr
        End If

    Next ShpNo

    Close #1
    Set celObj = Nothing
    Set shpObj = Nothing

    Next myPage
Next myDoc

End Sub
[/code]

Thanks all. I actually found that the schema was for an old version of excel. So I took a punt and changer the .xml file extension to .xls and it worked.

@Dammit

I need to count the number of Postcode Districts that a company covers, but the data I have been provided is displayed in the below format:

AL1-10
B1-2,12-15,20-38,40,45-50,65-80,95-98
BB1-12,18
BD1-9,12-24
BH1-7,9-25,31

Is there any formula/function I can use to convert to a more excel-friendly format. The ranges (e.g. AL1-10) are causing me problems, otherwise I'd normally do a Text-to-column function and then concatenate the postcode initial with the subsequent numeral...

WEll....
You could do this
in c2 put
=C1&B2

in b2, put 1, fill this down to 100?
Then in c1 put AL
thencopy that formula down to 100.
the contents of the cells will be al1 - al 100

you can then tudy shit up.

Yep, that's an option, but that was just a snapshot of the data I have. The full list covers 103 postcode districts, so would need to do that 103 times :(

Something something something dynamic formulas?

You could use Left and Right (with some Ifs to determine the correct splits) to separate out the two sides of the range, then it’s a fairly simple task of addition and subtraction.

How many companies is this for?

Fuck it, I'll just ask them to redo the source data for me.

The right answer.

If it's a one off, you could try this:

text to columns
use find("-",a1) and mid / left / right to get the numbers either side of the dashes
use substitute(a1,substitute(A1,"1")) to remove the letters. or just type "1" in each cell...
concatenate stuff

I think I would get rid of the letters entirely. Be very careful with London postcodes like EC1A ... , you might have to do this step manually for those.

Then do a pattern search for any single digits (none in your example) and replace with a temporary symbol such as 'x'. Do the same for any double digits like 40, 18, 31 in your example. You'll count these as '1' later.

Then split to columns on ','. The ranges should collapse to an negative integer one less than the count of postcodes. So '1-10' will now be '9'.

Add the count of numeric cells to the sum of numeric cells. Add the count of 'x' to this and that should be your answer.

*Edit: Hmm, I think I must have dreamt that Excel had pattern/regex matching in Search and Replace... it can be done in Word but it's a bit fiddly.

Yeah run this through an online regex tool to grab the codes. That's the tool you need.

Or ask if you know any Linux bods.

I would paste it into excel as is and work some logic around ctrl h replacing and text to columns with a bit of doodling to create the rangers - 19 - 23 before a vlookip (perhpas not necessary) to post in the letters back to the numbers.

Back at uni i'm sure i could run some sed scripts over the top of that to sort it out in unix in about 20mins.

edit

Fuck it, I'll just ask them to redo the source data for me.

Spoken like a true business user. >

You can write a vba function to wrap the vba 'split'function up and make it accessible from the spreadsheet. You could then split your strings on the commas to chop it up a bit.

That would get you half way there, but requires knowledge of vba and array formulas :)

You could approach it the other way. Write a long list of all the postcodes out ( B1,B2...B98) then try to work out a way to see if they're in your list above. Mark any not there for deletion.

But yeah... Get someone to rewrite it. That's stupid input :)

Array formulas??
Fuck dat.

Copying sheets from one workbook to another, the macro and action button still point to the old workbook. Is there a simple fix?

Edit: new workbook developer > Macros shows no macros. Presume these don't copy across?

Edit 2: the pivot tables reference the old workbook too (i can fix this tho)

No that I've found, my workaround is to use a hidden config sheet with file paths/etc. There's probably a better way!

If you're just copying sheets, then no I don't think macros copy as well - you either need to save the whole workbook as a copy or copy individual macros across. Obvs make sure it's .xlsm to keep the macros!

Can you pipe the input through something which supports LINQ?

static IEnumerable<string> EnumeratePostcodes(IEnumerable<string> postcodeRanges){
    return
        from line in postcodeRanges
        let prefix = new string(line.ToCharArray().TakeWhile (x => Char.IsLetter(x)).ToArray())
        from clause in line.Substring(prefix.Length).Split(',')
        let range = clause.Split('-').Select (c => Convert.ToInt32(c)).ToArray()
        from item in Enumerable.Range(range[0], range.Length == 1 ? 1 : (1+range[1]-range[0]))
        select prefix + item;
}

void Main()
{
    EnumeratePostcodes(new[]{
        "AL1-10",
        "B1-2,12-15,20-38,40,45-50,65-80,95-98",
        "BB1-12,18",
        "BD1-9,12-24",
        "BH1-7,9-25,31"
    }).Dump();
}

IEnumerable (122 items)
AL1
AL2
AL3
AL4
AL5
AL6
AL7
AL8
AL9
AL10
B1
B2
B12
B13
<snip>

Oooooh, la di dah!

It'll be all lambda functions up in here next.

(Nice one)

I was approaching it wrong... in the end all I had to do was make a copy of the macros & pivots workbook and copy the other worksheets (no macros or pivots) across into that.

Will be trickier if ever there's macros in both workbooks, i found a tip to export & import these in this case.

You can drag macro modules and forms from one workbook to another in the VBA editor (Alt-f11). Not sheets though.

^ good to know, ta.

You can export and import macros also.