Editing Microsoft Excel Documents with ASP
Created 08 January 2001 00:00
If you have Microsoft Excel installed on your web server, you can edit or create documents using ASP.
  Dim objApp, objWorkBook, objASheet

  ' create instance of Excel, Workbook and finally active sheet
  Set objApp = CreateObject("Excel.Application")
  Set objWorkBook = objApp.WorkBooks.Add
  Set objASheet = objApp.ActiveWorkBook.ActiveSheet

  ' set sheet name
  objASheet.Name = "Address Book"
  
  ' set title fields
  objASheet.Cells(1,1).Value = "Name"
  objASheet.Cells(1,1).Font.Bold = True
  objASheet.Cells(1,2).Value = "Telephone"
  objASheet.Cells(1,2).Font.Bold = True

  ' set info fields
  objASheet.Cells(3,1).Value = "Matthew Salmon"
  objASheet.Cells(3,2).Value = "3121878"
  objASheet.Cells(4,1).Value = "Fiona Morris"
  objASheet.Cells(4,2).Value = "2662687"

  ' save sheet to whereever
  objASheet.SaveAs Server.MapPath(".") & "TEST.XLS"
	
  ' quit the application - very important to prevent multiple instances of Excel
  ' running on the server - use On Error Resume Next to ensure this line gets 
  ' executed
  objASheet.Application.Quit

  ' clean up
  Set objASheet = Nothing
  Set objWorkBook = Nothing
  Set objApp = Nothing
Adding HTML Attributes in XSL
Created 08 January 2001 00:00
e.g. you want to add an image using the PRODUCTIMAGE element from your stylesheet:
<IMG>
  <xsl:attribute name="SRC"><xsl:value-of select="PRODUCTIMAGE" /></xsl:attribute>
</IMG>
Generate dynamic file downloads with ASP
Created 08 January 2001 00:00
Should you ever require to generate a download file from ASP, where the file doesn't actually exist, but is perhaps the results of a query, here is the code. Works in IE4+ and NN2+. Without the "content-disposition" header, the filename the browser prompts to save is the name of the ASP page (eg. download.asp) - you can replace "data.csv" with anything you like.
  Response.ContentType = "application/csv"
  Response.AddHeader "content-disposition", "inline; filename=data.csv"
  Response.Write("one,two,three" & chr(13) & chr(10))
  Response.Write("1,2,3" & chr(13) & chr(10))
  Response.Write("1,2,3" & chr(13) & chr(10))
DOS : Ports Being Used
Created 08 January 2001 00:00
  netstat -ar|more
To find out more, Telnet to the port on that machine.
VB6 COM Callbacks
Created 08 January 2001 00:00, updated 06 June 2017 20:14
COM components using asynchronous callbacks must provide an interface through which the component will make the callback. The client program must then create an object using the interface and pass it into a method of the component. When the component completes its processing, it calls a method of the object that was passed in to perform the notification.

1. A notification interface for validating an employee number (called EG.Notify) :

  Option Explicit
  
  Public Sub ValidateReturned(validated as Boolean)
  End Sub
2. The component actually performing the validation (called EG.Employee):
  Option Explicit
  Public objNotify As EG.Notify

  Public Sub ValidateEmployee(empID As Long, objClient As EG.Notify)
    Set objNotify = objClient
    Call DoValidation
  End Sub

  Private Sub DoValidation()
    ' do validation of employee here
    
    ' finished - call the client
    objNotify.ValidateReturned True
  End Sub
3. class module to implement the callback (called EmpNotifier):
  Option Explicit
  Implements EG.Notify

  Private Sub Notify_ValidateReturned(validated as Boolean)
    ' the process is complete
    If NOT validated Then
      MsgBox "The employee number could not be successfully validated."
    End If
  End Sub
4. Using the interface requires creating two objects - one which actual does the validation and one to handle the notification of the validation:
  Option Explicit
  Private objEmployee As New EG.Employee
  Private objNotify As New EmpNotifier

  Private Sub Form_Load()
    objEmployee.ValidateEmployee 123456, objNotify
  End Sub
