Archive for category Microsoft Office

Exchange autodiscover

We’ve been having a couple of really annoying issues for awhile, but never often enough or serious enough for me to really dig into it. It’s slow today and it happened again so I was determined to figure it out. The solution was annoyingly simple.

Two issues:

  1. When typing up an email to an internal user I would randomly get an error saying MailTips couldn’t be retrieved. It didn’t happen all the time though.
  2. I have heard from a couple of people that when they tried to set their out of office response it wouldn’t let them. But again, it didn’t happen to everybody and was not always an issue for the two people I heard from.

It wasn’t until today when I got the MailTips error again that I realized the two problems were related.

I ran the Outlook tool to test e-mail autoconfiguration and every autodiscover test failed. I swear I ran that before we updated to Outlook 2016 from 2010 and it passed, but maybe I’m losing my mind.

Anyway, the solution was to create a cname record on the DNS (of course!) server. Works perfectly now.

Source: Setup a DNS record for Autodiscover

  1.     On your remote desktop session, select Start / Programs / Administrative Tools / DNS.
  2.     Right-click your domain name and select New Alias (CNAME).
  3.     Under Alias Name enter: autodiscover
  4.     Under Fully qualified domain name (FQDN) for target host enter: autodiscover.emailsrvr.com
  5.     Click the Ok button.

,

1 Comment

Audit Trail – Access 2010 w/SQL Server backend

I don’t do these posts much anymore, but something I just got working this morning definitely warrants one.

I maintain a database here that has an Access 2010 front-end and SQL Server back-end. Since I had to rebuild the database from scratch and actually know how to maintain it I took a few database classes and got a certificate where I learned some SQL and basic database knowledge. Little did I know that I also should’ve learned visual basic. I’ve played around with Access in the past and used it for little personal databases, but I’ve never actually built and maintained one like this. I tried desperately to avoid VB since I’ve never even looked at it before and I’ve always hated programming. But there were times where I couldn’t avoid it, some things you just can’t do without it.

The major thing I’ve been working on this week is creating an Audit Trail so I have record of who, what, and when a person edits or deletes a record from the database. The first link I found was this one http://www.fontstuff.com/access/acctut21.htm. I created the table just as he described it. The first problem I had was, I’m guessing, because it isn’t an Access database – it has a SQL back-end. Again, I knew basically nothing about VB when I started this a few days ago. Running the code it wasn’t even finding the table so I had to use my google-fu to figure what wtf I needed to do. That led me to learning about connection strings.

So this is what the section of my code looks like before it actually gets to the auditing part:

Sub AuditChanges(IDField As String, UserAction As String, Optional DeletedID As String)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
Set cnn = New ADODB.Connection
    cnn.Provider = “sqloledb”
    cnn.ConnectionString = “Driver={SQL Server Native Client 10.0};Server=[server IP Address];Database=[DB Name];Trusted_Connection=yes;”
    cnn.Open
Set rst = New ADODB.Recordset
rst.Open “SELECT * FROM tblAuditTrail”, cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()
strUserID = fGetUserName()

The sections in bold are what I changed or added to the original code. I already have a function in Access, that I found somewhere else online, that automatically enters the username of the person logged into the computer. That’s part of the code I use to track new entries. All the reading I did the last few days said something like that is better than using enviorn(“username”) and since I already had the function I just used it.

So getting that far and using the rest of his code exactly the same at least got it partially working. I was able to track edits with all the information I wanted. But deletes and additions were not being recorded properly. It would record when a new record was added and also deleted, but it wasn’t capturing the primary key value for some reason, which is the entire point. I really don’t understand why it wasn’t working, I downloaded his example DB and looked at it, still couldn’t figure it out.

More research led me to another forum where the person used Martin Green’s code, but had the same problems I was having. It’s the 5th post down the page, by johngachui. He changed some other things in the code that weren’t what I was interested in, so I just focused on the part about the deleted records. He added the DeletedID As String part that I added up above. I didn’t really care about recording additions either since I already do that another way so once I got the code functioning, I removed the parts about recording new records.

