www.geekybob.com

Just a short, simple blog for Bob to share his thoughts.

FrontPage Macro: Reformat HTML

17 February 2008 • by Bob • FrontPage

Using this FrontPage VBA Macro

This FrontPage VBA Macro is designed to reformat the HTML for every HTML or ASP file within the currently open web site.

FrontPage VBA Macro Example Code

Public Sub ReformatHTML()
    Dim objWebFile As WebFile
    Dim strExt As String
    Dim cbCommandBar As CommandBar
    Dim cbCommandBarControl As CommandBarControl

    If Len(Application.ActiveWeb.Title) = 0 Then
        MsgBox "A web must be open." & vbCrLf & vbCrLf & "Aborting.", vbCritical
        Exit Sub
    End If

    For Each objWebFile In Application.ActiveWeb.AllFiles
        strExt = LCase(objWebFile.Extension)
        If strExt = "htm" Or strExt = "html" Or strExt = "asp" Then
            objWebFile.Edit
            Application.ActivePageWindow.ViewMode = fpPageViewHtml
            DoEvents
            Set cbCommandBar = Application.CommandBars("Html Page View Context Menu")
            Set cbCommandBarControl = cbCommandBar.FindControl( _
                Type:=msoControlButton, _
                Id:=CommandBars("Html Page View Context Menu").Controls("Reformat HT&ML").Id)
            cbCommandBarControl.Execute
            DoEvents
            Application.ActivePageWindow.Save
            Application.ActivePageWindow.Close
        End If
    Next

End Sub

Access Macro: Export Table/Query To Excel

17 February 2008 • by Bob • Office

Using this Access VBA Macro

I wrote this Access VBA Macro for a friend to export an Access table or query to a spreadsheet; it might come in handy. ;-]

Access VBA Macro Example Code

Sub ExportTableOrQueryToExcel()

    Const strTitle = "This is my worksheet title"
    Const strTableOrQuery = "Query1"
    
    ' define the path to the output file
    Dim strPath As String
    strPath = "C:\TestFile " & _
        Year(Now) & Right("0" & _
        Month(Now), 2) & Right("0" & _
        Day(Now), 2) & ".xls"
    
    ' create and open an Excel workbook
    Dim objXL As Object
    Set objXL = CreateObject("Excel.Application")
    objXL.WorkBooks.Add
    objXL.Worksheets(1).Name = strTitle
    objXL.Visible = False
   
    ' delete the extra worksheets
    Dim intX As Integer
    If objXL.Worksheets.Count > 1 Then
        For intX = 2 To objXL.Worksheets.Count
            objXL.Worksheets(2).Delete
        Next
    End If
    
    ' open the database
    Dim objDB As DAO.Database
    Dim objRS As DAO.Recordset
    Dim objField As DAO.Field
    Set objDB = CurrentDb
    
    ' open the query/table
    Dim strSQL As String
    strSQL = "SELECT * FROM [" & strTableOrQuery & "]"
    Set objRS = objDB.OpenRecordset(strSQL)
    
    Dim lngRow As Long
    Dim lngCol As Long
    
    If Not objRS.EOF Then
    
        lngRow = 1: lngCol = 1
        
        For Each objField In objRS.Fields
            objXL.Worksheets(1).Cells(lngRow, lngCol).Value = objField.Name
            lngCol = lngCol + 1
        Next
        
        lngRow = lngRow + 1
    
        ' loop through the table records
        Do While Not objRS.EOF
            lngCol = 1
            For Each objField In objRS.Fields
                objXL.Worksheets(1).Cells(lngRow, lngCol).Value = objField.Value
                lngCol = lngCol + 1
            Next
            lngRow = lngRow + 1
            objRS.MoveNext
        Loop
    
    End If
    
    objXL.DisplayAlerts = False
    objXL.ActiveWorkbook.SaveAs strPath, 46
    objXL.ActiveWorkbook.Close

End Sub

Access: Database Schema Report

17 February 2008 • by Bob • Office

Summary

This article shows you a Windows Script Host (WSH) application that will create a report based on the schema of an Access Database.

More Information

  1. Open Windows Notepad and copy/paste the WSH code listed below into it.
  2. Modify the strDatabaseFile and strOutputFile constants for your database and desired report name.
  3. Save the file as "DatabaseScema.vbs" to your desktop.
  4. Double-click the WSH file to run it.
    Note: A message box that says "Finished!" will appear when the script has finished executing.