Halting Program Execution with VB6
Created 08 January 2001 00:00, updated 06 June 2017 20:14
Say for example you want to perform batch updates, but only want to do 100 records every minute. If you're using a DLL or ActiveX EXE to do the job, the easiest way to halt program execution is to use the Windows API Sleep method. Just add the following declaration to a module in your project, and specify the number of seconds times by 1000 (to get milliseconds) that you want your program to sleep for in between updates.

Note: This call stops ALL the threads in your application. If you only want to pause the processing of a single thread, use the Wait() API function.

  Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
ASP : Returning Recordsets with SOAP
Created 08 January 2001 00:00

This page provides my implementation of returning recordsets with SOAP messaging. A more complete article is Returning ADO Recordsets with SOAP Messaging by Chris Dengler.

Recordset to be retrieved

A DLL on the web server took certain parameters and returned a recordset containg employee information. My web service offered the facility to retrieve this information in the form of a simple XML representation.

Schema Generated by the Wizard

The SDL wizard generated the following schema for the response with the return type defaulted to string:

  <element name='GetEmployeeResponse'>
    <type>
      <element name='return' type='dt:string'/>
    </type>
  </element>

Edited Schema
I adjusted the schema to my own structure so that the SOAPPackager on the client end knew that it was receiving XML rather than the string:

  <element name='GetEmployeeResponse'>
    <type>
      <element name='return' type='GetEmployeeStruct'/>
      <element name='pvarResults' type='dt:string'/>
      <element name='pvarErrors' type='dt:string'/>
    </type>
  </element>
  <element name='GetEmployeeStruct'>
    <type>
      <element name='RECORD' type='GetEmployeeRecStruct'/>
    </type>
  </element>
  <element name='GetEmployeeRecStruct'>
    <type>
      <element name='intEmpID' type='dt:integer'/>
      <element name='strUserName' type='dt:integer'/>
      <element name='strFirstName' type='dt:string'/>
      <element name='strSurname' type='dt:string'/>
      <element name='strEmail' type='dt:string'/>
     </type>
  </element>

Adjusted ASP Function
The ASP function was then adjusted to pass an ADO recordset into the DLL call as opposed to a string, and a simple XML representation of that database is returned by the web service.

  '____________________________________________________________________
  ' function used by the listener
  Public Function GetEmployee (ByVal plngUserID)
    Dim objGetEmployee
    Set objGetEmployee = Server.CreateObject("MyPackage.MyClass")
    Dim objRS		' record set object to pass to function
    ' create disconnected recordset
    Set objRS = Server.CreateObject("ADODB.Recordset")
    ' call function - function will return objRS as a disconnected recordset
    Call objGetEmployee.GetEmployee(plngUserID, objRS)
    ' convert the recordset into XML format and send back to client
    GetEmployee = GetXMLFromRS(objRS)
    ' clean up objects
    Set objRS = Nothing
    Set objGetEmployee = NOTHING
  End Function
  '____________________________________________________________________
  ' function used to convert the recordset to XML
  Function GetXMLFromRS(ByVal objRS)
    ' declare variables
    Dim strXML					' output XML string
    Dim objField				' fields in the record set
    Dim strFieldName		' name of the field
    Dim strFieldValue		' value of the current field in the current record
    ' create outer element RECORDSET
    strXML = "<RECORDSET>" & vbCrLf
    ' loop through the recordset, adding each record and values to string
    Do While NOT objRS.EOF
      ' add new RECORD element for each record
      strXML = strXML & vbTab & "<RECORD>" & vbCrLf
      ' loop through the fields and add each with value to xml string
      For Each objField In objRS.Fields
        strFieldName = objField.Name
        strFieldValue = CDATAIt(objRS(strFieldName))
        strXML = strXML & vbTab & vbTab & "<" & strFieldName & ">" _
        & strFieldValue & "</" & strFieldName & ">" & vbCrLf
      Next
      ' close RECORD element
      strXML = strXML & vbTab & "</RECORD>" & vbCrLf
      objRS.MoveNext
    Loop
    ' close outer element RECORDSET and return XML string
    strXML = strXML & "</RECORDSET>" & vbCrLf
    GetXMLFromRS = strXML
    ' GetXMLFromRS = "Test"
  End Function

  '____________________________________________________________________
  ' function to add cdata tags to XML information
  Function CDATAIt(ByVal strXMLVal)
	  CDATAIt = "<![CDATA[" & strXMLVal & "]]>"
  End Function