Here’s the functioning code as I need it, without recording additions:

Sub AuditChanges(IDField As String, UserAction As String, Optional DeletedID As String)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
Set cnn = New ADODB.Connection
cnn.Provider = “sqloledb”
cnn.ConnectionString = “Driver={SQL Server Native Client 10.0};Server=10.55.12.4;Database=Deconfliction;Trusted_Connection=yes;”
cnn.Open
Set rst = New ADODB.Recordset
rst.Open “SELECT * FROM tblAuditTrail”, cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()
strUserID = fGetUserName()
Select Case UserAction
Case “EDIT”
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = “Audit” Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![RecordID] = Screen.ActiveForm.Controls(IDField).Value
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
.Update
End With
End If
End If
Next ctl
Case Else
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![RecordID] = DeletedID
.Update
End With
End Select
AuditChanges_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
AuditChanges_Err:
MsgBox Err.Description, vbCritical, “ERROR!”
Resume AuditChanges_Exit
End Sub

After making the changes in this code, The person in the 2nd forum also said to do things differently on the forms that call the auditchanges function and this is what I used:

Dim DelID As String

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.NewRecord Then
Call AuditChanges(“cevid”, “EDIT”)
End If
End Sub

Private Sub Form_Delete(Cancel As Integer)
DelID = [cevid]
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then
Call AuditChanges(“cevid”, “DELETE”, DelID)
End Sub

Works great now, on the main form at least. I came across a ton of other forums where people reported issues with this not working on subforms because of the Screen.ActiveForm.Controls(IDField).Value part. I haven’t gotten that far yet. That is my next obstacle.

Sources:

, ,

Leave a comment

Access 2010 set focus on subform control

Just a quick post because I know I’ll need this in the future. In our little database we have here in the office I was wanting a specific control in a subform to become active when someone clicks in any of the blank space on the main form. I couldn’t get the stupid thing to work until I found a forum saying you have to first reference the subform and then reference the control after it.

Not sure it’s your issue, but to set the focus to a subform control from a main form, you need both:

Me.subformcontrol.SetFocus
Me.subformcontrol.Form.somecontrol.SetFocus

I just kept trying to use the one statement on the bottom. Can’t believe I haven’t run into this until now, but there you go. It worked like a charm.

Source:
Access 2010 – Navigation Page, cannot set focus to “inside” form

Leave a comment

Combining Outlook distribution lists

This really should be as easy and clicking a couple buttons, selecting the groups to merge and hitting OK. I can’t believe there isn’t a nice way within Outlook to do this. I have 7 distribution lists that I’m going to merge into probably 2 lists. We use this to send attachments so it’s nice to break it up and not clog up the server all at the same time, but we don’t need this broken up that much.

  1. What you want to do is open each group that you’re wanting to merge together and go to the file menu, then save as.
  2. On the save as type line make sure to select text only (*.txt) and remember where you save it. The text files will look something like this:textfile
  3. Delete the lines at the top so all you have in your text file are the names and email address. Do this for all the text files.
    cleanedtextfile
  4. This next part I find easier to do with excel. In Excel (2010) go to the Data tab and select from text. Select delimited and hit next. I only need to select tab because I’m wanting to remove the email address outside of the parentheses. If you select tab and space it will split them into 3 columns, which isn’t necessary. This way we can delete the entire column at once. Actually, after completing this I had some lists that were formatted differently so I had to play around a bit. As long as you get the duplicated email address in it’s own column that will work.
  5. Highlight column B where the duplication of the address is and delete it.
  6. Now this is where I’m sure there are multiple ways to do this, but I’m not an Excel expert so this is what I figured out. What we need to do next is add a semi-colon to the end of each line to separate the people. What you should have now is something like this:
    Excel
  7. I used a formula to do this. In column B1 type =A1&”;” to get this:
    ExcelMerge
  8. Now click and drag the corner of cell B1 down the column to add the semi-colon the the rest of the fields. There’s probably a macro that can do this, but I have no experience with them so this isn’t too bad. My lists aren’t that large.
  9. What I did next was copy the column with the added semi-colon and paste it into a new notepad file. I did this with all 7 of my groups. You can either just keep appending the one file or put them into multiple, user preference.
  10. Now go into Outlook and create a new contact group.
  11. Click on add members and choose either from outlook contacts or from address book, it gets you to the same place.
  12. Now instead of selecting people from the address book go to your notepad file, copy and paste all of those people separated by the semi-colon into the members box and hit OK.
    PasteContacts

