MS Excel / VBA help thread

For serious?
I'm beginning r, SQL, medium excel.
Want to do more real modelling and stats.

Saving as | delimited rather than , delimited in Excel. Is there a way to do this without changing the region settings on your PC?

Save as CSV then find/replace

It's not a big deal to change the region settings - or are you not allowed in there?

I'm sure it goes without saying but watch out for 'comment' type fields with find and replace in case they've got a comma in them.

Save as CSV then find/replace

You're bollocksed if there are commas anywhere in cells though.

You could write a vba function that concatenates all the cells in a row together, | separated. Here's one:

Public Function Sconcat(rng As Range, Optional delim As String = "|")

    Dim cellIt As Variant
    Dim returnValue As String

    For Each cellIt In rng.Cells
        returnValue = returnValue & delim & cellIt.Value
    Next cellIt

     returnValue = Right(returnValue, Len(returnValue) - Len(delim))

    Sconcat = returnValue

End Function

Call it from the worksheet as =sConcat(B2:D2), where B2:D2 contains the row you want to stuff together, copy it down for each row. Copy the resulting column into a textfile.

It's not that big a deal to change the region settings but inevitably I forget to change them back and so on and it's annoying if it's saving via VBA

There are nearly always random commas in the data which is why I want to save as | delimited and find/replace won't work.

I found this macro which seems to do the job and isn't too slow (about 3 minutes for saving a 1m row, 13 column spreadsheet)

Sub DelimFile()

Open "C:\pipe.csv" For Output As 1 'Change this path

rowno = 1
colCount = Application.CountA(ActiveSheet.Rows(1))
While ActiveSheet.Cells(rowno, 1) <> ""
    dataout = ""
    For c = 1 To colCount
        If c <> colCount Then
            dataout = dataout & Trim(ActiveSheet.Cells(rowno, c)) & "|"
        Else
            dataout = dataout & Trim(ActiveSheet.Cells(rowno, c))
        End If
    Next c
    Print #1, dataout
    rowno = rowno + 1
Wend
Close #1
End Sub

There are nearly always random commas in the data which is why I want to save as | delimited

Could you save as TSV (tab separated) instead? That's built in to Excel

You could throw that into the workbook_beforesave event - with a message asking if yo'd also like to export as | separated file. It might get annoying to be prompted every save mind...

It's not something I always use, just need it occasionally and have a few macros that I would prefer to save as | delimited. Will just tidy that up with a dialog box for where to save to and leave it at that I reckon.

Some things don't always seem to cope well with importing tab delimited files for some reason.

Anyone any good at VB in Word? I'm trying to select tables and insert rows, change headers, etc but I can't work out how to select a table in VBA in Word (the document has multiple tables). A quick google doesn't seem to give any answers and I can't see any table names in Word. Macro recorder doesn't give any help either.

Would it be easier to embed an Excel sheet and interact with that?

Can you get vba to play nice between the Word and an embedded Excel sheet.

That sounds amazing.

I'm a .Net developer who thinks using VBA or even pivot tables is a sign you should be using Access.

I'd rather generate LaTeX than have Word involved in any workflow.

Yes, I definitely wouldn't want to be involved in a Word / Excel / VBA mashup - but I'd love to stand back and watch someone else try to make it happen. It sounds like pure theatre...

If it was up to me I'd have done it all in Excel but the template is already in Word. Eventually found what I wanted on the MS site, I always forget how much info they have there https://msdn.microsoft.com/en-us/vba/office-vba-reference

It was:

ActiveDocument.Tables(1).Select

where the number in brackets is your table (although I can't find a way of identifying which table is which number, it appears to run in order from the start).

You can "rename table" can't you? do it that way?

As part of this discussion.
I have two tables in access, my normal strategy is to merge into one, link to excel, pretty graphs and dashboard.
because i like the formatting control in excel.

i bet you can write this from access to pdf yeah?
is it a ball ache and should i stick with:
data in access
data to excel
pretty graphs linked to powerpoint
pdf

i think after writing that it looks like a ballache (but it really isn't) and could easily go access > output

You can in Excel but not that I know of in Word (although I'm no expert).

I can't work out how to select a table in VBA in Word (the document has multiple tables)

Can you give a bit more detail - how do you know which table(s) you want to select, functionally speaking? Not just the one at the text cursor/in the selection?

Right.
So I have one table of data from years X -Y (static unchanging)
And
One from z- now (changes each week with new stuff)

Same fields in each. They're from the same select survey survey.

There's 157 fields in each.

How do I join these two fuckers?

Access apparently says >255 is no good.

If it’s the same fields but different date ranges it’s a Union not a join right? Smash it all into a new table with new year/month field for each row?

I tried union earlier today and got told too many fields damo too many fields.

Odd. Not a problem I’ve come across before.

What do you want to do with it?

Simplify my life.
Extract data from survey for this school year.
Link to this table in access.
Append it to a table of data for the previous years.
Clean it up
Link cleaned table to excel
Graphs and ting in excel (clicky slicers)

Repeat year on year