Deal with the Outlook permission popupIf you use Excel to access Outlook stuff like addresslists, you are probably frustrated by the popup that asks the user to provide permission to access Outlook for 1 minute (or 10 minutes). This can be quite confusing for users. I don't usually write this kind of program, but I needed something that would wait for the Outlook popup, and answer yes on behalf of the user. It couldn't be done by Excel, because Excel would be frozen waiting for Outlook to respond at the time. So what you do, is launch this program a few seconds before you ask for Outlook information, and it will look out for, and fill in the Outlook dialog, and then end itself.If you are interested in how it works, it looks for the Outlook dialog title, and if it finds it, it gets the "handle" of the controls on that dialog, like the permission checkbox and Yes button, then it sends them keystrokes (this is better than SendKeys, which might be sent to another window if it pops up at the wrong moment. There is one little complication - programmatically pressing the Yes button is not enough - Outlook seems to want to be sure someone actually pressed a key - so my program also puts and enter keystroke in the keyboard buffer, which satisfies Outlook. This is a VB program, so it needs VB6 runtimes. Submit webforms using only codeSee other link below which uses the inet control. This goes one better, pure VBA. Get it here.Trap webcam images in ExcelMost webcams refresh automatically, and put the picture in the same filename over and over. You can set Excel to download that picture periodically and put it in a picture box on your worksheet, effectively creating your own webcam. Here's an example.
Send email using SMTPI have another example below showing how to send email using Outlook and pure html. This one shows how to use the Simple Mail Transfer Protocol. It requires the Microsoft Winsock control (included in the zipfile). The advantage is you can customise everything about the message, and it can be sent totally in the background without bothering the user, which is ideal for things like automatically reporting out of date virus files (which is why I wrote it). Get it here.Excel doesn't run properly in IE? Want to distribute multiple files?1. If you are finding that workbooks crash when they are launched from Internet Explorer, because IE runs Excel inside itself, and VBA doesn't take kindly to this, or2. You would like to distribute multiple files to users and they don't all know about zipfiles yet, or 3. You would simply like to compress the workbooks you post on your website, then ..consider using a self-extracting EXE file. I have uncovered a really good freeware utility for this. What I like is that you can tell it to extract all files to a specified folder, eg the Temporary file folder, and run, without having to prompt the user. This minimises the amount of knowledge required by the user. In addition, it will run Excel stand-alone, rather than embedded in IE. So why not use zipfiles? They have to be downloaded and the file(s) extracted and run manually. Believe it or not, many ordinary users have trouble with this. So the self-extracting option can be valuable. I have posted the utility, and a sample workbook to show how I use some of its features here. Create links to web pages and Word helpfiles from buttons or other Excel objectsThis workbook shows how to create links in your workbook to web pages and/or Word helpfiles without using cell hyperlinks. It shows how to use Word bookmarks to create context sensitive help with a simple Word helpfile.Get it here.
Update your user's Excel app from your websiteHow would you like your Excel app to intelligently update itself using your website, or pass your messages on to the user? Excel can connect itself to the net, if a connection exists, and tell the user if something needs to be done or if something new is available. The most difficult part is getting your web pages to respond intelligently to your Excel requests. I use (Microsoft) ASP which is just a simple form of VBA. If you know ASP, this will be a piece of cake. If not, you may find it more tricky, but it's worth it.Get a sample workbook here. Note the updating and links won't work because they were designed for a specific intranet.
Create links to web pages and Word helpfiles from buttons or other Excel objectsThis workbook shows how to create links in your workbook to web pages and/or Word helpfiles without using cell hyperlinks. It shows how to use Word bookmarks to create context sensitive help with a simple Word helpfile.Get it here.
Submit webforms from ExcelHave you ever wanted to get data from a webpage which requires a form to be filled in? If you're a developer yourself, why not consider putting the form in Excel and submit it directly from there? Excel has much better presentation, editing and validation features than HTML, after all. And if you want to get information such as the user network name, it's dead easy from Excel, but murder from a webpage. I've written a workbook which feeds the results of a survey back to a webpage. Get it here (NB it uses the Inet control which you should have already).
Download files without web queriesWeb queries are useful but pretty clumsy. You can download pages and files without them, fortunately, using API calls. Here's an example.
Update websites from ExcelWe wrote a spreadsheet for a client which calculated investment unit prices each month. He wanted to use these figures to update the chart and figures on his website. So we wrote some code to do it at the push of a button. It creates new charts, saves them as GIF picture files (did you know Excel could do that?), uploads them to the specified website, and edits the webpage templates and uploads them too. Get it here.
Download files without web queriesWeb queries are useful but pretty clumsy. You can download pages and files without them, fortunately, using API calls. Here's an example.
An Excel web browserIf you use IE, you have access to a fully functional web browser control, but some of the commands are pretty obscure (eg printing from it). Here's my effort with quite a few features built in.
An Excel web serverYes, a WEB SERVER. Run this, and other people on the net[work] can download files from a folder you specify. I use it within my company when people in remote offices need files off my system, because it works fine within a company network and is not exposed beyond the company firewall.It also works on the internet when I run it from home, but I wouldn't leave it running too long as it probably isn't secure from hackers. You could work this up into a fully fledged chat tool or ICQ, but even I accept that Excel has reasonable limits < Note - you need the Inet control to run this.
|