Removing External Data References from Excel

I really enjoy working with both Excel and Access when trying to solve quick data collection issues.

However, sometimes after you caputre the data inside of the Excel Spreadsheet from Microsoft Access you want to freeze the data in Excel.

Here is the fastest way to drop all the data links to external resources from inside of Microsoft Excel to create a snap shot in time.

Sub Delete_All_Queries()

Dim myQueryTables As QueryTable
Dim myWorkSheets As Worksheet

For Each myWorkSheets In ThisWorkbook.Worksheets
For Each myQueryTables In myWorkSheets.QueryTables
myQueryTables.Delete
Next myQueryTables
Next myWorkSheets

End Sub

Microsoft CDONTS now CDO.Messaging (Windows XP, 2000, 2003)

Standard ASP using email messaging in Windows 2003 R2 Servers

I ran into a snag the other day when I installed my new 2003 R2 servers and my email messaging quite working. After review discovered CDONTS.NEWMAIL is no longer supported in 2003 R2 Servers and you now need to understand CDO.Message.

Sending email with CDO is actually a simple process. You create a reference to the new CDO component and while taking into consideration the new command syntax. The challenge was trying to find the new syntax formats in ASP format vs ASP.NET. The resources seemed to have disappeared on using this in ASP because 2003 R2 is now ASP.NET.

I really do not have time to convert to ASP.NET so this is what I found to get everything moving again using ASP in a Windows 2003 R2 environment.

The basic conversion, the old CDONTS.NEWMAIL to CDO.MESSAGE.

Old Code Format:
Set CDONTS = Server.CreateObject("CDONTS.NEWMAIL")
With CDONTS
.Value("From") = "Michael Kintner "
.Value("To") = "Michael Kintner "
.Subject = "EMail Subject"
.Body = "EMail Body In HTML Format"
.MailFormat = 0 ' Use HTML Format
.BodyFormat = 0 ' Use HTML Format
.AttachFile "c:\myfile.txt"
.Send
End With
Set CDONTS = Nothing

New Code Format:
Set cdoMessage = CreateObject("CDO.Message")
With cdoMessage
.From = "Michael Kintner "
.To = "Michael Kintner "
.Subject = "EMail Subject"
.HTMLBody = "EMail Body

In HTML Format
"

'.TextBody = "EMail Body" & CRLF & "In Test Format"
.AddAttachment "c:\myfile.txt"
.Send
End With
Set cdoMessage = Nothing

Note: When sending email via HTML format then use .HTMLBody property, if via TEXT format then use the .TextBody property which has been commented.

To send an email via standard text:
Set cdoMessage = CreateObject("CDO.Message")
With cdoMessage
.From = "Michael Kintner "
.To = "Michael Kintner "
.Bcc = "Michael Kintner "
.Cc = "Michael Kintner "
.Subject = "EMail Subject"
.TextBody = "EMail Body"
.Send
End With
Set cdoMessage = Nothing


To send an email via HTML text:
Set cdoMessage = CreateObject("CDO.Message")
With cdoMessage
.From = "Michael Kintner"
.To = "Michael Kintner"
.Bcc = "Michael Kintner "
.Cc = "Michael Kintner "
.Subject = "EMail Subject"
.HTMLBody = "EMail Body This is a test HTML body"
.Send
End With
Set cdoMessage = Nothing

When using these basic examples to uses the local STMP Server embedded into IIS to send your email. However if you wish to use an outside mail server use this format.

Set cdoMessage = CreateObject("CDO.Message")
With cdoMessage
.From = "Michael Kintner "
.To = "Michael Kintner "
.Bcc = "Michael Kintner "
.Cc = "Michael Kintner "
.Subject = "EMail Subject"
.TextBody = "EMail Body"
.Send
End With
Set cdoMessage = Nothing

In the beginning

This is my first blog post. Testing to see how well this works.

The Hinsdale House (The Paranormal)

Oct 21, 2006 I went to a lecture with my wife, Alison and our neighbor's Tom and Carol. It was about explaining the scientific and geological reasons for paranormal and supernatural events at the Hinsdale House. Michael J Rambacker is a Paranormal Investigator & Legend Hunter and was explaining about paranormal and supernatural reasons through scientific and geological studies. After about 60 minutes of stories and events with pictures he decided to take us to a house in Hinsdale were most of the events came from. Stories of past, people passing through car accidents, drowning's and mass killings back in the late 1700’s and Early 1800’s set the mood when we arrived to see the Hinsdale hanging tree and house.

Being very skeptic about this I ended up taking about 165 photo’s from about 5:25pm upon arrival to 7:10pm when we left. There were 20 or more people present and we stayed until dark.

When arriving home, myself and friends downloaded the pictures from the camera to the computer and was extremely surprised to find three photos together that made our hair stand up. Note that when taking these photos I, nor any body else during the visit seen any bright lights of any kind.

These photos are real and unaltered using our Kodak C643 EasyShare Digital Camera.

Click to Enlarge

100_1431.JPG Taken Oct 21, 2006 at 6:18:46pm – You can see a slight bright light about ¾ the way across the picture from the left. (Very small)

Click to Enlarge

100_1432.JPG Taken Oct 21, 2006 at 6:18:54pm – You can see a bright light just below the power lines next to the telephone pole.

Click to Enlarge

100_1433.JPG Taken Oct 21, 2006 at 6:19:00pm – You can see almost the same angle with nothing there.

Click to Enlarge

Hinsdale-House-Three-Pics.jpg – I combined all three photos, added the time from the photos and while is photo shop used to auto contrast to brighten the photo. Nothing else!!!

Click to Enlarge

Hinsdale-House-Close-Up.jpg – Here is a blowup of the photo 100_1432.JPG.

It was not raining, the camera was sitting on a small sign in the yard with a two second start timer to prevent moving during picture taking. I moved the camera slightly between shoots to try to get different angles. Nobody out of the 20 visiting seen any sort of bright lights other than telling stories, hearing jokes and skepticism. After viewing this with my own camera knowing nothing was doctored I now believe something, just not sure what and how.

For questions and comments, call me at (716) 474-5700 or email mike@kintner.us

All material Copyright of Michael Kintner

Directions to the Hinsdale House
Pin A. Exit Route 86 (Exit 27)
Pin B. Sightings of Orbs
Pin C. Hanging Tree (Which I have been told has recently fell over)
Pin D. Hinsdale House

SQL Server fails to install

So many times I needed to install the SQL 2000 Server Tools and get the annoying message "A previous program installation created pending file operations on the installation machine. You must restart the computer before running setup." And what's puzzling you did not have a pending installation or maybe you did and it did not clear correctly.

Note that this is a registry fix so BACKUP before you attempt to make this change!

To fix this issue you will need to make a quick change to the registry.

Start run command prompt: Regedit

Go to registry area: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager

Find the Key: PendingFileRenameOperations and delete this value.

If you are new to Regedit I would highly suggest that you backup the registry befor eyou perform this operation.

To backup the registry go to the Session Manager area and right-click over the "Session Manager Key" and click save.

More details can be found at Microsoft Support: KB Article 312995