Client-side
The result returned is straight-forward XML, for example:

  <return>
    <RECORDSET>
      <RECORD>
        <EmployeeID><![CDATA[4]]></EmployeeID>
        <UserName><![CDATA[msalmon]]></UserName>
        <FirstName><![CDATA[Matt]]></FirstName>
        <LastName><![CDATA[Salmon]]></LastName>
        <Email><![CDATA[matt_salmon@yahoo.com]]></Email>
      </RECORD>
      <RECORD>
        <EmployeeID><![CDATA[4]]></EmployeeID>
        <UserName><![CDATA[example]]></UserName>
        <FirstName><![CDATA[Example]]></FirstName>
        <LastName><![CDATA[Test]]></LastName>
        <Email><![CDATA[example@examples.com]]></Email>
      </RECORD>
    </RECORDSET>
  </return>
VB6 : HTTP Transfers
Created 08 January 2001 00:00
The MSXML2 Library contains the class ServerXMLHTTP30 which can be used to send HTTP requests and posts over the web.

Example 1: Executing an ASP page on a remote web server

  Dim objNetRequest As MSXML2.ServerXMLHTTP30
  Dim strURL As String
  strURL = "http://remote.com/YourPage.asp?param1=value1"
    
  Set objNetRequest = New MSXML2.ServerXMLHTTP30
  With objNetRequest
    .open "GET", strURL, False
    .send ("")
    MsgBox .responseText
  End With
  Set objNetRequest = Nothing

Example 2: Posting a form to a remote page
  Dim objNetRequest As MSXML2.ServerXMLHTTP30
  Dim strURL As String
  strURL = "http://remote.com/YourPage.asp?param1=value1"
    
  Set objNetRequest = New MSXML2.ServerXMLHTTP30
  With objNetRequest

    .open "POST", strURL, False
    .setRequestHeader "Content-Type", "text/xml"
    .send ("some text to be posted")
    MsgBox .responseText
  End With
  Set objNetRequest = Nothing
Storing data in INI files with VB6
Created 08 January 2001 00:00, updated 06 June 2017 20:13
Although most settings can be stored in the registry these days, it is sometimes more practical to store settings in an INI file (e.g. you want to be able to manually go and change those settings regularly).

There are predefined functions in the Windows API to allow you to read and write properties to/from INI files:

  ' to write values to an INI file.
  Declare Function WritePrivateProfileString _
    Lib "kernel32" Alias "WritePrivateProfileStringA" _
    (ByVal lpApplicationname As String, ByVal _
    lpKeyName As Any, ByVal lsString As Any, _
    ByVal lplFilename As String) As Long
  
  ' to read values from an INI file
  Declare Function GetPrivateProfileString Lib _
    "kernel32" Alias "GetPrivateProfileStringA" _
    (ByVal lpApplicationname As String, ByVal _
    lpKeyName As String, ByVal lpDefault As _
    String, ByVal lpReturnedString As String, _
    ByVal nSize As Long, ByVal lpFileName As _
    String) As Long
For example, if you have the following INI file:
  [MyHeading]
  MyKey1=myValue1
  Dir=c:mydir
  File=hello.txt
You could read the value of the "File" key in the following way:
  Dim lngResult As Long
  Dim strFileName
  Dim strResult As String * 50
  strFileName = "C:MyIniFile.ini"
	
  lngResult = GetPrivateProfileString("MyHeading", _
    "File", strFileName, strResult, Len(strResult), _
    strFileName)

  If lngResult = 0 Then
    'An error has occurred
    Call MsgBox("An error has occurred", vbExclamation)
    Exit Sub
  Else
    MsgBox "The value is " & strResult
  End If
You could change the value of the same key in the following way:
  Dim lngResult As Long
  Dim strFileName
  strFileName = "C:MyIniFile.ini"
  lngResult = WritePrivateProfileString("MyHeading", "File", "NewValue", strFileName)

  If lngResult = 0 Then
    'An error has occurred
    Call MsgBox("An error has occurred", vbExclamation)
  Else
    MsgBox "The value has been changed"
  End If

Using the return value

