MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

SQL queries to read a spreadsheet

  Asked By: Gerritt    Date: Aug 25    Category: MS Office    Views: 741

I have several sheets with several bank accounts' data in them. I
want to create a union query and produce a new table which assembles
the various bank statements into a single virtual bank statement for
analysis purposes, e.g. creating a pivot table with the combined data.

I'm kind of stuck, and have looked for some simple examples online but
I can't find anything to get me started. Could someone either give a
few lines of code or point to a website with samples?

This is a macro that will be run in one spreadsheet, it doesn't need
to connect to a different workbook, just compile data from several
sheets and create this new combined sheet, which I'll create a pivot
table from.



4 Answers Found

Answer #1    Answered By: Kuhlbert Schmidt     Answered On: Aug 25

It's a long while since I used SQL into workbooks, so someone else might
have information readily to hand.

However, IIRC your SQL needs to refer to a workbook  file. I.e. it won't
look at information in a workbook that's open in RAM.

It is likely that the SQL would be able to refer to the copy of the workbook
that is on disk while you are running the code  from the copy of the workbook
that is in memory, but there is always the potential for these to be out of
step, so that's a real risk.

If you want to use SQL, then my thought is that you should use a second
workbook to access the one with the data  in it. If you want to stay inside
one workbook, then don't use SQL - simply use code and/or formulas.

You can do your union with a subroutine that is simply given a worksheet
name in its call and goes and gets the information from that sheet  and
appends it to your result sheet. Call this as many times as there are
sheets - from a master subroutine or button event handler. Once they
finish, sort the information as needed.

If your union is working on differently-formatted "tables", then you'd
probably have one subroutine for each "select" in the SQL and call them
appropriately from the master.

Note that I also seem to recall that SQL access to Excel is not very good at
referring separately to multiple worksheets in the one workbook. But it's a
hazy recollection and possibly even obsolete.

In essence, if it's Excel reading Excel, use code, not SQL. If it's Excel
reading Access (or any other DB), use SQL. If it's Access reading Excel,
use SQL in preference to code.

Answer #2    Answered By: Maria Hughes     Answered On: Aug 25

Or if I want to do this in a really roundabout way, first export the
data (with pre-processing to standardise the columns etc) to Access,
and then bring it back from Access to Excel.

Ok, ta. You've at least diagnosed the first major issue with my code.

Answer #3    Answered By: Damian Jones     Answered On: Aug 25

Have you considered creating  the pivot table  directly, using multiple data
ranges (your bank account data  sheets)? Depending on the layout of your data, it
may not be necessary to combine the data before creating the pivot  table.

Answer #4    Answered By: Eamon Jones     Answered On: Aug 25

> Have you considered creating  the pivot table  directly, using
multiple data  ranges (your bank account data sheets)? Depending on the
layout of your data, it may not be necessary to combine the data
before creating the pivot  table.

Quite a lot needs to be done in reorganising the data. For example
some of the bank statements  contain credits and debits in separate
columns, others have one column with positive and negative values.
I'd need among other things to consolidate these into a single
transactions column. There are a bunch of other things like that
which need doing also.

Didn't find what you were looking for? Find more on SQL queries to read a spreadsheet Or get search suggestion and latest updates.