Jump to content

MySQL-Excel


miffe
 Share

Recommended Posts

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

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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)
Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...