Posts by AJW

This ones from Aaron Bloods "Collection of Innovation". (made that bit up myself)

AJW

Quote

Originally posted by Dave Hawley
Hey Tony! I like the way you use the colours to help read your example, very simple yet very neat!

Dave....KISIS

No I'm not getting fresh

KISIS = Keep It Simple I'm Stupid.

Regards

Tony

PS I'm still trying to wrangle that code review job for you. One of the joys of working for a large company is their responsiveness......

Hi Report_2,

If your not familiar with VBA as per Daves reply you could try the example below.

I assume that the report is generated from a master or template ??

If not you will need to play around with the file association a bit to see what happens.

AJW

Can't get the image to attach see below.

For a quick sum of the values click on the column heading to select the entire column. Then at the bottom of your Excel screen right mouse click on the grey bar that tells you things like if num lock and caps lock is on. A list should appear that will let you select Average.

AJW

And attached

MKNov,

Suggest the following a s a starting point.

Create a seperate worksheet within the one workbook for each division (as attached example).

Then using the right mouse click button create a copy of each division sheet as a new workbook.

Next link the copied sheets to the Master divisions sheet and store them wherever.

You then only need to update the Master and the changes will show up in the copied and linked workbooks that are stored where the individuals can access them. (example below)

Regards

AJW

Try plotting them as a single data series and then add the trend line.

AJW

Hi All,

Nothin way special just a little addin that I developed a while back, use a lot and find very handy.

Thought some others may find it usefull for quickly changing the appearance of the Excel environment and moving around.

The addin needs to be placed here:

C:\Program Files\Microsoft Office\Office\Library\My Controls.xla

And activated in the usual way:

Tools > Addins. Then hit the browse button to find "My Controls.xla"

Loading the Addin will bring up a new toolbar with descriptions of what each Icon does when you hover over it. It will also expand your right mouse click menu to include a number of usefull toggle tools for workbook appearance.

Enjoy

AJW

14/05/03

Had a number of people asking how to download this addin so I figured the best place to respond would be in the post itself.

The easiest way I know is to right mouse click on the file and SaveAs from the options listed.

AJW

From: Microsoft Office 2000/Visual Basic Programmer's Guide

COM Add-ins vs. Application-Specific Add-ins
In the previous and current versions of Word, Excel, Access, and PowerPoint, you can use VBA to create add-ins specific to each of those applications. For example, you can create an add-in for Word that builds a custom report from a selected database, and another add-in for Excel that performs a similar task. You save the Word add-in as a Word template file (*.dot), and the Excel add-in as an Excel add-in file (*.xla). Despite the fact that the two add-ins share some common code, you have to create two separate add-ins in order to add functionality to both applications.

Note Outlook and FrontPage do not provide any way to create application-specific add-ins by using VBA.

A COM add-in, on the other hand, can share some add-in functionality and code across applications. The COM add-in project contains a component for each application in which it will run and is also registered for each application. Usually a COM add-in contains some code that is common across all applications and some that is specific to each application. For example, if you build a COM add-in to create a custom report in Word or Excel from a database, the code that accesses the database and retrieves a set of data can be shared. Once you've retrieved the data, you need to work with the Word object model to write the data to Word, and with the Excel object model to write the data to Excel.

The following table lists both types of add-ins and their file extensions.

Add-ins File extensions Available to
Word add-ins (application-specific) .dot, .wll, .wiz Word only
Excel add-ins (application-specific) .xla, .xll Excel only
PowerPoint add-ins (application-specific) .ppa, .pwz PowerPoint only
Access add-ins (application-specific) .mda, .mde Access only
Exchange Client extensions (application-specific) .dll Outlook and Microsoft Exchange clients only
COM add-ins .dll Word, Excel, Access, PowerPoint, Outlook, and FrontPage

Note Prior to Visual Basic 4.0, DLLs could not be created in Visual Basic. Developers used Visual C++ or a comparable language to create DLLs. The .wll and .xll add-in file formats refer to DLLs created in C++ specifically as add-ins for Word and Excel, before these applications included VBA. Likewise, the only add-ins available for the previous version of Microsoft Outlook were Exchange Client extensions, which are DLLs created only in C/C++. Although more recent versions of Word, Excel, and Outlook still support these custom add-ins, you no longer need to create .wll and .xll files or Exchange Client extensions in order to build a sophisticated add-in.

COM add-ins and application-specific add-ins also differ in terms of how the user views and installs available add-ins. In all Office 2000 applications, available COM add-ins are displayed in the COM Add-ins dialog box. This dialog box is the same across all Office applications.

Human Nature - It's called the comfort zone.

Linux is still fairly new to most people and lets face it does the average user know what Open Source means ?

It may have great techi features that we would drool over but I think most people want to plug it in, turn it on and start playing. Unless computer vendors are going to start offering it as an alternative op sys people wont naturally go there. They are used to their tried and tested windoze. Way too comfortable to change without reason.

My two cents worth.

AJW

tolver,

Haven't looked at what John W's addin does but here are a few more tips that may help.

Links are notoriously tricky to find. They are normally based on a cell link to another file. An easy way to find these links is to do a search for .xls or .xla in your workbook. Edit > Search. You can then either delete the link or replace it with a value.

Another type of link which is not common knowledge is range names. Even some of the expert linkbreakers miss this one.

As an example, if I create a new workbook by right mouse click on the page tab > Move or Copy of an existing worksheet it will also bring any and all of the named ranges over with it.

