MySQL-Excel

[miffe]

Is it possible to run query's from MS Excel on 'PC1' having on the same network 'PC_Server' which has Apache, MySQL and Php with MySQL Extensions?If so, how can I run the query and produce results?Thanks a lot,Miffe

[aspnetguy]

okay do you want the query to run from PC1 to a database on PC_Server? Or do you want to run a query from PC_Server to a Excel spreadsheet on PC1?The first one is no problem you just need to know the network IP of PC_Server (can't be DHCP)

[miffe]

okay do you want the query to run from PC1 to a database on PC_Server? Or do you want to run a query from PC_Server to a Excel spreadsheet on PC1?The first one is no problem you just need to know the network IP of PC_Server (can't be DHCP)

The Excel spreadsheet is on PC1, the database is in MySQL on PC_Server, I have Apache installed on the server. As far as I know the server is in DMZplus (set automatically by the network config. because it was creating some kind of problem in DHCP).From inside an excel spreadsheet in PC1 how could I create a query, is this with ODBC? Or can I run a PHP script? or using VBA? or do I install ASP on the server and learn to code in that?My other problem is that I dont want the DB to be accessed from the exterior... but I've got apache running with phpMyAdmin to control the DB... how can I restrict access to the interior of the network (the LAN)... will .htaccess do the trick? or do I apply another kind of password protection to phpMyAdmin, or can I force apache/phpMyAdmin to work only as an interior service and not output to the internet?Thanks -Miffe

[aspnetguy]

I am not too good at networking but I know you can lock down the server to prevent outside access.So you still haven't defined what you want to do you just are telling me what you have setup. Do you want to export your data from Excel to MySql????You would have to run a php script from PC_Server that opend the Excel spreadsheet and parsed it into an sql query to send to Mysql...I just don;t know how to do it.

[miffe]

I am not too good at networking but I know you can lock down the server to prevent outside access.So you still haven't defined what you want to do you just are telling me what you have setup. Do you want to export your data from Excel to MySql????You would have to run a php script from PC_Server that opend the Excel spreadsheet and parsed it into an sql query to send to Mysql...I just don;t know how to do it.

Thx a lot for your help aspnetguy;What I'm actually trying to do is import values from mysql on pc_server onto an excel spreadsheet in pc1... lets say, I have a customers table in a DB on the server, and when the person at the desk wants to make a reciept she will go to a "select customer" drop down box which will execute a SELECT * FROM customers; in the Server DB and return the values into excel, I guess as a result array or something like in PHP.How can I run a PHP-MySQL Query from Excel if in this case it works?

[aspnetguy]

try this http://www.phpfreaks.com/tutorials/114/0.php

[miffe]

try this http://www.phpfreaks.com/tutorials/114/0.php

I quickly read through the tutorial, and to me it seems that this method is actually via the network running this php script, which will execute the MySQL command and produce me a new file .xls (Excel worksheet) and try to download it to my PC. Is there no way to do this in an active worksheet?Is there any way to run PHP scripts in MS Excel, I know it's a bit dumb to ask (open source -vs- non open source) but even so I was wondering if someone had ever come up with an addon or fix, or module or something to try and implement this feature into excel... If this won't work I guess I'm screwed and I'll have to return back to my MS Access DB (after 2 days of work installind a mysql-php-apache server ;P)

[aspnetguy]

AFAIK you cannot download it to an existing spreadsheet but can export the data to a new one.

[redsun]

If you have installed MS Query during the installation of Excel (or Office), you can run a SQL query on any DSN that you have set in your system.Just check your Excel - if you see in the menu 'Data —> Import external data' (sorry, I've non-english version, the name could be a little different), then you are able to load data from your DB.If you don't see it in Data menu, just run the Excel installation again and check MS Query to be installed.A little complication is that you need to set the password to access the DB everytime you run the query. So it's not just like clickin' on one button and query result is here. Good for you is that you can specify where you want do populate the result - on this sheet or new one.All of that assumes that you have access to your DB on the remote server. That should not be so big problem to solve.Redsun