excel opened successfully

[sramey00]

hello,

I have some code that reads a tabbed delimited text with a file path to an excel document and also the password which opens that doc.

the script is to open the excel file and save it to a different location without a password.

It's working now and I am adding error logging incase the wrong password is supplied.

VBscript will not error if the wrong password is supplied, but excel will. Is there a way to programmatically tell if an instance of excel is successfully opened?

I have tried using err.num, but fails because it's the instance of excel that errors and not a vbscript runtime error.

DO WHILE txtofFiles.AtEndOfStream <> TRUE
	IF txtofFiles.AtEndOfStream <> TRUE THEN
		
		sLinetoWrite = ""
		strLine = txtofFiles.ReadLine
		
		sLinetoWrite = "Decrypting: " & strLine

		strPasswordPath = LEFT(strLine, INSTR(strLine, chr(9))-1)
		strPassword = RIGHT(strLine, LEN(strLine) - INSTRREV(strLine, chr(9)))
		strFileName = RIGHT(strPasswordPath, LEN(strPasswordPath) - INSTRREV(strPasswordPath, "\"))

		'MSGBOX strPasswordPath & vbcrlf & strPassword & vbcrlf & strFileName
		
		
		'********include error checking here to identify if excel opens correctly****
		objExcel.Workbooks.Open strPasswordPath,,,, strPassword
		objExcel.Workbooks(1).SaveAs strSavePath & strFileName,,""
		objExcel.Workbooks.Close
		'****************************************************************************

		txtLog.writeLine sLinetoWrite & " ... done"

	END IF
LOOP

 

[mrmovie]

'how about
intReturn = objExcel.Workbooks.Open(strPasswordPath,,,, strPassword)
Wscript.Echo intReturn

though i doubt it!! otherwise i guess it would have thrown a runtime..unless you ahve on error resume next turned on..

 

[sramey00]

Yea that doesn't work. i've tried that. the workbooks.open is a sub and doesn't return a value...

 

[mrmovie]

how about then trying to access a property of the 'supposedly' open workbook???

strTemp = objExcel.ActiveWorkbook.Name

i was just guessing at the syntax

 

[sramey00]

Mrmovie, thats a good idea. let me see if i can rig somethin to work with that.

i still think there's a way to access excel's err.num, but i dont know the proper propertes to reference... i.e objexcel.err.num is my ultimate goal!!

 

[PHV]

And what about this ?
Set objWB = objExcel.Workbooks.Open(strPasswordPath,,,, strPassword)
If Not (objWB Is Nothing) Then
objWB.SaveAs strSavePath & strFileName,,""
objWB.Close
Set objWB = Nothing
Else
sLinetoWrite = sLinetoWrite & " ERROR"
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sramey00]

ok so i think im a bit tired.

apparently, my testing wasn't correct and vbscript DOES raise an excel error through vbscript... all that for nothing!!

 

[sramey00]

Ok.. Heres the code incase anyone needs this....

The text file is created from a password cracking program and i use that file to locate the encrypted files and save them as an unprotected version of it... this is by no means to be used as a password decrypting program. the user has to supply the full path AND password to this script to unlock the file and save it in a different location.

'Script requirements: tabbed delimited text file containing full path location to password protected files and password.
' ex. c:\passworded xls\secret.xls	password
'also supply location to program for save as location


Dim objExcel
Set objExcel = WScript.CreateObject("Excel.Application")
set objFSO = createobject("Scripting.Filesystemobject")

strSavePath = "C:\Excel Remove Password\Decrypted\"
strPassList = "C:\Excel Remove Password\password list.txt"
strLog = "C:\Excel Remove Password\log.txt"


set txtLog = objFSO.CreateTextFile(strLog)
set txtofFiles = objFSO.opentextfile(strPassList)

txtLog.WriteLine "Obtaining files and passwords from: " & strPassList
txtLog.writeline "Save As Location: " & strSavePath
txtLog.WriteBlankLines(1)
txtLog.Writeline "Starting: " & now() & "****************************************************************"

ON ERROR RESUME NEXT

DO WHILE txtofFiles.AtEndOfStream <> TRUE
	IF txtofFiles.AtEndOfStream <> TRUE THEN
		
		sLinetoWrite = ""
		strLine = txtofFiles.ReadLine
		
		sLinetoWrite = "Decrypting: " & strLine

		strPasswordPath = LEFT(strLine, INSTR(strLine, chr(9))-1)
		strPassword = RIGHT(strLine, LEN(strLine) - INSTRREV(strLine, chr(9)))
		strFileName = RIGHT(strPasswordPath, LEN(strPasswordPath) - INSTRREV(strPasswordPath, "\"))

		objExcel.Workbooks.Open strPasswordPath,,,, strPassword

		IF err.number <> 0 THEN
			txtLog.WriteLine sLinetoWrite & " ... ERROR OPENING FILE - " & err. Number & " - " & err.description
			err.clear
		ELSE
			objExcel.Workbooks(1).SaveAs strSavePath & strFileName,,""
			objExcel.Workbooks.Close
			txtLog.writeLine sLinetoWrite & " ... done"
		END IF


	END IF
LOOP

txtlog.writeline "Completed: " & now() & "****************************************************************"

MSGBOX "Complete"

txtLog.close
txtoffiles.close
set txtoffiles = nothing
Set objExcel = Nothing