These named ranges can reference back to the original workbook from which the new workbook was created. The only way to clear them out is to use Insert > Name > Define, then work through the list of the names shown and check where they reference. If they refer to a seperate workbook you can either replace the reference location with the same range in the active workbook or if they are not needed delete them.

Hope this helps.

AJW

Quote

Originally posted by XL-Dennis

In general IT-departments tend to treat end-users as "pain in their....." so this sounds really good

:bsmile: :bsmile: :bsmile::bsmile:

Yeah know the feeling well, I now call them Spy-T department as they are monitoring my every move - possibily even this post (do I sound paranoid?)

I think their problem is that I challange their ability with what I can do through Excel VBA and Windows Scripting. Things they keep telling me they can't do because it not possible I go ahead and do anyway. Not exactly the best way to win friends

AJW

Welcome npalex,

If I understand your question rightly I would have used a Radar.

Explain POLAR ?

Thanks

AJW

hehehehe.............. I'm a bad boy !

I've got a little helper called Fred (PowerPup). Fred's job is to be a right royal pain in the ............

Anyway he works on a random number. If the number is right up pops Fred and asks a dumb question like the one willR showed.

Well to cut a long story short Fred does a number of different things.

1. Reverse all the Menu texts ... File = eliF etc. Then after about thirty seconds of panic he resets them. (picked that one up from somewhere?)

2. Clears all entries in the Activesheet...againg after about 30 seconds of panic he restores them.

3. Jiggles - Well not Fred but the screen, using the smallscroll left right up down and looping about 500 times while asking the user if they can speed read (my favorite)

4. Plays hide and seek ! Hides the activeworkbook and then after about 5 seconds asks the user if they have found it yet ? After about 30 seconds if it's still hidden he starts to give them hints like "Checked the menu called Window yet?" After about 60 seconds if they haven't found it he brings it back and asks if the want to play again ? (my second favorite)

All of these events are driven by random numbers so there is no telling when Fred will want to play.

Must warn however it could cause you some problems with your IT department, especially when they spend a week talking to Symantec about a new virus that neither of them can nail.

It's a fairly good indicator that your in trouble, geeez wish people had a sense of humor.

AJW

Hi Sedso,

Saw your cry for help but am a little pressed for time at present.

As a suggestion and in order to assist others with your enquiry could you please attach a copy of the spreadsheet that you need help with (if not to sensitive).

This just give them a better visual of what your trying to achieve.

If I can frre up some time I'll try to look at it myself.

Thanks

AJW

iwilli02,

Hi willi,

I'm going to assume from your post that you have little or no VBA code skills and give a very simple solution.

Jack UK has assumed you want a number of people to use the file at once but the way I read your enquiry is that you want a simple way to access the file without trying to remember wher it is everytime ?

Try this:

1. Launch Windows Explorer.

2. Locate the file in question.

3. Right mouse click on the file and a list of commands appears. There should be one that reads - Send_To > Desktop (create shortcut). Click this command.

This will place and Icon that represents the file on you Desktop (main screen). You can then jump straight to that file from your Desktop without going through the explorer window and trying to remember the path.

Alternatively if you want to access this file from within Excel you can create what is called a hyperlink.

Do this:

1. Select a cell within you worksheet where you would like the Hyperlink placed. Click on the create hyperlink icon (within Excel as per image below).

2. A new window appears called Edit Hyperlink. Use the link to file option and click on the browse button. This will allow you to select the location of the file you wish to link to.

This will place a link to the file in your worksheet so that when you click it it will launch the file.

Trust this helps.

AJW

Sing,

You can insert an Excel Worksheet and Graph as objects into a Userform in VB.

Check it out and have a play around, it may save you a lot of trouble trying to pass data to a seperate application.

AJW

21MSU,

Quick Fix as attached, someone may come up with a better fix given more time.

AJW

Hi All,

This ones probably for the slightly more advanced user.

Ever had to move / copy / replace folders and wanted it to happen external to Excel so as not to freeze up what your working on?? & as an extra bonus include a progress indicator ??

Well check this little trick I picked up along the way using Visual Basic Scripting.

Now for anyone not sure what VBS is..... well it's essentially the same as VBA but designed to run in the Windows environment whereas VBA runs in an application environment.

What to do:

1. Make a *.VBS file (using the attached as a template) that defines what files you want to go where. The attached file can be edited with a right mouse click > edit.

The code in the attached file reads:

Const FOF_CREATEPROGRESSDLG = &H0&
'The destination folder
ParentFolder = "C:\Marketing"
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.NameSpace(ParentFolder)
'The source folder
objFolder.CopyHere "P:\Marketing\Quote Package", FOF_CREATEPROGRESSDLG

As you can see it's fairly straight forward you spec the source folder to copy and destination folder to replace.

2. Using VBA code you can call the *.VBS file to execute:

Sub AutoUpdateQuotePackUpdateMsg()
Shell "wscript C:\Windows\UpdatePackage.vbs", vbNormalFocus
End Sub

What happens ??

When you call the *.VBS file to execute through VBA it runs the code. This causes Windows to prompt you to copy the folder.

The copying takes place in Windows rather than freezing up Excel while folders are moved around.

Anyways I think it's cool.

BTW: There is a lot more VBS code that you can use to do a range of things in Windows. Using VBS from within a VBA enhanced application can dramatically reduce the drain on that applications resources as you passing the workload over to Windows. There's a vast range of VBS code on the WEB, just do a search.

WARNING: VBS code can be and is used as a virus carrier so be carefull of what you download. Also be carefull of what you write as it can do a lot of harm to your system if you get it wrong.

Other than that...........enjoy!

AJW