MS-Query

This is the first of what I hope will be a series on the topic. The next editions planned are annexes on how to create different kinds of connections to MS-Access, MS-Excel, ODBC databases and text files.

Hi dflak,
Thanks very much for this.
I recently started playing about with Queery things.
https://www.excelforum.com/excel-pro...-xl-files.html

_....

Here Just some feedback from me , comments just in passing interest, for you and anyone searching this Thread as I did...:

MS-Query 2017.docx
I do not quite recognise all your Queery, but that is probably as I Queered a Excel File. I do not seem to have as many options as you
ExcelMSQueery123.jpg http://imgur.com/965w5yy
MsQueerOptions.JPG http://imgur.com/xIoFL8U
?? I am not sure why you have so many ??



Data Connections.docx
I see you have a section, _ MS-Excel _
I need to do things a bit differently to you, or at least it seems so. I need
Data Ribbon ---- From other sources ----- From Microsoft Queery
Excel Files --- OK
( Drive I needed to select ---- OK )
Select Folder ---- OK
Select Folder --- OK
You have to repeat the last steps until the File you are interested in appears
Select the File ----- OK
From this point I follow along similar to you.
The important thing I found was to keep hitting that OK at a lot of points. Otherwise you can get thrown off into thinking something is happening as this thing , ( bottom right in the screenshot ) shows the whole time, and you wait forever for it, at least if like me, you think it is doing something ... ConnectingWTForEver.JPG http://imgur.com/FSzyEQL

I did not quite follow you point ( made in MS-Query 2017.docx ) about not checking the Wizzard box. Using that option seems to make it a bit more difficult with my data file, _.....
"DBSept2016 - first AlanSucces.xlsx" http://imgur.com/D9knusB
Download File: https://app.box.com/s/j4ssuud642l1dr5tp689a080y7ustl7t
_..... , .. on selecting a sheet it gives the columns in a mixed up order c, which is not very helpful ???? . NotWizzardOfWozNotSoGood.JPG http://imgur.com/RVqHmGn

Whereas this , is what I want and I get with the Wizard option checked
WizzardOfWozGood.JPG http://imgur.com/Odot2KS
( in the last screenshot the columns are listed in the order as in the data workbook Worksheet )

But I guess that is just what you get used to.

_...............................

Here another alternative Blog for the Ms Query to get data from another Excel File..
This video was good on the Ms Queery with an Excel File.. , even if it is in Hindi
https://www.youtube.com/watch?v=_P07uPfv-ck
and here again very detailed instructions in English
http://www.exceltrainingvideos.com/g...er-excel-file/
( You will find the video in English there as well , ... I noticed later )
_..............................


With hindsight it was probably not such a good idea for me to use MS Queery, at least the way was doing it. I think I thought it would be clever to record a macro as I did it to get a code to read data from a closed workbook. Later it seemed the MSQueery code was more just for novelty value, as most other ways were better.

https://www.excelforum.com/excel-pro...-xl-files.html


I guess this is all telling me that this queery stuff is for “manual use”, ( Forgive my imprecise terms: I do not have a computer background ).


Anyways, Thanks for the alternative Blog on it, dflak. It’s always helps to have a few to look at.


Alan.

MS-Query really shines when you have an ODBC defined database. It also works well if you are willing to set up a directory for text files that you wish to use as data sources. In both of these cases, it is worth the trouble to define the connection, because once established, you can access any table or file in the database or directory.

In the case of using another workbook, you set up the connection, and it is good only for that workbook. If you move or rename the workbook, you lose the connection. So it is just as much work but not as much value. Generally copying and pasting from another workbook works better. You can put the path and file names in cells in the workbook to make the application more flexible. You will lose the ability to write and use SQL like MS-Query gives, but usually the extra data doesn't hurt and you can deal with it once it is imported.

I'm a big fan of MS-Query, I use it extensively with very complex queries against Oracle databases. I seldom use it to link to another Excel spreadsheet.

Hi
Thanks for that.
I think my code derived from a macro recording of a MA Query to bring data from one ( closed ) Excel Workbook, to another ( open ) one was a bit of a weird thing really. My speed measurements weren’t too impressive, but then some ADO based codes weren’t much better either.

I am checking out putting my data into text files and reading that instead just now, in another Thread or two. ( https://www.excelforum.com/excel-pro...liarities.html )

I might look again at what I get from doing a query on a text file, just to see what happens, although I see no option for .txt files ??
I might be off in another weird direction
( I have no computer background BTW – I am just trying to speed up a personal project with a bit of VBA. Outside of a year or so with VBA I have no composter knowledge at all. So I guess I am likely to hit a few Red herrings along the way.... lol.. )

Alan