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