Windows Script Host (WSH) Code
Option Explicit

' --------------------------------------------------
' Define variables and constants
' --------------------------------------------------

Const strDatabaseFile = "MusicStuff.mdb"
Const strOutputFile = "MusicStuff.htm"
Const adSchemaTables = 20

Dim strSQL
Dim strCN
Dim objCN
Dim objRS1
Dim objRS2
Dim objField
Dim intCount
Dim objFSO
Dim objFile

' --------------------------------------------------
' Open the output file
' --------------------------------------------------

Set objFSO = WScript.CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile(strOutputFile)
objFile.WriteLine "<html><head>" & _
"<style>BODY { font-family:arial,helvetica; }</style>" & _
"</head><body>"
objFile.WriteLine "<h2>Schema Report for &quot;" & _
strDatabaseFile & "&quot;</h2>"

' --------------------------------------------------
' Setup the string array of field type descriptions
' --------------------------------------------------

Dim strColumnTypes(205)

' initialize array

For intCount = 0 To UBound(strColumnTypes)
strColumnTypes(intCount) = "n/a"
Next

' add definitions

strColumnTypes(2) = "Integer"
strColumnTypes(3) = "Long Integer"
strColumnTypes(4) = "Single"
strColumnTypes(5) = "Double"
strColumnTypes(6) = "Currency"
strColumnTypes(11) = "Yes/No"
strColumnTypes(17) = "Byte"
strColumnTypes(72) = "Replication ID"
strColumnTypes(131) = "Decimal"
strColumnTypes(135) = "Date/Time"
strColumnTypes(202) = "Text"
strColumnTypes(203) = "Memo/Hyperlink"
strColumnTypes(205) = "OLE Object"

' --------------------------------------------------
' Open database and schema
' --------------------------------------------------

strCN = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & strDatabaseFile
Set objCN = WScript.CreateObject("ADODB.Connection")
objCN.Open strCN
Set objRS1 = objCN.OpenSchema(adSchemaTables)

' --------------------------------------------------
' Loop through database schema
' --------------------------------------------------

Do While Not objRS1.EOF
  If Left(objRS1("TABLE_NAME"),4) <> "MSys" Then
    objFile.WriteLine "<p><big>" & objRS1("TABLE_NAME") & "</big></p>"
    objFile.WriteLine "<blockquote><table border=1>" & _
      "<tr><th>Field Name</th><th>Data Type</th></tr>"
    strSQL = "SELECT * FROM [" & objRS1("TABLE_NAME") & "]"
    Set objRS2 = objCN.Execute(strSQL)
    For Each objField in objRS2.Fields
      objFile.WriteLine "<tr><td>" & objField.Name _
      & "</td><td>" & strColumnTypes(objField.Type) & "</td></tr>"
    Next
    objFile.WriteLine "</table></blockquote>"
  End If
  objRS1.MoveNext
Loop

' --------------------------------------------------
' Close the output file
' --------------------------------------------------

objFile.WriteLine "</body></html>"

MsgBox "Finished!"

IIS Versions and Timeline

17 February 2008 • by Bob • IIS

I put this list together sometime ago, but I don't recall why. In any event, the following time line illustrates the history of Microsoft's Internet Information Services and the individual services that shipped with each version.

WebDAV Module for Windows Server 2008 GoLive Beta is released

22 December 2007 • by Bob • IIS, IIS News Item, WebDAV

Earlier today the IIS product team released the GoLive beta version of the new WebDAV extension module for IIS 7. (This version is currently available for Windows Server 2008 only.)

Listed below are the links for the download pages for each of the individual installation packages:

We've loaded this version with many great new features such as:

Here are a couple of screenshots of the new WebDAV UI in action:

WebDAV UI WebDAV Authoring Rules

Additional documentation about installing and using this version of WebDAV can be found at the following URL:

Installing and Configuring WebDAV on IIS 7.0:
http://go.microsoft.com/fwlink/?LinkId=105146

While this release is a beta version and not technically supported, feedback about this release and requests for information can be posted to the following web forum:

IIS7 - Publishing:
http://forums.iis.net/1045.aspx

I would be remiss if I did not mention that special thanks go to:

Thanks!

Note: This blog was originally posted at http://blogs.msdn.com/robert_mcmurray/

FPSE 2002 RC1 for Windows Server 2008 and Windows Vista (x86/x64)

