Excel Removing Spaces and Other Characters
Posted by Matt
The first approach is to use the SUBSTITUTE function. Let's say that a policy number is in cell A5. In cell B5 you could use this formula:
=SUBSTITUTE(A5," ","")
The result is that cell B5 contains the policy number with all the spaces removed.
The second approach works well if you have a lot of cells containing spaces, and you want to remove them in one step. Create the following macro:
Sub NoSpaces()
Dim c As Range
For Each c In Selection.Cells
c = Replace(c, " ", "")
Next
End Sub
Select the cells you want to modify, and then run the macro. It examines each cell in the selected range, removing any spaces in that range. The result is then placed back in the same cell.
Active Directory/Exchange 2007/2010 Bulk Import Contacts From CSV, Import-Csv & New-MailContact
Posted by Matt
Using Quest Software's Active Directory cmdlets Powershell extensions (http://www.quest.com/powershell/activeroles-server.aspx), I wrote a simple Powershell script to work through a csv file with users.
The powershell script to run through the csv is below:
Import-Csv 'addr.csv' | `
ForEach{
New-MailContact $_.dname `
-DisplayName $_.dname `
-FirstName $_.fname `
-LastName $_.lname `
-organizationalunit "Users/Contacts" `
-Alias $_.alias `
-ExternalEmailAddress $_.email
}
An example of the CSV:
dname,fname,lname,email test,test,test,test@test.com
Installing Backup Exec 2010 remote agent on Linux (Debian)
Posted by Matt
1. Create a folder to keep all RALUS files and copy the archive into it :
- mkdir /root/BE
- mkdir /root/BE/RALUS2010
- mv RALUS_RMALS_RAMS-2896.9.tar.gz /root/BE/RALUS2010/
- cd /root/BE/RALUS2010
2. Unpack the archive provided by Symantec (this can be found on the BE 2010 disc under the LinuxUnixMac folder)
- tar xzf RALUS_RMALS_RAMS-2896.9.tar.gz
3. Stop the RALUS service if it is already installed and runnig
- /etc/init.d/VRTSralus.init stop
4. If you are under a 64 bit Linux:
- Extract debian package :
tar xzf RALUS64/pkgs/Linux/VRTSralus.tar.gz
- Install debian package :
dpkg -i VRTSralus-13.0.2896-0.x86_64.deb
- Start installation :
./RALUS64/installralus
5. If you are under a 32 bit Linux:
- Extract debian package :
tar xzf pkgs/Linux/VRTSralus.tar.gz
- Install debian package :
VRTSralus-13.0.2896-0.i386.deb
- Start installation :
./RALUSx86/installralus or ./installralus
6. Be sure to answer all questions correctly especially the one about the host server (XXX.XXX.XXX.XXX), you must give the IP of the Backup Exec server.
7. Do a restart of the RALUS Backup Exec agent
- /etc/init.d/VRTSralus.init start
A) If you get "ERROR: VXIF_HOME is invalid. It must point to the root of VxIF. Exiting ...", simply edit ./RALUS64/installralus, and change line 3 :
- from : VXIF_HOME=../;export VXIF_HOME
- to : VXIF_HOME=/root/BE/RALUS2010/;export VXIF_HOME
B) If you get "./RALUS64/installralus: line 50: ../perl/Linux/bin/perl: No such file or directory", simply edit ./RALUS64/installralus, and change line 50 :
- from : ../perl/$OS/bin/perl -I.. -I$PATH -I$VXIF_HOME -I../perl/$OS/lib/$PERL_VER ../installralus.pl $*
- to : ../perl/$OS/bin/perl -I.. -I$PATH -I$VXIF_HOME -I../perl/$OS/lib/$PERL_VER ./installralus.pl $*
- or to : perl -I.. -I$PATH -I$VXIF_HOME ./installralus.pl $*
- (to be clear, remove one dot in front of"/installralus.pl", keep only one dot instead of two)
Enclose contents of a cell in quotes in Excel
Posted by Matt
I needed a simple way to enclose the contents of a cell in quotes. A simple VBA sub that you can throw into the "this workbook" visual basic code section of Excel (Developer tab needs to be visible in '07 and '10, '03 is under Tools -> Macros) was the easiest thing to do.
Below is a vba sub that all you need to do is change f to the column you want to enclose in quotes and then just run the project from the VB code window.
Sub makequotes() mc = "f" For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row If Cells(i, mc) <> "" And Left(Cells(i, mc), 1) <> """" Then Cells(i, mc).Value = """" & Cells(i, mc) & """" End If Next i End Sub
Active Directory Bulk User Import From CSV, Import-Csv and New-QADUser
Posted by Matt
Using Quest Software's Active Directory cmdlets Powershell extensions (http://www.quest.com/powershell/activeroles-server.aspx), I wrote a simple Powershell script to work through a csv file with users.
The powershell script to run through the csv is below:
Connect-QADService -Service "domain.com"
$Users = Import-Csv "C:\users.csv"
foreach ($User in $Users)
{
New-QADUser -Name $User.Name `
-ParentContainer $User.OU `
-FirstName $User.FirstName `
-LastName $User.LastName `
-UserPassword $User.userPassword `
-SamAccountName $User.sAMAccountName `
-UserPrincipalName $User.UPN
}
foreach ($User2 in $Users)
{
Set-QADUser $User2.UPN `
-PasswordNeverExpires $true `
-UserMustChangePassword $false
}
An example of the csv file is below:
Name,FirstName,LastName,sAMAccountName,UserPassword,OU,UPN John Smith,John,Smith,jsmith,password,"OU=Department,OU=Users,DC=domain,DC=com",jsmith@domain.com
Connecting to MSSQL Express Remotely, Enabling Remote Connections SQL Express
Posted by Matt
If you’re trying to connect to SQL Server 2008 Express remotely without enable remote connection first, you may see these error messages:
- “Cannot connect to SQL-Server-Instance-Name
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 – Server doesn’t support requested protocol) (Microsoft SQL Server)” - “Cannot connect to SQL-Server-Instance-Name
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server)” - “Cannot connect to SQL-Server-Instance-Name
Login failed for user ‘username‘. (Microsoft SQL Server, Error: 18456)”
To enable remote connection on SQL Server 2008 Express, see the step below:
- Start SQL Server Browser service if it’s not started yet. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer.
- Enable TCP/IP protocol for SQL Server 2008 Express to accept remote connection.
- (Optional) Change Server Authentication to SQL Server and Windows Authentication. By default, SQL Server 2008 Express allows only Windows Authentication mode so you can connect to the SQL Server with current user log-on credential. If you want to specify user for connect to the SQL Server, you have to change Server Authentication to SQL Server and Windows Authentication.
Note: In SQL Server 2008 Express, there isn’t SQL Server Surface Area Configuration so you have to configure from SQL Server Configuration Manager instead.
Step-by-step
- Open SQL Server Configuration Manager. Click Start -> Programs -> Microsoft SQL Server 2008 -> Configuration Tools ->SQL Server Configuration Manager.
- On SQL Server Configuration Manager, select SQL Server Services on the left window. If the state on SQL Server Browser is not running, you have to configure and start the service. Otherwise, you can skip to step 6.
- Double-click on SQL Server Browser, the Properties window will show up. Set the account for start SQL Server Browser Service. In this example, I set to Local Service account.
- On SQL Server Browser Properties, move to Service tab and change Start Mode to Automatic. Therefore, the service will be start automatically when the computer starts. Click OK to apply changes.
- Back to SQL Server Configuration Manager, right-click on SQL Server Bowser on the right window and select Start to start the service.
- On the left window, expand SQL Server Network Configuration -> Protocols for SQLEXPRESS. You see that TCP/IP protocol status is disabled.
- Right-click on TCP/IP and select Enable to enable the protocol.
- There is a pop-up shown up that you have to restart the SQL Service to apply changes.
- On the left window, select SQL Server Services. Select SQL Server (SQLEXPRESS) on the right window -> click Restart. The SQL Server service will be restarted.
- Open Microsoft SQL Server Management Studio and connect to the SQL Server 2008 Express.
- Right-click on the SQL Server Instance and select Properties.
- On Server Properties, select Security on the left window. Then, select SQL Server and Windows Authentication mode.
- Again, there is a pop-up shown up that you have to restart the SQL Service to apply changes.
- Right-click on the SQL Server Instance and select Restart.
- That’s it. Now you should be able to connect to the SQL Server 2008 Express remotely.
Excel opens up debug window when opening, when opening Excel have to minimize in order to display
Posted by Matt
There are alot of issues that can cause this. In this specific case it is related to Groupwise being installed and having the integrations enabled at one point or another. Even after disabling integrations I manually had delete the GW XLS file in the two following locations:
Go to C:\Program Files\Microsoft Office\Office12\XLSTART
C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART
VBSCRIPT that logs computer name, username and date to a MySQL DB, writing to MySQL DS using VBSCRIPT
Posted by Matt
So I wanted an easy way to get a workstation inventory (this is not the easiest way but it provides some more functionality that I wanted) and an easily accessible log of who is currently logged in on what machine and a date/time log of when users log in where on my orgs domain.
I setup a MySQL DB with a table that contained 5 fields: id, workstation, username and datetime. Id is an auto increment primary key field, workstation and username are text and datetime is type datetime.
In order for the vbs to be able to work with MySQL, I first had to deploy the MySQL ODBC Connector install (msi) via group policy. Once that was done I simply created the script (shown below) and then added it to the logon scripts in a new group policy and applied it to Domain Users.
Now i have a nice sortable MySQL log 
!
Option Explicit
On Error Resume Next
Dim WshNetwork
Dim strComputer
Dim strUser
Dim objDB
Dim objDBConn
' SET NETWORK OBJECT
Set WshNetwork = WScript.CreateObject("WScript.Network")
' GET COMPUTER AND USERNAME
strComputer = WshNetwork.ComputerName
strUser = WshNetwork.UserName
' SET DATABASE INFO
Set objDB = CreateObject("ADODB.Connection")
objDBConn = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=server; DATABASE=database; UID=username; PWD=password;"
' OPEN CONNECTION TO DATABASE
objDB.Open objDBConn
' SET AND EXECUTE SQL INSERT
Dim strSQL
strSQL = "INSERT INTO logons (computer_name, user_name, date) VALUES ('" & strComputer & "','" & strUser & "', CURDATE())"
objDB.Execute strSQL
' CLOSE DB CONNECTION
objDB.Close
' CLEAR OBJECTS TO FREE MEMORY
Set WshNetwork = Nothing
Set objDB = Nothing
Setting up Outlook group policies, configuring safe senders
Posted by Matt
When you are in an Active Directory network environment, you can set Outlook policies to enforce settings on a specific or a group of users or computers. This is mainly to be used to change or limit the default behavior of Outlook in a corporate environment but can also be useful in some home environments. For instance, as a home user you might want to set policies on what your children can and cannot do in Outlook.
This article explains how you can set Group Policies for Outlook and which tools are needed for it.
Download the Policy Templates
The Policy Templates and documentation are included in the Office Resource Kit. Use the links below to download the Resource Kit for your Outlook version.
- Outlook 2000 ORK
- Outlook 2002/XP ORK
- Outlook 2003 ORK
- Office 2007 Policy Templates
- Office 2010 Policy Templates
Updating the Office 2003 Policies
When you installed the Office 2003 Resource Kit you must update the policy templates.
- Download the Outlook 2003 updated policies.
- Run the installer and accept the License Agreement
- Type a location to extract the file to. For example C:\Office2003ORK
- Open the folder in Explorer
- Select all the ADM-files (11 in total) and copy them to C:\Windows\inf
- You'll get prompted if you want to overwrite the files. Choose "Yes to All"
Install the Office 2007 Policies
When you downloaded the Office 2007 policy templates you must install them first.
- Run the installer and accept the License Agreement
- Type a location to extract the file to. For example C:\Office2007Policies
- Open the folder in Explorer
- Windows XP
Select all the ADM-files (15 in total) from the ADM\en-us folder (or your localized language when available) and copy them to;
C:\Windows\inf - Windows Vista and Windows 7
Select all the ADMX-files (15 in total) from the ADMX folder and the ADML-files (15) from the en-us folder (or your localized language when available) and copy them to;
C:\Windows\PolicyDefinitions
If you want to control a Active Directory network environment, then you can also copy them to the Sysvol\PolicyDefinitions folder on a domain controller.
- Windows XP
- If you get prompted if you want to overwrite the files. Choose "Yes to All".
If you get prompted by User Account Control (UAC), press Continue or provide Administrator level credentials.
Install the Office 2010 Policies
When you downloaded the Office 2010 policy templates you must install them first. While there are separate packages for the 32-bit and 64-bit AdminTemplates, this only applies to the Office Customization Tools (OCT) and not the policy templates itself. The adm and admx templates in both packages are exactly the same.
- Run the installer and accept the License Agreement
- Select a location to extract the file to. For example C:\Office2010Policies
- Open the folder in Explorer
- Windows XP:
Select all the ADM-files (13 in total) from the ADM\en-us folder (or your localized language when available) and copy them to;
C:\Windows\inf - Windows Vista and Windows 7:
Select all the ADMX-files (13 in total) from the ADMX folder and the ADML-files (13) from the en-us folder (or your localized language when available) and copy them to;
C:\Windows\PolicyDefinitions
If you want to control a Active Directory network environment, then you can also copy them to the Sysvol\PolicyDefinitions folder on a domain controller.
- Windows XP:
- If you get prompted if you want to overwrite the files. Choose "Yes to All".
If you get prompted by User Account Control (UAC), press Continue or provide Administrator level credentials.
Setting Policies in an Active Directory network environment
Once you've installed the Office Resource Kit of your version you can control the policies via Group Policy Management Editor (GPMC).
Windows XP users can download it here.
Windows Vista users can download it here.
Windows 7 users can download it here.
- Open the GPMC through Control panel-> Administrative Tools-> Group Policy Management
- In the left column browse to the folder Group Policy Objects and select the Policy you wish to enforce Outlook policies on
- Right click the Policy and choose Edit…
- When you use Windows Vista or Windows 7, then the policies will be loaded automatically under;
User Configuration-> Policies-> Microsoft Office Outlook version.
When you are using Windows XP then you'll have to load the templates first;- In the left column of the Group Policy editor right click Administrative Templates in User Configuration and choose Add/Remove Templates…
- Press Add…
- Select the file corresponding with the installed version of the Office Resource Kit
- Press Open and then Close to return to the Group Policy editor. You now will see Outlook listed and you can now enforce settings.
Setting Policies on the local computer
Once you've installed the Office Resource Kit of your version you can control the policies via the Group Policy editor.
Note: The Group Policy Editor is not available when you are using a "Home" version of Windows.
- You can open the Group Policy Editor in the following way;
- Windows XP
Start-> Run; gpedit.msc - Windows Vista and Windows 7
Start-> type; gpedit.msc
- Windows XP
- When you use Windows Vista or Windows 7, then the policies will be loaded automatically under;
User Configuration-> Administrative Templates-> Microsoft Office Outlook version.
When you are using Windows XP then you'll have to load the templates first;- In the left column of the MMC expand Local Computer Policy then right click Administrative Templates in User Configuration and choose Add/Remove Templates…
- Press Add…
- Select the file corresponding with the installed version of the Office Resource KitPress Open and then Close to return to the Local Policy editor.
You now will see Outlook listed and you can now enforce settings. Some of the settings will only take effect after you restart your computer. Note that the settings are enforced to all users of that computer even the administrator! As an administrator you can of course change settings through the Group Policy Editor when required.
Set “File As” property for all Outlook contacts, contacts are blank in Outlook Address Book after import
Posted by Matt
In my travels of migrating from Novell Groupwise to Exchange and Outlook, I came across many issues with exporting and importing contacts. One of those issues is that the File As property for the contacts in Outlook is not filled out, thus making it so when using the Address Book, all contacts show up as blank names.
My resolution for this was a nice VB script I found that you run within Outlook and it runs through all Contacts in the Contacts folder and renames them depending on which line is uncommented.
To get to the VB project within Outlook where the code needs to be pasted, in Outlook hit ALT+F11 or select the VB Project from the Macro menu in Tools.
Public Sub ChangeFileAs()
Dim objOL As Outlook.Application
Dim objNS As Outlook.NameSpace
Dim objContact As Outlook.ContactItem
Dim objItems As Outlook.Items
Dim objContactsFolder As Outlook.MAPIFolder
Dim obj As Object
Dim strFirstName As String
Dim strLastName As String
Dim strFileAs As String
On Error Resume Next
Set objOL = CreateObject("Outlook.Application")
Set objNS = objOL.GetNamespace("MAPI")
Set objContactsFolder = objNS.GetDefaultFolder(olFolderContacts)
Set objItems = objContactsFolder.Items
For Each obj In objItems
'Test for contact and not distribution list
If obj.Class = olContact Then
Set objContact = obj
With objContact
' Uncomment the strFileAs line for the desired format
'Lastname, Firstname (Company) format
' strFileAs = .FullNameAndCompany
'Firstname Lastname format
' strFileAs = .FullName
'Lastname, Firstname format
' strFileAs = .LastNameAndFirstName
'Company name only
' strFileAs = .CompanyName
'Companyname (Lastname, Firstname)
' strFileAs = .CompanyAndFullName
.FileAs = strFileAs
.Save
End With
End If
Err.Clear
Next
Set objOL = Nothing
Set objNS = Nothing
Set obj = Nothing
Set objContact = Nothing
Set objItems = Nothing
Set objContactsFolder = Nothing
End Sub