If you’re only merging a couple groups you can probably just paste directly from Excel into the members box. I had 7 to do so it was easier for me to go through notepad first.

I found this info on another site so I’m definitely not taking credit for it. Just adding it to my little notebook since I know I’ll be needing it again at some point.

Source:

 

 

Leave a comment

Excel – save worksheets as separate files

I have an excel file that has about 100 worksheets in it. It was actually an export of a bunch of information. I didn’t realize it would put it all into one huge excel file. I needed to save all the worksheets as their own files and definitely was not about to do this by hand. I found a forum where someone posted some visual basic code that would save the worksheets as their own files, with the worksheet names as their filenames, and save them in the same directory the original file is in – perfect.

Sub CreateNewWBS()
Dim wbThis As Workbook
Dim wbNew As Workbook
Dim ws As Worksheet
Dim strFilename As String

    Set wbThis = ThisWorkbook
    For Each ws In wbThis.Worksheets
        strFilename = wbThis.Path & "/" & ws.Name
        ws.Copy
        Set wbNew = ActiveWorkbook
        wbNew.SaveAs strFilename
        wbNew.Close
    Next ws
End Sub

If you don’t know how to run the VBA code in excel here are the step by step instructions.

You need to have the developer tab enabled first. To do that go to file -> options -> customize ribbon -> in the right column under main tabs check the box for developer.

  1. Go to the developer tab and click on the visual basic button on the left side.
  2. Once the window opens go to the insert menu and select module.
  3. Paste the code you want to run and to run it immediately you can press F5. Boom, it worked perfectly for me, now I have 100 individual files.

Sources:

27 Comments

Word 2010 table of contents all the same number

I have been trying to help someone here print a 72 page report and something really irritating happens with the table of contents at random times. This document is worked on by multiple people so track changes is turned on. I don’t know if that makes a difference for this or if it is just a TOC problem.

I opened the document earlier and printed several copies of it without any problems. Then I stopped to make sure she didn’t want to go over it one more time before I printed the amount needed. When I went back into it to print the rest the TOC changed all the page numbers to 2. What the hell? It was working perfectly fine when I opened it before, I didn’t make any changes before closing it and didn’t save it. It has been happening off and on the entire time this report has been worked on. I haven’t been able to find a pattern for when it happens. It doesn’t make any sense to me at all.

I closed and reopened it I don’t know how many times to no avail. After looking around online this appears to be a common problem with all versions of word. I came across a post on the MS forums about this very issue and found a work-around. While viewing the document press ctrl+end and then ctrl+alt+i. Why does this key combination work? I have no idea. It is annoying enough that I don’t care why, I’m just glad it worked since we have to print 25 copies of this for a meeting this week.

Sources:

1 Comment

Remove a modify password on a Word document

My Dad came to me asking me if I could crack a password for a word document he was trying to edit. He had created a form template and needed to edit it but could not remember the password he used. If all you have is a password set to disable editing of a word document it is pretty easy to remove it.

  1. Open the file and save as html file
  2. Open the html file in notepad or similar text editor
  3. Look for and delete two lines <w:DocumentProtection>Forms</w:DocumentProtection> and <w:UnprotectPassword>B6339E98</w:UnprotectPassword>

    The UnprotectPassword will be a random combination of letters and numbers, depending on what the password to the document is.
  4. Save the file.
  5. Now you can open it in word, you may see an error. But the file will open and you can now edit it. So now re-save it as whatever file extension you want and you are good to go.

Doing this messed up the page size settings of the document, but that is easy to fix. It saved my Dad from having to recreate the document from scratch.

2 Comments