14 December 2007 • by Bob • FrontPage, IIS, IIS News Item

Earlier today Microsoft and Ready-to-Run Software released to web the Release Candidate 1 (RC1) version of the FrontPage 2002 Server Extensions for IIS 7.0 on Windows Server 2008 and Windows Vista. This build now includes a combined installation package for 32-bit and 64-bit versions of Windows.

Listed below is the link for the download page for the combined 32-bit/64-bit installation package:

FPSE 2002 RC1 for IIS 7 is supported on all of the the following operating systems:

Once again, additional documentation about installing and using this version of FPSE 2002 can be found at the following URL:

Installing the FrontPage 2002 Server Extensions:
http://go.microsoft.com/fwlink/?LinkId=88546

While this release is a beta version and not technically supported, feedback about this release and requests for information can be sent to fpbeta@rtr.com or posted to the following web forum:

IIS7 - Publishing:
http://forums.iis.net/1045.aspx

Thanks!

Note: This blog was originally posted at http://blogs.msdn.com/robert_mcmurray/

Creating XML Reports for FSRM Quota Usage

25 October 2007 • by Bob • Scripting

I had a great question in follow up to the "Secure, Simplified Web Publishing using Microsoft Internet Information Services 7.0" webcast that I delivered the other day, "How you can you programmatically access the quota usage information from the File Server Resource Manager (FSRM)?"

First of all, there is a native API for writing code to access FSRM data detailed at the following URL:

http://msdn2.microsoft.com/en-us/library/bb625489.aspx

That's a bit of overkill if you're just looking to script something.

There is a WMI interface as well, but it’s only for FSRM events.

So that leaves you with a pair of command-line tools that you can script in order to list your quota usage information:

Right out of the box the first command-line tool, storrept.exe, can generate a detailed XML report using a user-definable scope. To see this in action, take the following example syntax and modify the scope parameter to your desired paths:

storrept.exe reports generate /Report:QuotaUsage /Format:XML /Scope:"C:\"

 You can also specify multiple paths in your scope using a pipe-delimited format like:

/Scope:"C:\Inetpub|D:\Inetpub"

When the command has finished, it will tell you the path to your report like the following example:

Storage reports generated successfully in "C:\StorageReports\Interactive".

The XML-based information in the report can then be consumed with whatever method you usually use to parse XML. It should be noted that storrept.exe also supports the following formats: CSV, DHTML, HTML, and TXT.

This XML might be okay for most applications, but for some reason I wanted to customize the information that I received, so I experimented with the second command-line tool, dirquota.exe, to get the result that I was looking for.

First of all, using dirquota.exe quota list returns information in the following format:

Quotas on machine SERVER: Quota Path: C:\inetpub\ftproot Source Template: 100 MB Limit (Matches template) Quota Status: Enabled Limit: 100.00 MB (Hard) Used: 1.00 KB (0%) Available: 100.00 MB Peak Usage: 1.00 KB (10/25/2007 2:15 PM) Thresholds: Warning ( 85%): E-mail Warning ( 95%): E-mail, Event Log Limit (100%): E-mail, Event Log

This information is formatted nicely and is therefore easily parsed, so I wrote the following batch file called "dirquota.cmd" to start things off:

@echo off echo Processing the report... dirquota.exe quota list > dirquota.txt cscript.exe //nologo dirquota.vbs

Next, I wrote the following vbscript application called "dirquota.vbs" to parse the output into some easily-usable XML code:

Option Explicit

Dim objFSO, objFile1, objFile2
Dim strLine, strArray(2)
Dim blnQuota,blnThreshold

' create objects
Set objFSO = WScript.CreateObject("Scripting.FileSystemObject")
Set objFile1 = objFSO.OpenTextFile("dirquota.txt")
Set objFile2 = objFSO.CreateTextFile("dirquota.xml")

' start the XML output file
objFile2.WriteLine "<?xml version=""1.0""?>"
objFile2.WriteLine "<Quotas>"

' set the runtime statuses to off
blnQuota = False
blnThreshold = False

