Well written Excel help

I thought this post on C Pearson was well written.  It explains name referencing in VBA.

Getting Excel addins to work with Ruby

I was having difficulty with this until I came across this post by bbiker.  The problem is that when you open Excel with a program OLE link it has a different environment to that when you open manually.  Specifically addins might not work and need to be kicked.

I put it to work with the following function in the test code (this is non working code as it requires your own addin name) :

require 'win32ole'

class UpdateAddInFile

  def addin_installed?(ai)
    begin
      puts "Addins: #{ai}? =  #{@xl.AddIns(ai).Installed}"
      @xl.AddIns(ai).Installed=0
      @xl.AddIns(ai).Installed=1
    rescue
      puts "<><>Testing for adding #{ai} caused an exception."
    end
  end

  def initialize(thisFile)
    @xl = WIN32OLE.new('Excel.Application')
    @xl.Visible = true
    addin_installed?("Your Add-In")
    #Satisfying wait as Excel addins get installed
    dummy = @xl.workbooks.Add # Add a new work book
    dummy.Cells(1,1).value = "add in functions will now work"
  end

end

UpdateAddInFile.new('c:\\test.xls')

puts "Waiting for readline"
readline

Excel 2007 slow to open

Just a quick note I have both Excel 2003 and Excel 2007 and Excel 2007 was very slow to open.  A search of the web came up with the invaluable and crazy suggestion to create a blank .XLAM and add it.  Hey presto Excel 2007 responds as quickly as 2003.  Weird.

Excel madness

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

http://support.microsoft.com/kb/q208218/

Draft note about automating Excel scripts

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) <> ""
UpdateSomething

SeriesRow = SeriesRow + 1
Wend
GetConstantsClose
End Sub

Sub 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.