www.website-watcher.com

[phillfri]

I appreciate the fact that AM-Notebook has a built in spreadsheet, but I find that I honestly miss the capabilities that Excel can provide me with both in terms of calculations and VBA. But there are some quirks about Excel that can be irritating when trying to integrate its use with another application like AM-Notebook. I thought I'd jot down these notes in the hopes that someone else may find them useful. Note that I use Excel 2003, but these comments are applicable to Excel 2007 also.

Future Development Suggestion: It would be nice if one could have quick access to launch external tools. Either a keyboard shortcut, or better yet, the ability to add the external tools as a button to the Tools toolbar as one can other items on the Tools menu. It would also be nice if one could run a vbscript file from the external tools option as well as executables. That would accomodate a bit of customization in what one can do directly from AM-Notebook. I don't know if either of these suggestions is possible or not. I really like the potential of this program as an organizational tool :>)


Get Rid of the Excel Splash Screen
Use the /e option at the end of the parameters (after the file name - outside of the quotes around the file name) that you enter into the external tools option in AM-Notebook.

Accomodate Multiple Instances of Excel
Some may not want to do this, but personally I find the fact that an Excel workbook is always opened in any instance of an Excel application that happens to be running when you open the workbook to be a pain in the you know where. I want to utilize Excel for a multitude of purposes, and I don't want multiple workbooks opening up in the same instance of Excel where vba code in the different workbooks may end up conflicting. The following tip (applicable to all Microsoft Office applications) will allow opening each Excel workbook in a seperate instance of the Excel application. For instance, I run an Excel timesheet that I launch from AM-Notebook. I don't want it popping up inside of another Excel workbook that I may have open. An added benefit of this approach is that you can size and place each of your Excel workbooks as you wish with a little vba code.

Open File Explorer (My Computer) and Choose Folder -> Options -> File Type. Find XLS (or other Excel file extension) and select it, then click the advanced button. Now Select the “Open” Action and choose the Edit button. Shutoff DDE by unchecking it. Then edit the Action line to read something like the entry below (note the "%1"). And note there must be a space between the /e and the following quotation mark. Ok your way out of File Explorer Options. Then go to the Excel application Advanced Options and check the "ignore other applications that use DDE" checkbox.

"C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" /e "%1"

Disable Office document scanning in AVG Antivirus Software
This one is a pain in the neck. There is no way on a document by document basis to prevent Microsoft Office from scanning every document you open in an Office application if you have antivirus software installed. This can slow down opening Excel files considerably. Its an annoyance in my book, since documents are scanned when they are downloaded from the web anyway, or when they arrive as an email attachment. Some people will argue with me on this one I'm sure. But in any event, almost all the current antivirus software has a "Microsoft Office Plugin" that gets installed. You have to disable that plugin to get rid of virus scanning on Microsoft Office documents when you open them. I use AVG free, and the following steps can be taken. If you choose to do this you'll have to investigate how your antivirus software handles MS Office documents. Some virus software will have a simple option that you can check. Otherwise the process will probably be very similar to that of AVG free.

You can reconfigure the Options for AVG by...
1. Download the latest AVG Free installation package from [free.grisoft.com]
2. Run the AVG Free install file
3. Choose the Add/Remove components option and follow the setup wizard until you reach the Component Selection area
4. Uncheck Plugin for MS Office and finish the wizard.
5. Restart your computer then test...

VBA Helpers - All this code goes into the ThisWorkbook VBA module

This code runs when the workbook opens. It positions and sizes the workbook as predefined by the SaveAppPosition macro (see below).

[Martin Aignesberger]

First, thanks for your post and your tips.

[phillfri]

Thanks Martin. I will explore that capability! I think it was the external tools "Files of type" dropdown box being limited to "*.exe" rather than having the general "*.*" option in addition that threw me. I should have just tried it :>)

[Martin Aignesberger]

[phillfri]

Thanks to Martin educating me on the ability to run vbs from AM-Notebook, I created a small helper script to launch Excel files. Hope someone finds it useful.

VBS Excel Helper

Place the code below in a text file and name the file something appropriate like "AMApps.vbs". Personally, I created an "Excel" folder under the AM-Notebook folder, and that's where I place all my Excel helper workbooks and this VBS file (facilitating ease of backups). To utilize the vbs file from AM-Notebook, select it in the "Program" text box when configuring Excel as an external application to use as a tool. Or just enter its full path name (without quotes), e.g. X:\AM-Notebook\Excel\AMApps.vbs . Then in the Parameters text box enter the full path name of the Excel file you wish to open (in quotation marks), e.g. "X:\AM-Notebook\Excel\Timesheet.xls". This assumes that the Excel file extension(s) is associated with the Excel application. If its not, this script won't work.

You get a couple benefits by utilizing the script. First, you no longer need the /e Excel command line parameter to suppress the Excel splash screen. The splash screen isn't invoked when an Excel application object is started by VBS. Second, VBS always starts an Excel application object in invisible mode. This means you won't see the Excel workbook you're opening jumping around the screen as it positions itself to its pre-defined start position and size while the Workbook_Open code is running (see earlier posting). The last thing to be done is to make the Excel application visible in the Workbook_Open code. Add ".Visible = True" as the last effective line to that code, after the ".Screenupdating = True" and before the "End With".

[pss]

Hi!

It excuses my ignorance, but… What is the VBS?

In my normal management with AMNB, Copy/paste excel spreadsheet and I open to applications and archives of the disc with the function “Link to File” (unfortunately, the program does not allow to insert archives like Ole Objects).

What additional benefit could obtain with this function when implementing it?

You could explain better and more slowly the utilities of the function and as they are handled?

Also use OpenOffice, This function, also can be implemented for OpenOffice?

I am a simple user of AMNB and use the program to store notes, articles, information of the Web.

I will thank for your commentaries

Thanks

You could add some screenshot of example in the forum?
_________________
AM Notebook PRO v.4.1.1 final
Windows XP Professional sp2 (spanish version)
Mozilla Firefox 2.0 Browser (spanish version)
Suite OpenOffice 2.1 (spanish version)

[phillfri]

pss - VBS stands for "Visual Basic Script". Its very similar to VBA. It comes as a part of Windows XP and text files with a .vbs extension will be run by VBS when they are launched.

The purpose of the simple VBA and VBS code I have posted is to try to make an Excel workbook open and close from AM-Notepad (or another program) much as the Am-Notepad "Address Book" and "Alarm" tools do.

Because of the way Excel is set up during its installation, all workbooks that are opened get crammed into one instance of the Excel application. This can cause problems with conflicting VBA code in different workbooks if one uses a lot of VBA code - which I tend to do. (And I pick a lot of that VBA code up on the internet - so its not all coordinated to avoid conflict.)

Likewise, Excel will only remember the last screen position it was in when it was closed - which is usually full screen mode for most people. So when Excel is re-opened it will go back to that last position. When I use Excel as a helper I often just want it to quickly launch a small window at a certain place on my screen where I can quickly record a piece of information I want to record and save and then close it again.

All of the VBSBA code and "tweaks" that I've mentioned are intended to make each Excel workbook be able to act like a standalone utility. For me, its a question of speed and how smooth my workflow is when I use an Excel workbook as a helper/utility. For other people this may have no value at all.

[pss]

Hi phillfri!

Thank you very much by your explanation, now I understand somewhat more. Very Interesting add-on!

Thanks again!

patricio
_________________
AM Notebook PRO v.4.1.1 final
Windows XP Professional sp2 (spanish version)
Mozilla Firefox 2.0 Browser (spanish version)
Suite OpenOffice 2.1 (spanish version)