' loop through the text file
Do While Not objFile1.AtEndOfStream

  ' get a line from the file
  strLine = objFile1.ReadLine

  ' only process lines with a colon character
  If InStr(strLine,":") Then
    ' split the string manually at the colon character
    strArray(1) = Trim(Left(strLine,InStr(strLine,":")-1))
    strArray(2) = Trim(Mid(strLine,InStr(strLine,":")+1))

    ' filter on strings with parentheses
    strLine = strArray(1)
    If InStr(strLine,"(") Then
      strLine = Trim(Left(strLine,InStr(strLine,"(")-1)) & "*"
    End If

    ' process the inidivdual entries
    Select Case UCase(strLine)

      ' a quota path signifies a new record
      Case UCase("Quota Path")

        ' close any open threshold collections
        If blnThreshold = True Then
          objFile2.WriteLine "</Thresholds>"
        End If

        ' close an open quota element
        If blnQuota= True Then
          objFile2.WriteLine "</Quota>"
        End If

        ' signify a new quota element
        objFile2.WriteLine "<Quota>"

        ' output the relelvant information
        objFile2.WriteLine FormatElement(strArray(1),strArray(2))

        ' set the runtime statuses
        blnQuota= True
        blnThreshold = False

      ' these bits of informaiton are parts of a quota
      Case UCase("Source Template"), UCase("Quota Status"), _
          UCase("Limit"), UCase("Used"), _
          UCase("Available"), UCase("Peak Usage")

        ' close any open threshold collections
        If blnThreshold = True Then
          objFile2.WriteLine "</Thresholds>"
        End If

        ' set the runtime status
        blnThreshold = False

        ' output the relelvant information
        objFile2.WriteLine FormatElement(strArray(1),strArray(2))

      ' these bits of informaiton are thresholds
      Case UCase("Warning*"), UCase("Limit*")

        ' open a threshold collection if not already open
        If blnThreshold = False Then
          objFile2.WriteLine "<Thresholds>"
        End If

        ' output the relelvant information
        objFile2.WriteLine FormatElement( _
          Left(strLine,Len(strLine)-1), _
          Replace(Mid(strArray(1), _
          Len(strLine))," ","") & " " & strArray(2))

        ' set the runtime status
        blnThreshold = True

    End Select
  End If
Loop

' close any open threshold collections
If blnThreshold = True Then
  objFile2.WriteLine "</Thresholds>"
End If

' close an open quota element
If blnQuota= True Then
  objFile2.WriteLine "</Quota>"
End If

' end the XML output file
objFile2.WriteLine "</Quotas>"

objFile1.Close
objFile2.Close
Set objFSO = Nothing

' format data into an XML element
Function FormatElement(tmpName,tmpValue)
  FormatElement = "<" & Replace(tmpName," ","") & _
  ">" & tmpValue & "</" & Replace(tmpName,Chr(32),"") & ">"
End Function

When the batch file and vbscript are run, they will create a file named "dirquota.xml" which will resemble the following example XML:

<?xml version="1.0"?>
<Quotas>
  <Quota>
    <QuotaPath>C:\inetpub\ftproot</QuotaPath>
    <SourceTemplate>100 MB Limit (Matches template)</SourceTemplate>
    <QuotaStatus>Enabled</QuotaStatus>
    <Limit>100.00 MB (Hard)</Limit>
    <Used>1.00 KB (0%)</Used>
    <Available>100.00 MB</Available>
    <PeakUsage>1.00 KB (10/25/2007 2:15 PM)</PeakUsage>
    <Thresholds>
      <Warning>(85%) E-mail</Warning>
      <Warning>(95%) E-mail, Event Log</Warning>
      <Limit>(100%) E-mail, Event Log</Limit>
    </Thresholds>
  </Quota>
</Quotas>

I found the above XML much easier to use than the XML that came from the storrept.exe report, but I'm probably comparing apples to oranges. In any event, I hope this helps someone with questions about FSRM reporting.

Have fun!

Note: This blog was originally posted at http://blogs.msdn.com/robert_mcmurray/

Upcoming IIS7 Webcasts

05 October 2007 • by Bob • IIS

Okay, I have to admit, I was remiss at pointing this out earlier - several members of the IIS product team and myself are presenting webcasts on the TechNet web site for various IIS subjects throughout the month of October. Here's the schedule of upcoming topics:

We've had two webcasts already, and you can still listen to those online:

Enjoy!

FPSE 2002 RC0 for Windows Server 2008 and Windows Vista (x86/x64)

02 October 2007 • by Bob • FrontPage, IIS, IIS News Item

Earlier today Microsoft and Ready to Run Software released to web the Release Candidate 0 (RC0) version of the FrontPage 2002 Server Extensions for IIS 7.0 on Windows Server 2008 and Windows Vista. This build now includes support for 64-bit Windows, and addresses several issues that we've seen since the FPSE release that shipped last April.

