Posts by lasw10

    Re: Copying a formula from Spreadsheet to VBA

    Something like this?

    Re: Open worksheets in an xla for editing

    Have you tried copying the .xla file - saving it somewhere and then renaming it - ie changing the extension to .xls?

    General advice on .xlas is to save an .xls duplicate, prior to converting the original to an .xla

    Re: NT User Name to get Outlook information

    a quick aside...

    there are a collection of "environ" variables available in XL - one such variable pulls the active NT


    Dim user as string
    user = environ("username")

    saves a lot of code

    as for pulling the contact info - you would need to loop the global address book entries .... I would think there are examples listed in this section of the forum... if not post back and we can put up some code.

    Re: Automatic emails from Excel

    well rather than use the OnTime method I would suggest you do the following:

    1. Create a Workbook_Open event that calls your email routine.
    2. Ensure your Macro Security Level is set to Low (if you wish this to run without user prompt being required)
    3. Create an Event in the Windows Scheduler to launch your XL file at the required time.

    When the file is launched the open event will be executed which will in turn call your mailing routine.

    you can use ADO in reverse...

    you would simply need to first build a NOT IN Clause to exclude the values already present in your database

    then run some sql

    eg assuming your data in XL is on sheet1 from row 2 onwards...and say the field you will use in your clause is in cell 1 and called "ID"... and you want to put the new data from your .mdb into sheet2

    a very simple example would be as follows (note this is actually MYSQL Sql syntax... I don't use MS Access very much so it may need tweaking)

    prior to generating your mail dialog box use

    ActiveWorkbook.SaveAs "C:\myfilename.xls"

    Depending on what you are doing in this routine you might want to have a look at the SendMail Method in VBA (useful for a simple mailing routine where no message body is required - say just a subject)


    ActiveWorkbook.SaveAs "C:\myfilename.xls"
    ActiveWorkbook.SendMail Recipients:"[email protected]", Subject="Your Subject"

    If this is a repeated task from within another module in another file you may want to turn off warnings about saving over an existing file (if you always save the new file with the same name) by using

    Application.DisplayAlerts = False

    prior to saving the file

    and then restoring the alerts once the file has been closed

    Application.DisplayAlerts = True

    Do you know the OL Contact Item field into which the ID is placed - if it is at all?

    As far as OL goes the normal way to extract the nt id of the user is to look at the Address field - the id is normally at the end (.../cn=lw) where cn is NORMALLY the nt.

    If you do then the simplest solution would be to iterate (via a loop) through each ol.contactitem and test whether the cn=x = environ("username")

    if it is then pull the OL contact item name and place it into B3.

    The alternative may be to query your mail exchange directly (not use OL at all) - I have some code for this but your IT dept should be able to give you something to do this that is specific to your exchange server.

    Let us know what you want to do and we can post up some code.

    OK but surely if "Assessment" counts as 2 hours then you can have a field with a formula in your dataset


    if Assessment appears in Column C of your data range
    then add a new column called Days

    Then add Days to your PT - Summing.

    Assuming you're using Outlook (not OL Express / Lotus Notes)

    You can use something like this (tailor to meet your own requirements)

    Assuming Sheet holding names etc is called Sheets("DATA") and first row containing an address is row 2 and is in column 1 (A)

    see the below thread for guidance…t=22715&highlight=contact

    I would suggest importing the data you require to a data sheet (say called CONTACTS)

    Then create an additional sheet (or userform) with a combobox which has it's listfillrange bound to the data sheet (to populate with names etc...)

    Then use a lookup or index function to return the rest of the data (phone number, address etc...) given the selected name.

    A friend of mine (honest) has just asked me whether it is possible to define the default location when opting to "import data" (via Import External Data) as it sets to "MyDataSources" automatically.

    I have advised on desktop shortcuts, mapped drives etc to the appropriate folder (to speed up selection) but wondered where (if at all - the registry perhaps?) one can set the default location for this tool?

    you can do this in VBA if you wish to... (don't know if you can force it via options etc... don't think so)

    in VBA (Alt + F11 is shortcut) - in ThisWorkbook add

    Private Sub Workbook_Open()
    Application.Cells.VerticalAlignment = xlCenter
    End Sub