Posts by bradles

    I am trying to run the following code from Ron Debruin's site that sends a simple email:

    I get the following error on the [COLOR="RoyalBlue"]Set OutApp = CreateObject("Outlook.Application")[/COLOR] line when I try to run it:

    [COLOR="Red"]Run-time error '-2147024770 (8007007e)':
    Automation error
    The specified module could not be found.[/COLOR]

    I have ensured that there is a reference to the Microsoft Outlook 11.0 Object Library. I'm sure I've done this in the past and it has worked fine. Does anyone know how I fix this? Am I likely to need to uninstall and reinstall office from scratch?


    Re: Solver Changes Numbers To Decimals Even When Constraining To Integers


    Thanks for the above.

    I've attached an example using more unique numbers. But it's come up with a strange solution. I've asked solver to set a cell to 527 by changing the numbers in E3:E35. It ends up setting it to 10,629 for some reason and I can't work out why. I thought it would have found the two missing numbers, 540 and -13 fairly easily.

    Do you know why this doesn't work?


    Hi All,

    I am experimenting with solver and have attached an example of my problem.
    A3:A12 contains a list of numbers from 1 to 10. B3:B12 are my cells I wish to change to be either 1 or 0. If a cell in column B equals 1 then it multiplies the corresponding number in column A by 1 and puts the result in column C. Column C is summed up in cell C14.

    My solver settings are below:

    Target: $C$14=11
    By Changing: $B$3:$B$12
    Subject to constraints: $B$3:$B$12=Integer

    Effectively, I'm giving solver a number, in this case 11, and I'd like it to return the possible combinations of the numbers 1 to 10 that can sum up to = 11 (eg, 5 and 6, 1 and 10, 4 and 7, etc.)

    When I run the solver though, it sometimes changes the cells in column B to be a decimal, even though I have used the "Subject to constraints" option to ensure they are only changed to integers.

    Is anyone able to tell me what I am doing wrong?


    Re: Not a Valid Add-in Message Loading Add-in

    Quote from shg

    I don't think I was much help, but glad you got it sorted out.

    No, you've been a great help. I have to apologise as I've just realised I don't have this problem sorted out. I am still getting the "Not a Valid Add-in" error when I try to install an add-in that I have saved using the FileDialog to save the .xla file.

    This is my code:

    When I select the Microsoft Office Excell Add-In (*.xla) from the "Save as type" in the FileDialog box and click "Save" it saves the file to my J:\ drive as Book1.xla. However when I try to load that file as an Add-In I get the error message that says it's "Not a Valid Add-in" and it won't load.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Ok. After doing a fair bit more research i've found out that in order to save an addin programatically you need to do the following:

    * Set the IsAddin Property to true
    * Save the file with filetype as xlAddIn
    * Set the IsAddin Property back to false

    Using the FileDialog I would have to check the last three characters of the .SelectedItems(1) property. If they are "xla" then I would have to run the above routine. If not, then I could use the .Execute statement.

    Now my code looks like this:

    Thank you all for your input.

    Re: Not a Valid Add-in Message Loading Add-in

    Sorry SHG,

    I think I've figured it out. I've switched to using the FileDialog object instead of the GetSaveAsFilename. I think if I stayed with the GetSaveAsFilename I would need to let the ThisWorkbook.SaveAs command know what filetype I wanted to save the file as. I wasn't sure how to determine that.

    With the FileDialog object, the execute statement seems to save the file as the appropriate type without me needing to worry about it.

    Thanks for your help.

    Re: Not a Valid Add-in Message Loading Add-in

    Hi SHG,

    I've been looking around the help documentation and the help on this site but I can't figure out how to return what the user has selected.

    Also, probably seems like another question, but will I need to write a function that converts what the user has selected into the FileType (eg., xlAddin) so that I can use it like below:

    ThisWorkbook.SaveAs x, xlAddIn

    Re: Not a Valid Add-in Message Loading Add-in

    Hi Andy,

    So what you're saying is my Save command like below:

    x = Application.GetSaveAsFilename(, "Excel Add-In (*.xla),xla", 1)
        [B]ThisWorkbook.SaveAs x[/B]

    should be:

    x = Application.GetSaveAsFilename(, "Excel Add-In (*.xla),xla", 1)
        [B]ThisWorkbook.SaveAs x, xlAddIn[/B]


    Edit: I assume this means I'll have to find out what file type they have selected from the dialog if there is more than one type to choose you know how i do that?


    Re: Not a Valid Add-in Message Loading Add-in

    Hi SHG,

    Yes it is somewhat rare that I'd ever need to do something like this. Basically, I wanted to measure how long it was taking users to locate the target directory for their save file in our large and complicated directory structures at work. Attaching my own procedure which emulated the SaveAs dialog is the best way i've come up with so far.

    Problem is, I need to ensure they have access to all the normal FileFilters (eg, *.xla, *.txt, *.csv, *.xls, etc) when they are saving documents. It also effects me when I am saving other addins. Currently I have some code that resets the SaveAs onAction when I want to save an addin but it's a bit annoying to have to run it all the time...and if anyone else in the organisation makes an addin, i'd better make sure they can save it as one.

    This is just a temporary measure for a month or so, then I can have the SaveAs and Open command buttons reset back to their defaults.

    I am using the GetSaveAsFilename function to save a file. I have the filefilter as "Excel Add-In (*.xla),xla", however when I save a file this way as an add-in, then try to load that add-in, I get the following error message.

    'C:\Documents and Settings\User1\Desktop\Book3.xla' is not a valid add-in.

    Does this mean that the GetSaveAsFilename is not capable of saving a valid xla file?

    Re: Default GetSaveAsFilename & GetOpenFileName to File Types

    Thanks Dave.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]I modified the code below, originally from the "help" on the extensions property. It loops through and displays a list of all the extensions with a description.

    I could probably modify this to at least build the string for the FileFilters.

    The Output in the Immediate Window:

    I have made my own custom Open and Save As procedures that I attach to the command buttons in the "File" menu. These procedures are using the GetOpenFilename & GetSaveAsFilename functions. My dilemma is that I want the dialog boxes that open from these functions to display the same default FileFilters as you would get when using the built-in Open and Save As commands. eg., "Microsoft Office Excel Workbook (*.xls), *.xls,XML Spreadsheet (*.xml),*.xml,XML Data (*.xml),*.xml, ETC, ETC, ..., "

    I could always build my own custom FileFilter string manually to match the dialog boxes but I was wondering if there might already be a constant within excel that I don't know about.

    Re: Measure Time Taken To Find File & Open

    Hi Pike,

    Thanks for responding. I haven't tried your way of using the FileDialogFilePicker as yet.

    After careful consideration, the way i've decided to approach this is by rerouting the "Open" onAction procedure to one of my own. My procedure is using the GetOpenFilename to mimic the Open dialog looks exactly the same but allows me to add my time checks to it.

    I am using an Open event to set the onAction of the Open command button to MyOpenFileProcedure. I then use a Close event to reset the Open command button. This is being used in an Addin.

    Hi all,

    I am trying to measure how long it takes to locate a file the user wants to open. Eg., from when they click File|Open to when they click the "Open" button in the Open dialog box (after they have navigated through the directories to find their file).

    I have approached this by using a class module and withEvents. This uses the Click event to trap when they have hit the File|Open button from the File menu and timestamp it.

    class module: EventFileOpen

    The above code is started in the Open event of ThisWorkbook:

    Public cbOpenFileButton As EventFileOpen
    Private Sub Workbook_Open()
        Set cbOpenFileButton = New EventFileOpen
    End Sub

    My next challenge would be to capture the exact moment they click the "Open" button (in the Open dialog box) after they have found their file.

    Does anyone know if it is possible to capture that event or any other way that I could measure the time taken for a user to find the file they wish to open.

    Re: Conditional Formatting With R1C1 References


    She's my little baby.

    I ended up having to use the offset formula for this problem as I wanted to highlight the higher number...hence, I had to work backwards...if that makes sense. I am moving from bottom to top up a list of numbers trying to find the highest number before a reduction occurs. If one occurs, highlight the higher number...not the lower one.

    I had to use the following formula in the top cell of a selection. eg, if A1:A10 was selected then:
    =IF( AND( ISNUMBER(OFFSET(A1,-1,0)), ISNUMBER(A1) ), OFFSET(A1,-1,0)<A1,0)

    This allowed for the fact that you couldn't compare the -1 offset from A1. It also allowed me to format the higher's a little tricky to wrap your head around why I am doing this...I'm getting lost in it myself. :yikes:

    Unless anyone can see an easier way. I've been hard at it today and am going a bit stale so it is possible there is a better way.