Posts by lasw10

Re: Use wildcard in function

just use FIND

eg

=IF(ISERROR(FIND("@",A1)),"",A1)

Re: Copying a formula from Spreadsheet to VBA

Something like this?

Sub PASTE_FORMULA()


Dim formrw, cnt, frw, lrw As Integer
formrw = 2 'row containing formula
cnt = Cells(formrw, 9) 'cell containing number of rows to paste formula
If cnt > 0 Then
    frw = formrw + 1 'set first row of range requiring formula
    lrw = formrw + cnt 'set last row of range requiring formula
Else
    'if count = 0 (I2) then exit routine
    Exit Sub
End If


'copy formula
Cells(formrw, 1).Copy
'paste formula to predetermined range
Range(Cells(frw, 1), Cells(lrw, 1)).PasteSpecial xlPasteFormulas


End Sub

Re: Enter key selecting item in list box on a user form

Have a look at ONKEY method in VBA Help.

However, that said, I don't personally think that using the Enter Key for this task is a wise idea given it is set at application level...

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

eg

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: NT User Name to get Outlook information

oops...

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.

Re: Activate entire row, of selected cell

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Rows(Target.Row).Select
End Sub

Re: Open Outlook Mail after click on button

try
Link = "mailto:[email protected]?subject=Help"

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)

Sub Marine()


Dim c as adodb.connection
Dim r as adodb.recordset
Set c = new adodb.connection
c.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=\\GCD01F4500\DATI\PUBBLIC


Dim s1, s2, notinstr as string
Dim rw as integer


s1 = "sheet1"
s2 = "sheet2"
notinstr = ""
rw = 2


Sheets(s1).Select


Do Until Cells(rw,1) = ""
     notinstr = notinstr & ", "
rw = rw + 1
Loop
notinstr = left(notinstr,len(notinstr)-2)


sq = "Select * from yourtable where ID not in (" & notinstr & ")"
set r = c.execute(sq)
If r.eof = false then
     Sheets(s2).Select
     Cells(2,1).CopyFromRecordset r
     r.close
End If
c.close


End Sub

as far as i am aware Mac XL does not support VBA.

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)

eg

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

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

ie

if Assessment appears in Column C of your data range
then add a new column called Days
=IF(C1="Assessment",.25,0)

Then add Days to your PT - Summing.

I would hazard a guess it would be simpler to create a new field in your source data with the formula

=hours/4

and then use this new field in your pivot table.

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)

Sub Mail_It()


Sheets("DATA").Select


Dim rw as integer
rw = 2


Set aOutlook = GetObject(, "Outlook.Application")
Do Until Cells(rw,1) = "" 
Set aEmail = aOutlook.CreateItem(0)
'set Importance
aEmail.Importance = 2
'Set Subject
aEmail.Subject = "Your Subject Title"
'Set Body for mail
aEmail.Body = Cells(rw, 1) & "," & vbLf & vbLf & "This is a Private Message"
'Set attachment if required
'aEmail.ATTACHMENTS.Add ActiveWorkbook.FullName
'Set Recipient
aEmail.Recipients.Add Cells(rw, 1)
'Send MailaEmail.Send 
rw = rw + 1
Loop


End Sub

see the below thread for guidance

http://www.ozgrid.com/forum/sh…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?

your called sub requires the variable StrCs to be parsed

eg

Call Flsh1("string")

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