How hta Changed the Way I Deliver Builds - Part 12 - Excel Help and Change Requests

I'm not a big fan of IT style Help Desk web sites where you have to log a call and wait for someone to contact you, if they do at all. I'm still a big fan of having the CAD Systems Group to be hands on and not a faceless entity. As such I've always preferred users call or email the support group direct, but there are times when you need more information for a help or change request. This is where I use some Excel files with a handy bit of code.

The download for this article is available from:

http://communities.bentley.com/communities/m/communities_gallery/221343.aspx

The code we use with our hta button to start an Excel file is:

sub starttr
Set xl = CreateObject("Excel.application")
'xl.Application.Workbooks.Open (MyUserpathHR) ' "R:\Admin\Help Request.xls"
xl.Application.Workbooks.Open ("R:\Admin\Help Request.xls")
xl.Application.Visible = True
Set xl = Nothing
end sub

Once open the user can fill out a number of fields and include snapshots to make bug fixing much easier. Once ready, we have a 'Submit' button at the top of the spreadsheet. What this button does is save a copy of the edited file to a specified location, say c:\temp\, using:

TempFilePath = "c:\temp\"
TempFileName = "Part of " & Sourcewb.Name & " " _
& Format(Now, "dd-mmm-yy h-mm-ss")

and then emails a copy of the file to a set email address:

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
.SendMail "sean.forward@<this could be your firm>.com.au", _
"This is the Subject line"
On Error GoTo 0
.Close SaveChanges:=False
End With

There are plenty of good examples on the net for this sort of code so don't hesitate to have a bit of a play.

More soon.