The return value (in the example lngResult) specifies the length of the string that is being returned. A value of 0 means an error occurred, but if you want to use the string that is returned you need to do some work due to the fact that the string is the length specified (in the example 50). For example if you want to tell the user what was returned, you would code:
MsgBox Left(strResult, lngResult) & " is the result."
which would effectively get the exact value returned rather than the padded version.
Returning recordsets in XML format with SOAP
Created 08 January 2001 00:00
This post provides my implementation of returning recordsets with SOAP messaging. A more complete article is Returning ADO Recordsets with SOAP Messaging by Chris Dengler.

Recordset to be retrieved

A DLL on the web server took certain parameters and returned a recordset containg employee information. My web service offered the facility to retrieve this information in the form of a simple XML representation.

Schema Generated by the Wizard
The SDL wizard generated the following schema for the response with the return type defaulted to string:

  <element name='GetEmployeeResponse'>
    <type>
      <element name='return' type='dt:string'/>
    </type>
  </element>

Edited Schema

I adjusted the schema to my own structure so that the SOAPPackager on the client end knew that it was receiving XML rather than the string:
  <element name='GetEmployeeResponse'>
    <type>
      <element name='return' type='GetEmployeeStruct'/>
      <element name='pvarResults' type='dt:string'/>
      <element name='pvarErrors' type='dt:string'/>
    </type>
  </element>
  <element name='GetEmployeeStruct'>
    <type>
      <element name='RECORD' type='GetEmployeeRecStruct'/>
    </type>
  </element>
  <element name='GetEmployeeRecStruct'>
    <type>
      <element name='intEmpID' type='dt:integer'/>
      <element name='strUserName' type='dt:integer'/>
      <element name='strFirstName' type='dt:string'/>
      <element name='strSurname' type='dt:string'/>
      <element name='strEmail' type='dt:string'/>
     </type>
  </element>

Adjusted ASP Function

The ASP function was then adjusted to pass an ADO recordset into the DLL call as opposed to a string, and a simple XML representation of that database is returned by the web service.
  '____________________________________________________________________
  ' function used by the listener
  Public Function GetEmployee (ByVal plngUserID)
    Dim objGetEmployee
    Set objGetEmployee = Server.CreateObject("MyPackage.MyClass")
    Dim objRS		' record set object to pass to function
    ' create disconnected recordset
    Set objRS = Server.CreateObject("ADODB.Recordset")
    ' call function - function will return objRS as a disconnected recordset
    Call objGetEmployee.GetEmployee(plngUserID, objRS)
    ' convert the recordset into XML format and send back to client
    GetEmployee = GetXMLFromRS(objRS)
    ' clean up objects
    Set objRS = Nothing
    Set objGetEmployee = NOTHING
  End Function
  '____________________________________________________________________
  ' function used to convert the recordset to XML
  Function GetXMLFromRS(ByVal objRS)
    ' declare variables
    Dim strXML					' output XML string
    Dim objField				' fields in the record set
    Dim strFieldName		' name of the field
    Dim strFieldValue		' value of the current field in the current record
    ' create outer element RECORDSET
    strXML = "<RECORDSET>" & vbCrLf
    ' loop through the recordset, adding each record and values to string
    Do While NOT objRS.EOF
      ' add new RECORD element for each record
      strXML = strXML & vbTab & "<RECORD>" & vbCrLf
      ' loop through the fields and add each with value to xml string
      For Each objField In objRS.Fields
        strFieldName = objField.Name
        strFieldValue = CDATAIt(objRS(strFieldName))
        strXML = strXML & vbTab & vbTab & "<" & strFieldName & ">" _
        & strFieldValue & "</" & strFieldName & ">" & vbCrLf
      Next
      ' close RECORD element
      strXML = strXML & vbTab & "</RECORD>" & vbCrLf
      objRS.MoveNext
    Loop
    ' close outer element RECORDSET and return XML string
    strXML = strXML & "</RECORDSET>" & vbCrLf
    GetXMLFromRS = strXML
    ' GetXMLFromRS = "Test"
  End Function

  '____________________________________________________________________
  ' function to add cdata tags to XML information
  Function CDATAIt(ByVal strXMLVal)
	  CDATAIt = "<![CDATA[" & strXMLVal & "]]>"
  End Function

Client-side

