My Excel instance has been corrupted by problems with my roaming profile. So now on running a macro when it comes across a Chr function I get the error Compile error: Can’t find project or library.
You just need to unclick the missing reference
Then all will be well.
See also
I run a number of Excel macros which rely on addins eg Fame to work. In order to make sure these happen reliably I run these in the following manner.
On a PC which is the controller I run daily tasks. The list of daily tasks has a graphical user IF that runs the tasks once. It also has an automatic mode which is run from the scheduler.
Eg DailyTasks.au3 –> DailyTasks.exe graphical editor
Then the actually running of the tasks is done by
DoDailyTasks.au3 –>DoDailyTasks.exe
These are compiled to Exes to make the whole thing more reliable and less dependent on a number of files being in the right version and in the right place.
To run an excel macro I have a script that opens a Remote Desktop connection and then runs a child script. This child script may need to be placed in the Program Files subdirectory of the Remote Desktop computer in order to have permission to run automatically.
The child script will open and Excel file and run a specific Macro.
The macro closes Excel when finished. The child script is waiting for excel to close and then closes the remote desktop. This then allows the top level script to continue to the next action.
The Excel file that it runs is usually just a pretty blank file and is a receptacle for VB code to run other Excel sheets. I have enclosed some sample code:
Sub aa_UpdateSomething()
GetConstants
aa_StartUpTrick
Dim i
SeriesRow = 2
While st(SeriesRow, 1) <> ""
UpdateSomethingSeriesRow = SeriesRow + 1
Wend
GetConstantsClose
End SubSub bb_UpdateSomething()
aa_UpdateSomething
Application.Quit
End Sub
Private Sub UpdateSomething
End Sub
The aa_ version is designed to run by hand from the spreadsheet for testing and the bb_ version if designed to be run automatically.
When I updated AutoIT it wiped my include path settings. So here is the script I used to show and update them (I used the great Koda gui builder tool):
#include <GUIConstants.au3>
#Region ### START Koda GUI section ### Form=O:\Automation\Include\AutoitIncludeSettings.kxf
$Form2 = GUICreate("Dialog", 329, 238, 347, 263)
GUISetIcon("D:\003.ico")
$GroupBox1 = GUICtrlCreateGroup("", 8, 1, 297, 193)
$Label1 = GUICtrlCreateLabel("Registry key for AutoIt searching for include files. (seperate with ;)", 24, 20, 230, 30)$Edit1 = GUICtrlCreateEdit(RegRead( "HKEY_CURRENT_USER\Software\AutoIt v3\AutoIt","Include"), 16, 48, 281, 137)
;GUICtrlSetData(-1, "AEdit1")GUICtrlCreateGroup("", -99, -99, 1, 1)
$Button1 = GUICtrlCreateButton("&OK", 65, 203, 75, 25, 0)
$Button2 = GUICtrlCreateButton("&Cancel", 162, 203, 75, 25, 0)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###While 1
$nMsg = GUIGetMsg()
Switch $nMsg
Case $GUI_EVENT_CLOSE
$DoAction = False
Exit
Case $Button1
$DoAction = True
ExitLoop
Case $Button2
$DoAction = False
ExitLoop
EndSwitch
WEndIf $DoAction Then
RegWrite("HKEY_CURRENT_USER\Software\AutoIt v3\AutoIt","Include", "REG_SZ", GUICtrlRead($Edit1))
EndIf
I have been investigating Python as I thought it might have a slightly simpler syntax from Ruby (I am finding it takes time to switch syntax from Mathematica, C#, VB etc) , be more better supported on .NET eg IronPython ahead of IronRuby and better scientific support and some work done on creating an interactive platform like Mathematica/Matlab. Enthought has a prepackaged Scientific distribution.
Anyway just to say not quite as good as I hoped in that Python is the middle of a big change in syntax from 2.x to 3.x, IronPython wont install on my server and it seems to work slightly differently on different computers when driving Excel.
After playing and buying a number of tools the free ActiveState Python distribution seems to work ok. The only snag I have found so far is that the PythonWin debugger really needs to be killed and restarted to work well if you are debugging.
Just wanted to mark down a great down installation tool, Inno Setup that I have used for years and written by Jordan Russell in Delphi.
This web post has great instructions (However last time I clicked it had disappeared (I wanted the macro instructions) so I have pasted it on the bottom)
http://www.fiftyfoureleven.com/weblog/general/outlook-email-shortcuts
but the first link was broken:
http://verychewy.com/archive/2006/04/12/outlook-macro-to-move-an-email-to-folder.aspx
Published in General on Tuesday, June 6th, 2006
Ever wished that you could click alt+1 on your keyboard and have a hilighted message sorted into the correct folder in Outlook? Me too. I've been looking for an easy solution for this for a while but never really dug in to figure it out. Now I have the answer!
So I've been following Merlin Mann's Inbox Zero series from the fringes, interested in the concepts and envious of the many mac only solutions that he provides.
I've wanted a one click solution for organizing my inbox for some time, and looked but not found the answer. Well, spurred on by my new blog (plug), and the fact that I am waaay behind on a lot of e-mail (sorry to those still waiting for a reply) I've found a solution.
What I am talking about here is having the ability to use a keyboard shortcut that shunts a selected/hilighted message in your Outlook inbox into a determined folder. In this manner, you can quickly sort a busy inbox into action/response/received messages, for example.
Note that I am using Microsoft Outlook 2003 on a Windows XP setup to do this. Your mileage may vary.
The process involves three relatively simple steps:
So, lets look at these in details...
A little bit of googling brought me to Outlook Macro to Move an Email to Folder. This really made things easy.
You are going to want to take the code from that example and use the instructions found at Create a macro.
Keep in mind that you need to replace _Reviewed in the following line of code with the folder where you are moving your mail to: Set objFolder = objInbox.Folders("_Reviewed"). Also, the target folder has to be in the inbox.
Once you've made your macro, Outlook (or winXP) will block the macro from running if your security settings are higher than low, which most people's are. To get around this you need to add a digital signature to the macro.
Fine, so where do you get the digital signature file?
I found the answer at OFF2000: Using SelfCert to Create a Digital Certificate for VBA Projects. Follow those instructions (it's quite simple) and then come back here for the next part.
Next we need to add the signature to the macro. Also quite easy, following the instructions at Add a digital signature to a macro project.
Here I refer you to the Outlook 2003 tip o' the day which teaches one how to add a new button to the toolbar and give it a keyboard shortcut. For our purposes, on step 3 of that article you will want to choose macros from the left hand pane and then the macro you wrote from step 1 from the right hand pane.
In the end it was quite simple. I have no idea why it took me so long to find the answers to doing this (this has been on the wish list for awhile). I suspect I had some luck somewhere along the line this morning with some fortuitous googling finding me the right answers :).
