McKirahan
Tue Mar 04 09:54:40 CST 2008
<Cryptographic_ICE@yahoo.com> wrote in message
news:06aa37ef-e055-4cb4-af8d-73083f59dca0@e10g2000prf.googlegroups.com...
> Thanks for the Reply.
>
> An example row would look like: (first row is column names)
>
> 1) "Easy Name" this would be a short descriptive name like "Financial
> Report"
> 2) "Old File Name" this is the name of the current file. It's a full
> path "C:\reports\"
> 3) "New file name" This is what we would like to call it. Also a full
> path
>
> There would be about 15ish files at any one time.
>
> I think the code would look something like this <input type="checkbox"
> name="EasyName">
>
> Then if the first 3 were checked it would only rename the first 3
> files.
>
>
>
>
>
> On Mar 3, 11:48 pm, "McKirahan" <N...@McKirahan.com> wrote:
> > "McKirahan" <N...@McKirahan.com> wrote in message
> >
> > news:-MWdnWA5-OHIVFHanZ2dnUVZ_vumnZ2d@comcast.com...> "McKirahan"
<N...@McKirahan.com> wrote in message
> > >news:-bSdnUb_74hCA1HanZ2dnUVZ_vyinZ2d@comcast.com...
> > > > <Cryptographic_...@yahoo.com> wrote in message
> >
> >
news:c06105cc-6220-4f9c-a729-4832a95819c1@s19g2000prg.googlegroups.com...
> >
> > > > > Hello,
> >
> > > > > I'm creating an HTA that renames files based on an excel sheet.
The
> > > > > excel sheet has 3 columns:
> >
> > > > > Name, Old File Name, New File Name
> >
> > > > > I would like to dynamically build a form (check boxes and submit
> > > > > button) based on the "Name" column. If the name is checked then it
> > > > > will run code against that record. Does anyone know the best way
to
> > > > > accomplish this?
> >
> > > > > I'm looking for something very similar to this (only with
checkboxes
> > > > > instead of a list box)
> >
> >
http://www.microsoft.com/technet/scriptcenter/resources/qanda/aug05/h....
> >
> > > > mspx
> >
> > > > What would be an example of the contents of a row?
> >
> > > Also, what would each "checkbox" tag look like? Perhaps:
> >
> > > <input type="checkbox" value="y,z">x
> >
> > > Where:
> > > x = Name
> > > y = Old File Name
> > > z = New File Name
> >
> > > Are all files in the same folder or is the full path included?
> >
> > One more question -- how many files might be involved?
Please do not top post as it makes the "conversation" harder to follow.
Will this help? Watch for word-wrap.
<html>
<head>
<title>Renames.hta</title>
<HTA:Application ID = "HTA"
ApplicationName = "Renames"
Border = "thin"
BorderStyle = "normal"
Caption = "yes"
Icon = ""
MaximizeButton = "yes"
MinimizeButton = "yes"
ShowInTaskBar = "yes"
SingleInstance = "yes"
SysMenu = "yes"
Version = "1.0"
WindowState = "normal">
<script type="text/vbscript">
Option Explicit
'****
'* 1) Read MS-Excel worksheet "cXLS" to build checkboxes;
'* "cSQL" identifies expected worksheet name;
'* "cROW" identifies expected column labels;
'* checkbox color of yellow indicates error.
'* 2) Rename files per the "checked" checkboxes;
'* "cDBG" may be used to display progress Alert();
'* checkbox color indicates success or failure of copy;
'* (success = green; failure = red or orange);
'* mouseover each checkbox to display "Step 2" status.
'****
'*
'* Declare Constants
'*
Const cHTA = "Renames.hta"
Const cXLS = "Renames.xls"
Const cDRV = "DRIVER=Microsoft Excel Driver (*.xls);DBQ="
Const cSQL = "SELECT * FROM [Sheet1$]"
Const cROW = "Name,Old File Name,New File Name"
Const cBOX = "<br><input type='checkbox' id='Box_#' class='bgcW'
value='?'>"
Const cDBG = False '= True/False: True enables Alert().
'*
'* Declare Objects
'*
Dim objFSO
Set objFSO = CreateObject("Scripting.FIleSystemObject")
Sub Process()
'****
'* Processing steps.
'****
'*
'* Declare Variables
'*
Dim strVAL
strVAL = document.getElementById("Process").innerHTML
'*
'* Process Steps
'*
If strVAL = "Step 1" Then
Call Process_Step1()
ElseIf strVAL = "Step 2" Then
Call Process_Step2()
End If
End Sub
Sub Process_Step1()
'****
'* Step 1 = Read MS-Excel worksheet to build checkboxes.
'****
'*
'* Declare ADO Constants
'*
Const adLockOptimistic = 3
Const adOpenKeySet = 1
Const adUseClient = 3
'*
'* Declare Variables
'*
Dim arrBOX()
Dim intBOX
intBOX = 0
Dim strBOX
Dim arrCOL()
Dim intCOL
Dim booERR
booERR = False
Dim arrROW
arrROW = Split(cROW,",")
Dim intROW
intROW = 0
Dim strRST
Dim strVAL
'*
'* Declare Objects
'*
Dim objRST
Set objRST = CreateObject("ADODB.Recordset")
objRST.CursorLocation = adUseClient
objRST.CursorType = adOpenKeyset
objRST.LockType = adLockOptimistic
objRST.Open cSQL, cDRV & cXLS
'*
'* Worksheet to checkboxes
'*
Do Until objRST.EOF
intROW = intROW + 1
intCOL = 0
ReDim arrCOL(UBound(arrROW))
'*
For Each strRST In objRST.Fields
If intROW = 1 And strRST.Name <> arrROW(intCOL) Then
Alert("Invalid column heading: " & strRST.Name)
Exit Do
End If
arrCOL(intCOL) = strRST.Value
intCOL = intCOL + 1
If intCOL > UBound(arrROW) Then Exit For
Next
'*
strVAL = arrCOL(1) & "," & arrCOL(2)
intBOX = intBOX + 1
strBOX = cBOX
strBOX = Replace(strBOX,"#",intBOX)
strBOX = Replace(strBOX,"?",strVAL)
'*
If Not objFSO.FileExists(arrCOL(1)) _
Or objFSO.FileExists(arrCOL(2)) Then
strBOX = Replace(strBOX,"bgcW","bgcY")
booERR = True
End If
'*
ReDim Preserve arrBOX(intBOX)
arrBOX(intBOX) = strBOX & arrCOL(0)
objRST.MoveNext
Loop
'*
'* Finish
'*
Set objRST = Nothing
'*
document.getElementById("Counter").value = intBOX
document.getElementById("Results").innerHTML = Join(arrBOX,vbCrLf)
document.getElementById("Process").innerHTML = "Step 2"
If booERR Then
document.getElementById("Process").innerHTML = "Error!"
End If
End Sub
Sub Process_Step2()
'****
'* Step 2 = Rename files per the "checked" checkboxes.
'****
'*
'* Declare Variables
'*
Dim intBOX
intBOX = document.getElementById("Counter").value
Dim strBOX
Dim intCHK
intCHK = 0
Dim strCHK
Dim strDBG
Dim intERR
intERR = 0
Dim arrFIL
Dim intFOR
Dim strVAL
'*
'* Checkboxes to MoveFile
'*
For intFOR = 1 To intBOX
strBOX = "Box_" & intFOR
strCHK = document.getElementById(strBOX).checked
If strCHK Then
intCHK = intCHK + 1
strVAL = document.getElementById(strBOX).value
arrFIL = Split(strVAL,",")
strDBG = ""
If Not objFSO.FileExists(arrFIL(0)) Then
strDBG = "File does not exist! " & vbTab & arrFIL(0)
ElseIf objFSO.FileExists(arrFIL(1)) Then
strDBG = "File already exists! " & vbTab & arrFIL(1)
End If
'*
If strDBG <> "" Then
intERR = intERR + 1
document.getElementById(strBOX).className = "bgcR"
Else
strDBG = "MoveFile " & arrFIL(0) & ", " & arrFIL(1)
On Error Resume Next
objFSO.MoveFile arrFIL(0), arrFIL(1)
If Err.Number = 0 _
And objFSO.FileExists(arrFIL(1)) Then
document.getElementById(strBOX).className = "bgcG"
strDBG = "." & strDBG
Else
intERR = intERR + 1
document.getElementById(strBOX).className = "bgcO"
strDBG = "!" & strDBG
End If
On Error GoTo 0
End If
document.getElementById(strBOX).Title = strDBG
If cDBG Then Alert(strDBG)
End If
Next
'*
'* Finish
'*
If intCHK = 0 Then
Alert("No checkboxes were checked!")
Exit Sub
ElseIf intERR > 0 Then
Alert(intERR & " error(s) encountered!")
End If
'*
document.getElementById("Process").innerHTML = "Done!"
End Sub
</script>
<style type="text/css">
body,td,th { font-family:Arial; font-size:80% }
.bgcG { background-color:lightgreen }
.bgcO { background-color:orange }
.bgcR { background-color:red }
.bgcW { background-color:white }
.bgcY { background-color:yellow }
.butt { background-color:#EEEEEE; font-weight:bold; width:140px }
.line { border:solid 1px black }
</style>
</head>
<body>
<table border="0" cellpadding="0" cellspacing="0" width="600">
<tr>
<th class="line">
<br>'Renames.hta' renames files per 'Renames.xls'.
<br><br>
<button class="butt" id="Process" onclick="Process()">Step 1</button>
<button class="butt" onclick="window.close()">Quit</button>
<br><br>
</th>
</tr>
<tr>
<td>
<form method="post">
<input type="hidden" id="Counter" value="0">
<span id="Results"> </span>
</form>
</td>
</tr>
</table>
</body>
</html>