Regular Expressions

I've recently discoverd that Regular Expressions can be used in VBA. You just need to add a reference to "Microsoft VBScript Regular Expressions 5.5" in the Visual Basic Editor.

Regular Expressions is a data validation language that is used within many other programming languages and is very powerful at validating data. The great thing about it is that once you learn Regular Expressions you can transfer that knowledge to many other languages that implement Regular Expressions.

You can find more information here:

http://en.wikipedia.org/wiki/Regular_expression
www.regular-expressions.info/
http://regexlib.com/

Private Sub txtEmail_Enter()
' Using Regular expressions Must add a reference to
'Microsoft VBScript Regular Expressions 5.5

Dim re, s

Set re = New RegExp
re.Global = True
s = txtEmailAddress.Text
re.Pattern = "^([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$"

If Not re.Test(s) Then
MsgBox "Email address is NOT valid."
End If

End Sub

Increase ODBCTimeout property for all queries in an Access Database

If you ever have problems with Access queries timing out you can right-click in the query builder and set the ODBCTimeout property in the property window or you can run this code which will increase the ODBCTimeout property of all the queries in the database. You can just paste this code in a module and then run it and all the queries will have their timeout reset. I have some code that runs about 40 Access queries in a row and some of them were timing out and I found it easier to just change them all at once instead each one manually. Of course if you have too many queries timing out or if they still timeout after setting them to the maximum (6000) then you may need to convert you queries to SQL Server or whatever server database you are using. SQL Server is almost always faster than Access at running queries.



Dim qdfs As QueryDefs, qdf As QueryDef
Set qdfs = CurrentDb.QueryDefs
For Each qdf In qdfs
qdf.ODBCTimeout = 6000
Next

ContextMagic http://www.contextmagic.com/

Here's a free utility that I use all the time. It's called ContextMagic and you can get it for free here

http://www.contextmagic.com/



It adds some commands to the context menu when you right click over an icon or file. My favorite one is "Copy Name to Clipboard". It copies the full path to the item your mouse is over. I use it to email the location of files to people on the network and when I'm programming and need to reference a file path. I use it almost everyday.

Connect to Access from Excel using ADO

Here's the Excel VBA code I used to pull data from an Access query into an Excel Spreadsheet. It also brings in the column names. If you regularly have to pull data from Access or SQL server and then copy it to Excel this can help cut out some of those steps. I usually do a lot of formatting in code after bringing the data in, but left it out in this example in order to focus on the data extraction.

In order for this to work you need to add a refernce to ADO in Excel. In the Visual Basic Editor (VBE) go to Tools, References and search for Microsoft ActiveX Data Objects 2.8 Library. Click the checkbox and click OK.

Dim SQLcmd As String

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

Dim fld As ADODB.Field

Dim Row As Integer

Dim Column As Integer

SQLcmd = "SELECT * FROM [AccessQueryName]"

rs.Open Source:=SQLcmd, _

ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=N:\MyFolder\MyDatabaseName.mdb" + _

"; User Id=admin; Password="

Column = 1

Row = 1

For Each fld In rs.Fields

Cells(Row, Column).Value = fld.Name

Cells(Row, Column).Select

With Selection.Interior

.ColorIndex = 15

.Pattern = xlSolid

End With

Selection.Font.Bold = True

Column = Column + 1

Next fld

Cells(2, 1).CopyFromRecordset rs

Cells.Select

Cells.EntireColumn.AutoFit

Useful Excel and Access Website

http://www.datapigtechnologies.com/




This site is great for learning some advanced techniques in Access and Excel. I really learned a lot from this site when I was starting out with Access and Excel. The videos are short and concise and show how to do some truly useful stuff. There are also some videos on Xcelsius if you want to learn that too.

The site is run by Mike Alexander who has several books out on Access and Excel that you can find on Amazon.com or in your local bookstore. He recently started a blog that you read here:

http://datapig.bravejournal.com/

I think Mike is really creative when it comes to both Access and Excel and highly recommend his website.

Excel

At some point you have to decide whether you're going to be a politician or an engineer. You cannot be both. To be a politician is to champion perception over reality. To be an engineer is to make perception subservient to reality. They are opposites. You can't do both simultaneously.

—H. W. Kenton

Hi, My plan for this website it to post useful pieces of code that I find or write that have been useful to me in my work. I'm not really a guru at all. I'm actually kind of lazy. Laziness - that's my main motivator when I'm writing code. I want the computer to do all my work! Most of my programming experience is with VBA in Access, Excel, Outlook. Also use SQL Server and recently have started to use VBScript, VB.Net and C#.Net (2005) and a little bit of javascript.

Here's some VBScript that opens a spreadsheet and runs a macro. I was helping a colleage build a DTS package. I don't know DTS very well and he doesn't know VB so he asked for my help. You can use VBScript in a DTS package, but you can also create a standalone VBScript file. All you need to do is save a textfile (notepad, wordpad) with the .vbs extension. Then you can doubleclick the VBScript file to run it.

VBScript

' Open spreadsheet and run macro

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and Settings\brogers\Desktop\DTS_Test.xls")

objExcel.run("macro1")

objWorkbook.Close True

objExcel.quit

Here's a script that loops through every excel file in a folder and runs a macro in each spreadsheet. I have a VBA version that I'll try to find and post later.

This script uses the FileSystemObject and searches for files ending in xls.

'Loop through spreadsheets in folder and run macro

strPath = "C:\Documents and Settings\br\Desktop"

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.DisplayAlerts = False

Set objFso = CreateObject("Scripting.FileSystemObject")

Set objFolder = objFso.GetFolder (strPath)

For Each objFile In objFolder.Files

If objFso.GetExtensionName (objFile.Path) = "xls" Then

Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)

objExcel.run("macro1")

objWorkbook.Close True 'Save changes

End If

Next

objExcel.Quit

Finding a Field in SQL Server

This script is great when you are searching for a field in a database with a lot of tables or tables with many fields. It lists every table and field in a SQL Server database. Just run it in Query Analyzer and select the database you want it to return info on in the drop down list at the top of Query Analyzer. This has saved me a lot of time. Some of our databases have over 300 tables.

--List of Table and field Names

SELECT

table_name,

column_name,

data_type,

character_maximum_length as width

FROM information_schema.columns

ORDER BY table_name, ordinal_position