MS Excel / VBA help thread

There's a stack overflow question where someone had this problem with three tables.

Two is confusing me.

Is it an access thing?

I don’t think I use access enough to answer that question.

I really don’t understand the issue, I’ll try replicating it today at work

Here we go:
https://support.microsoft.com/en-gb/help/291556/you-receive-a-too-many-fields-defined-error-message-when-you-save-a-ta

The internal column count that Microsoft Access uses to track the number of fields in the table has reached 255, even though you may have fewer than 255 fields in the table. This can happen because Access does not change the internal column count when you delete a field. Access also creates a new field (increasing the internal column count by 1) for every field whose properties you modify.

I have reached > 255 with 157 + 157 right?

This suggests a way of doing it with 3
https://stackoverflow.com/questions/13846776/union-all-query-too-many-fields-defined

Obvious question:
Why have I got 157 fields?

It's a database of schools, providing education from pre - reception - FE/HE (15 years) with 6 infectious diseases. (so, y1a,y1b,y1c,y1d,y1e,y1f x15).

Some schools offer different age ranges of education provision.

I guess one way of doing it is to rearrange the data into a tidier format making it long instead of wide. But that's a massive faff for a project that may not move along much further....

It's a few tables in a template. The ActiveDocument.Tables(1).Select seems to work with a bit of trial and error identifying which table I want.

Yeah you should have a table with:
Schoolref | disease | year | whatever you’re measuring

That requires a whole lot of reorganising....

How many lines currently in the table? I’ve used VBA in excel to convert similar in the past.
Obviously you’ve also got the database itself to configure still and reports/etc that mimic the handy way to view data when it’s all in a wide table.

Data comes from select survey in a wide format.
Current year has 2600 lines.
Survey is added to weekly from which ever schools have notifications to tell me.
Archive has 1755 lines.

I am trying to get things in place for if/when I leave. I want it to be simple and easy to understand.

extract data as csv.
open access. update linked table. run query to append / add to archive.
save as a linked table.
open excel
win

the more I learn about SSAS Tabular/PowerBI the more I'm going to tell people that it's the future. Even if you just install PowerBI free, you get the full tabular engine running in the background which is so much faster than anything you can do in Excel. And learn DAX. PowerBI over Tableau any day just for analysis services and microsoft ecosystem compatability. Power Query has been built in as "Get Data "

here's one of my first reports from some point last year. it's not the prettiest but we were just testing the free features. Feels like excel to use too, so there wasn't any real structured training. Just learning as we go.

https://data.london.gov.uk/dataset/london-fire-brigade-incident-records

@NurseHolliday @Chalfie +1 for starting a company. WAConsultants ?

I find Tableau a ballache. Maybe it's me being an old man resistant to change, or maybe I've spent 27 years learning how to use Excel and my brain is really conditioned to create stuff within the Microsoft Office world, it's really hard to think "how would I do something in Tableau" rather than think, "how would I do this in Excel but then use Tableau to make it look better". The kids graduating now and jumping straight into Tableau are the ones who are using it best.

@ObiWomKenobi

I'm in. With a name like that.

@NurseHolliday
That's the thing with everything isn't it?
It's hard to get upto speed and produce good looking stuff.
It's almost like you need to be able to say "hey, do it like this. not that". And manage people.

I find the best PowerBI users are the ones that have experienced the full development of the product. I.e. if you never used power query, power view, power pivot etc etc it's a different experience and depth of understanding

Won't that just target the first (second if zero-based, I forget) table in the doc?
That's fine if you'll always want to target the first table. But a more robust solution would tag the table uniquely within the template. When I did something like this I think I used a combination of field codes and plain text identifiers e.g [DISCLAIMER_GOES_HERE], the latter more obvious to non-savvy Word users.

Haven't tried PowerBI but Power Query is growing on me (although whoever decided that DAX commands should be case-sensitive deserves a slap). Power Pivot is good but I generally only use it as a glorified Excel for big data-sets, there's a lot I have no idea what it does.

It does, but ActiveDocument.Tables(2).Select selects the second one and so on.

There seemed to be a solution, probably what you're referring to, where you could bookmark tables and use those references but this was fine for what I needed.

I'm mucking around in power BI today. Is it always so slow?
Is it particularly resource intensive?
I am running windows 7....

EDIT : changed compatability mode to win 7
seems faster already.
I blame IT for setting it up.

YAWN:
UPDATE
I inserted the changing data into the static data. It works.

do you know how hot this made me?
http://datapigtechnologies.com/blog/index.php/split-data-into-several-columns-based-on-carriage-returns/

Just wait until someone FTPs the file to and from a unix box without changing the flag to binary transfer and your carriage returns and newlines are stripped out

Do you even bro?

The people I work with think that spreadsheets should have colour and shit in them.

do you know how hot this made me?

Why don't you just wear a gilet around the house like any normal person?

I have this
Access SQL

WHERE [Date entered] Between #1/1/2016# AND #31/12/2018#

I take an extract (wait come back) and it's not always on a fixed day.
What it should have in it is all data from 2016 - end of the last month.
So in feb it should look at Jan. March should look at feb.

In Excel I'd use something like

=EOMONTH(TODAY(),-1)

to get the end of the previous month.

It's DateSerial.
Sorry..

might help:

SET dateformat dmy
DECLARE @StartDate as datetime
DECLARE @EndDate as datetime
SET @StartDate = '01/01/2016'
SET @EndDate = DATEADD(month, DATEDIFF(month, 0, getdate()), 0)

your_query

WHERE your_date>=@StartDate AND your_date <@EndDate

use import query not direct query
use windows 10
use chrome/edge for the web service
dont import massive wide tables, use smaller tables with relationship joins