Using vbscript and setting an ADO recordset to a variable

Question

I have a lot of PDF files I am trying to rename based on criteria found in an excel spreadsheet. The spreadsheet lists the ID, last name, first name.

| 123456 | Smith | Joe |

I can open and read from excel fine and can create the ado connection and get a response to my query, but cannot find a way to take the part of the recordset that contains the filename and save as a variable.

The code I am using:(updated to current codeset)

'On Error Resume Next

LetterDirectory = InputBox("What letter are we scanning?")

DirectoryLocation = "C:\CLNotes\"
LetterDirectory = UCase(LetterDirectory)
SubDirectory = DirectoryLocation & LetterDirectory
FullDirectory = DirectoryLocation & LetterDirectory & "\*.pdf"

'Creating Excel objects
Set xl = CreateObject("Excel.Application")
Set xlBook = xl.Workbooks.Open("c:\CLnotes\dbo_Patient.xlsx")
Set xlSheet = xlBook.Worksheets(LetterDirectory)
xl.Visible = True

'Set Variables
xlRow = 1
totalRows = xl.WorksheetFunction.CountA(xlSheet.Range("A:A"))
oldFilename = 0

For xlRow = 1 to totalRows

LastName = xlSheet.Cells.Item(xlRow, 2).text
FirstName = xlSheet.Cells.Item(xlRow, 3).text
FullName = LastName & " " & Firstname
newFilename = xlSheet.Cells.Item(xlRow, 1).Text
'FullName = "b lorraine"

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open ("Provider=Search.CollatorDSO;Extended Properties='Application=Windows';")
objRecordSet.Open ("SELECT System.FileName FROM SYSTEMINDEX WHERE Contains('" & FullName & "')"), objConnection
objRecordSet.MoveFirst
oldFilename = objRecordset.Fields.Item("System.FileName")
Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("System.FileName")
    objRecordset.MoveNext
Loop
Next

Thanks.

Ekkehard.Horner · Accepted Answer · 2013-03-19 21:36:33Z

1

IF

Wscript.Echo objRecordset.Fields.Item("System.FileName")

outputs 'the part of the recordset that contains the filename' (you are interested in) AND

Set oldFilename = objRecordset.Fields.Item("System.FileName")

was your attempt to 'save [that value] as a variable' THEN

oldFilename = objRecordset.Fields.Item("System.FileName")

will solve your problem, because Set is to be used for assignment of objects only (not 'plain' data types like strings).

That Set var = non-object is the cause of your trouble - as I assume - is hidden by your use of the EVIL global On Error Resume Next.

answered Mar 19, 2013 at 21:36

Ekkehard.Horner

38.8k2 gold badges50 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

fiercebeard Over a year ago

I had actually tried it with set and without set, but I think the main thing holding me back was an unseen due to the evil error checking. I have since disabled that portion so I can actually see what's going on. It's actually stopping on the objRecordset.Open query. It's outputting "(null) (32, 1) : (null)".

Collectives™ on Stack Overflow

Using vbscript and setting an ADO recordset to a variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related