Listed below are the links for the download pages for each of the individual installation packages:

FPSE 2002 RC0 for IIS 7 is supported on all of the the following operating systems:

Additional documentation about installing and using this version of FPSE 2002 can be found at the following URL:

Installing the FrontPage 2002 Server Extensions:
http://go.microsoft.com/fwlink/?LinkId=88546

Quoting and updating some of my notes from the last release:

It should be noted that this version of FPSE 2002 is a beta release and is therefore unsupported. Also, this version of FPSE 2002 introduces no new functionality; it is essentially the same version of FPSE 2002 that was created for Windows Server 2003 that has been updated to work on Windows Server 2008 (code name "Longhorn") and Windows Vista. That being said, this version of FPSE 2002 will enable web hosters and developers to author their web content on servers or workstations that are running IIS 7.0 on Windows Server 2008 and Windows Vista.

While this release is a beta version and not technically supported, feedback about this release and requests for information can be sent to fpbeta@rtr.com or posted to the following web forum:

IIS7 - Publishing:
http://forums.iis.net/1045.aspx

Thanks!

FTP7 for Windows Server 2008 RC0 is released!

27 September 2007 • by Bob • FTP, IIS, IIS News Item

Today Microsoft released the newest version of our FTP server for Windows Server 2008 Release Candidate 0 (RC0)!

Listed below are the links for the download pages for each of the individual installation packages:

I added the following text to my "Installing and Troubleshooting FTP7" walkthrough, but I’m going to repost that here because it sums up nicely what our new FTP server has to offer:

This new FTP service incorporates many new features that enable web authors to publish content better than before, and offers web administrators more security and deployment options.

  • Integration with IIS 7.0: IIS 7.0 has a brand-new administration interface and configuration store, and the new FTP service is tightly integrated with this new design. The old IIS 6 metabase is gone, and a new configuration store that is based on the .NET XML-based *.config format has taken its place. In addition, IIS 7.0 has a new administration tool, and the new FTP server plugs seamlessly into that paradigm.
  • Support for new Internet standards: One of the most significant features in the new FTP server is support for FTP over SSL. The new FTP server also supports other Internet improvements such as UTF8 and IPv6.
  • Shared hosting improvements: By fully integrating into IIS 7.0, the new FTP server makes it possible to host FTP and Web content from the same site by simply adding an FTP binding to an existing Web site. In addition, the FTP server now has virtual host name support, making it possible to host multiple FTP sites on the same IP address. The new FTP server also has improved user isolation, now making it possible to isolate users through per-user virtual directories.
  • Extensibility and custom authentication: The new FTP server supports developer extensibility, making it possible for software vendors to write custom providers for FTP authentication. Microsoft is using this extensibility feature to implement two new methods for using non-Windows accounts for FTP authentication for IIS Managers and .NET Membership.
  • Improved logging support: FTP logging has been enhanced to include all FTP-related traffic, unique tracking for FTP sessions, FTP sub-statuses, additional detail fields in FTP logs, and much more.
  • New supportability features: IIS 7.0 has a new option to display detailed error messages for local users, and the FTP server supports this by providing detailed error responses when logging on locally to an FTP server. The FTP server also logs detailed information using Event Tracing for Windows (ETW), which provides additional detailed information for troubleshooting.

Additional information about new features in FTP7 is available in the "What's New for Microsoft and FTP?" topic on Microsoft's http://www.iis.net/ web site.

The following prerequisites are required to install this new version:

  1. You must be using Windows Server 2008 (code name "Longhorn") Release Candidate 0 (RC0) or later.
  2. Internet Information Services 7.0 must be installed.
  3. If you are going to manage the new FTP server using the IIS 7.0 user interface, the administration tool will need to be installed.
  4. You must install the new FTP server as an administrator.
  5. IIS 7.0 supports a new shared configuration environment, which must be disabled on each server in a web farm before installing the new FTP server for each node. Note: Shared configuration can be re-enabled after the FTP server had been installed.
  6. The FTP server that is shipped on the Windows Server 2008 DVD must be uninstalled before installing the new FTP server.

To help you get started using the new FTP server, the following walkthroughs have been published on the http://www.iis.net/ web site:

Special thanks go to:

Thanks!

Blog Navigation

You are on page 66 of 71 pages.

1 ... 64 65 66 67 68 69 70 71