VBA to PHP to MYSQL – Write from Excel to a Web Page
I created a Spreadsheet that sends data from Excel to a website, is processed by PHP, then is written to a MYSQL database. In turn the results are viewable via a webpage, Look Here.
To post a message there also, download the VBA-PHP-MYSQL Spreadsheet.
How it works
1. A user enters their Name and a short Message in Excel. When they click the “Send Comment” button, VBA sends the two variables via HTTP to a PHP file sitting on this server.
View the PHP file (I removed the database connection info and changed the extension to text)
2. The PHP file reads the variables sent from VBA and writes them to a MYSQL database, along with the time they were received.
3. Finally I created another PHP file to read from the MYSQL database and display the results.
Notes
The code still needs work, I’m not sure If I’ll make another update or leave it as is. I’ve provided the files as building blocks to save anyone that has an interest in this an hour or two.
I’ve enclosed both PHP files and the unprotected VBA, here’s a screenshot of the database table I used:
cool, if i ever needed to send stuff to a MySQL, i’d know how to know!!!
Thanks for testing it ross, glad it worked.
I’m enjoying the PHP stuff so I may go in that direction for year 2. I want to start a linkblog/personalblog, however I’ve decided that I want to code it myself, instead of using one “out of the box” like this site. (no idea how long it will take, I’m expectecting 4-8 weeks because of the PHP learning curve)
Don’t want to be a one trick pony, and also want to add some variety to development.
So the posting may go down here next year, but overall I’ll probably be adding more content to the web overall, just spread out.
Sounds good.
Yeah it work flawlessly!
Maybe you cound open it out, get poeple to send in links to you – i’m sure there would be enough coming in! – it could be a good sort of central hub for excel web stuff.
Coding your own blog – humm, sounds very tricky! — good luck!
Actually, 2 evenings into coding the blog and things are going VERY fast. PHP hasn’t been as big of hurdle as I thought.
I hope to have a basic linkblog (no comments etc.) going by early next week, at a different domain.
Having the ability to code a website opens up many possibilities, just not sure what they are yet
per the send in links suggestion, and excel based delicious might be cool.
“We have the technology, were jus not sure what to do with it
With delicious:
You add a POST link to your favorites, then when you are surfing the web, if you see a page you like you click your favorites link, add some tags and hit send.
Bam, it adds your post to delicious.
I’m setting up my blog the same way, so it will be exactly as you described…..it’s a bookmarking tool, plus I’ll be able to comment on the link, and visitors will be able to comment on my comments on the link.
I understand your thoughts about delicious, as I’ve been starting to use it more I had the thought, “heck, this is the same as a blog except I can’t add much content except tags, and I don’t ‘own’ my links anymore.” That was my motivation behind starting a linkblog.
However there isn’t a need to use Excel for this, just a browser. I’ll have to let the thought sit for a few days, get caught up on things, learn more php, and commence packing my stuff up(I’m moving in 2 weeks).
Arrrr no rest of the wicked eh! , hope the move goes well!!!
Thanks
Hi everyone..
im working on a project for my office, and i have used this code.. tested it and it worked.. but when i have pluged it to the program im working on, it started to give me this error message…
Compile error:
User-defined type not defined
any idea????
one other thing.. how can we get the message from the sql to the workbook.. rather than going to the web to read the messages, i want it to show back in the workbook and update every 5 sec let say….
any one???
thanks ppl
got how to get it to work in new workbooks!
ok, in the VB click on ObjectBrowser.
then right click anywhere under the library.
select references
tick the 6th box (microsoft WinHTTP)
and now it will work! yayy
BUT.. still trying to find out how to get data from sql into the workbook!
ciao
i would also be interested in a 2 way data transfer system. i have an access database and i wish to use an online mysql database to hold license information for my clients. For security reasons i would like to have a php file to all the mysql handling. So i want the VBA to post the clients details to the php, the php would then access the database and return what licenses they have. the vba can then take that and activate the licensing. So far this apears to have half of the problem resolved. i can get the vba to post to the php and the php to retrieve information from mysql. However the next step has me perplexed. Mabye i should get the php to write that information to a predetermined variable named text file which the vba could then download and read and then delete.
or if you find a better way please let me know
b
but at any rate great work its great code
Hi, I am not sure if it´s of any help. But here are three files I used to update, write and extract data between excel and mysql when I been updating alot of data.
http://vbaexcel.eu/vba-macro-code/extract-get-data-from-mysql-php
http://vbaexcel.eu/vba-macro-code/update-mysql-database-php
http://vbaexcel.eu/vba-macro-code/write-to-mysql-database-php