The result returned is straight-forward XML, for example:
  <return>
    <RECORDSET>
      <RECORD>
        <EmployeeID><![CDATA[4]]></EmployeeID>
        <UserName><![CDATA[msalmon]]></UserName>
        <FirstName><![CDATA[Matt]]></FirstName>
        <LastName><![CDATA[Salmon]]></LastName>
        <Email><![CDATA[matt_salmon@yahoo.com]]></Email>
      </RECORD>
      <RECORD>
        <EmployeeID><![CDATA[4]]></EmployeeID>
        <UserName><![CDATA[example]]></UserName>
        <FirstName><![CDATA[Example]]></FirstName>
        <LastName><![CDATA[Test]]></LastName>
        <Email><![CDATA[example@examples.com]]></Email>
      </RECORD>
    </RECORDSET>
  </return>
Send and receive data over HTTP using the Microsoft Internet Transfer Control
Created 08 January 2001 00:00
The following is an example of submitting credit card validation details:
  Dim inetTransfer As InetCtlsObjects.Inet
  Set inetTransfer = New InetCtlsObjects.Inet
  Dim strPostText As String
  Dim strPostHeader As String
  
' build up the text that will be posted to the authentication site
  strPostText = "VMSMerchantID=" & VMSMerchantID & _
    "&SSMerchantID=" & SSMerchantID & _
    "&VMMCMerchantID=" & VMMCMerchantID & _
    "&AttemptNum=" & AttemptNum & _
    "&CurrencyCode=" & CurrencyCode & _
    "&CountryCode=" & CountryCode & _
    "&RetOKAddress=" & objUtil.URLEncode(RetOKAddress, DEF_CODE_PAGE) & _
    "&RetNotOKAddress=" & objUtil.URLEncode(RetNotOKAddress, DEF_CODE_PAGE) & _
    "&MessageType=" & MessageType & _
    "&Amount=" & Amount & _
    "&CardType=" & CardType & _
    "&CardNumber=" & objUtil.URLEncode(CardNumber, DEF_CODE_PAGE) & _
    "&IssueNumber=" & IssueNumber & _
    "&ExpMonth=" & ExpMonth & _
    "&ExpYear=" & ExpYear & _
    "&StartMonth=" & StartMonth & _
    "&StartYear=" & StartYear
  ' add the form encoded header to the post so that the recipient sees the data as
  ' coming from a submitted form
  strPostHeader = "Content-Type: application/x-www-form-urlencoded" & vbCrLf
  ' use transfer object to execute the url
  inetTransfer.Execute AuthURL, "POST", strPostText, strPostHeader
  ' wait until the authorisation site returns an answer
  Do Until inetTransfer.StillExecuting = False
    DoEvents
  Loop
  ' return the code - this will be a zero-length string if the card is authorised
  Authenticate = inetTransfer.GetChunk(1024, icString)
  Set inetTransfer = Nothing
Sending HTTP Requests using the MSXML2 object
Created 08 January 2001 00:00
The MSXML2 Library contains the class ServerXMLHTTP30 which can be used to send HTTP requests and posts over the web.

Example 1: Executing an ASP page on a remote web server

  Dim objNetRequest As MSXML2.ServerXMLHTTP30
  Dim strURL As String
  strURL = "http://remote.com/YourPage.asp?param1=value1"
    
  Set objNetRequest = New MSXML2.ServerXMLHTTP30
  With objNetRequest
    .open "GET", strURL, False
    .send ("")
    MsgBox .responseText
  End With
  Set objNetRequest = Nothing

Example 2: Posting a form to a remote page

  Dim objNetRequest As MSXML2.ServerXMLHTTP30
  Dim strURL As String
  strURL = "http://remote.com/YourPage.asp?param1=value1"
    
  Set objNetRequest = New MSXML2.ServerXMLHTTP30
  With objNetRequest

    .open "POST", strURL, False
    .setRequestHeader "Content-Type", "text/xml"
    .send ("some text to be posted")
    MsgBox .responseText
  End With
  Set objNetRequest = Nothing
Mapping a drive from the command line in DOS
Created 08 January 2001 00:00
Syntax: net use * <ipaddress>\<drive>$ /USER:<username>

E.g.: net use * 10.83.106.157\c$ /USER:msalmon