Posts by bradles

Re: Run-time Error When Sending Email

Thanks Sicarii. It's been a long time between re-installing and the pc has started to slow down a bit. I think it might be time to do a re-install of the OS.

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

Sub Mail_small_Text_Outlook()
' Is working in Office 2000-2007
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String


    [B][COLOR="Red"]Set OutApp = CreateObject("Outlook.Application")[/COLOR][/B]
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)


    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"


    On Error Resume Next
    With OutMail
        .To = "[email protected]"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = strbody
        '.Send   'or use .Display
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

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?

Brad

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

[edit]

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?

Brad

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

Thanks Derk.

Do you know if there is any way I can see further variations to the results. Eg, 3 and 8, 2 and 9, 6 and 5 etc?

Brad

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

Sorry Derk,

The sample should now be correctly attached.

Brad

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?

Brad.

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

Thanks Fencliff.

I think I'll add the SaveCopyAs .SelectedItems(1) like in yours. That way it will look seamless to the user.

Thanks again.

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:

Dim dSaveAsDlg As FileDialog
Dim sSaveAsPath as String


'Create path of file being saved
sSaveAsPath = "J:\" & Activeworkbook.Name


'Start FileDialog
Set dSaveAsDlg = Application.FileDialog(msoFileDialogSaveAs)


With dSaveAsDlg
	.InitialFileName = sSaveAsPath
	.Title = "Modified Save As"
	.FilterIndex = 1
	
	If .Show = -1 Then
		.Execute
	Else
		Exit Sub
	End If
	
End With

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:

Sub SaveFileAsAddin()
    Dim dSaveAsDlg As FileDialog
    Dim sSaveAsPath As String
    
    'Create path of file being saved
    sSaveAsPath = "J:\" & ActiveWorkbook.Name
    
    'Start FileDialog
    Set dSaveAsDlg = Application.FileDialog(msoFileDialogSaveAs)
    
    With dSaveAsDlg
        .AllowMultiSelect = False
        .InitialFileName = sSaveAsPath
        .Title = "Modified Save As"
        .FilterIndex = 1
        
        If .Show = -1 Then
            'User has selected to save an AddIn
			If Right(.SelectedItems(1), 3) = "xla" Then
				With ActiveWorkbook
                    .IsAddin = True
                    .SaveAs dSaveAsDlg.SelectedItems(1), xlAddIn
                    .IsAddin = False
                End With
            
			'Not saving an AddIn so execute the FileDialog
			Else
                .Execute
            End If
        Else
            Exit Sub
        End If
        
    End With
    
End Sub

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 from...do you know how i do that?

Thanks.

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.

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

Sorry Dave,

I thought "GetSaveAsFilename not saving as a valid add-in" was an appropriate title...but I see what you mean.

Apologies once again.

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.

Sub Main()


    'Declare a variable as a FileDialogFilters collection.
    Dim fdfs As FileDialogFilters


    'Declare a variable as a FileDialogFilter object.
    Dim fdf As FileDialogFilter


    'Set the FileDialogFilters collection variable to
    'the FileDialogFilters collection of the SaveAs dialog box.
    Set fdfs = Application.FileDialog(msoFileDialogSaveAs).Filters


    'Iterate through the description and extensions of each
    'default filter in the SaveAs dialog box.
    For Each fdf In fdfs


        'Display the description of filters that include
        Debug.Print fdf.Description & " (" & fdf.Extensions & ")," & fdf.Extensions
        
    Next fdf


End Sub

The Output in the Immediate Window:

Microsoft Office Excel Workbook (*.xls),*.xls
XML Spreadsheet (*.xml),*.xml
XML Data (*.xml),*.xml
Single File Web Page (*.mht, *.mhtml),*.mht, *.mhtml
Web Page (*.htm, *.html),*.htm, *.html
Template (*.xlt),*.xlt
Text (Tab delimited) (*.txt),*.txt
Unicode Text (*.txt),*.txt
Microsoft Excel 5.0/95 Workbook (*.xls),*.xls
Microsoft Excel 97- Excel 2003 & 5.0/95 Workbook (*.xls),*.xls
CSV (Comma delimited) (*.csv),*.csv
Microsoft Excel 4.0 Worksheet (*.xls),*.xls
Microsoft Excel 3.0 Worksheet (*.xls),*.xls
Microsoft Excel 2.1 Worksheet (*.xls),*.xls
Microsoft Excel 4.0 Workbook (*.xlw),*.xlw
WK4 (1-2-3) (*.wk4),*.wk4
WK3,FM3 (1-2-3) (*.wk3),*.wk3
WK3 (1-2-3) (*.wk3),*.wk3
WK1,FMT (1-2-3) (*.wk1),*.wk1
WK1,ALL (1-2-3) (*.wk1),*.wk1
WK1 (1-2-3) (*.wk1),*.wk1
WKS (1-2-3) (*.wks),*.wks
WQ1 (Quattro Pro/DOS) (*.wq1),*.wq1
DBF 4 (dBASE IV) (*.dbf),*.dbf
DBF 3 (dBASE III) (*.dbf),*.dbf
DBF 2 (dBASE II) (*.dbf),*.dbf
Formatted Text (Space delimited) (*.prn),*.prn

Re: Measure Time Taken To Find File & Open

Thanks Pike.

I'll check your way out. I like the fact that you can set the InitialView with your apporoach.

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 box...it looks exactly the same but allows me to add my time checks to it.

Sub MyOpenFileProcedure()
	
	[B]'Code here to Timestamp the opening of the GetOpenFilename Dialog.[/B]
	
	'Call GetOpenFilename
	sOpenFileName = Application.GetOpenFilename("Excel Files (*.xls), (*.xls),CSV Files Comma delimted (*.csv), (*.csv),Microsoft Excel workbook (*.xls), (*.xls)" _
                                                    , 1, "Open (Modified)")


	[B]'Code here to Timestamp when they have located their file and clicked "Open".[/B]
	
	'Exit if user cancels
    If sOpenFileName = "False" Then
        Exit Sub
    End If
	
	'Open File
	Workbooks.Open sOpenFileName


End Sub

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

Public WithEvents cbOpen As CommandBarButton


Private Sub cbOpen_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    MsgBox "File open started at: " & Now()
End Sub


Private Sub Class_Initialize()
    Set cbOpen = Application.CommandBars.FindControl(ID:=23)
End Sub


Private Sub Class_Terminate()
    Set cbOpen = Nothing
End Sub

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

Thanks.

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 number....it'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.