Solved: Cant open xls files saved from outlook using vb

[ladylexus]

Hello All,

I have a task to automate receiving data from a xls file and creating a csv file and there is no way to get around the file being an outlook attachment.

I can get the attachment from Outlook to the C drive using vb code I found on the net.

I can create a CSV file from the file using code I found on the net. The problem occurs when I try to open the excel attachment I saved to the C drive.

I get an "unable to get the open property of the workbooks class". I noticed when I emailed the excel file to myself the size changed. my test file was 358KB, in outlook the size is 364KB and when I use the vb code to save the file to disk the size is 360 KB.

The automated save file will not open. I can manually open the file in excel and if I save the file (not making any changes) the size is again 358KB and I can open the file using my vb code.

How can I get rid of the Outlook extras that been added, at least I think it's coming from outlook.

to get the file from oulook i use:

[VBA]Set WshShell = WScript.CreateObject("WScript.Shell")
Const olFolderInbox = 6
Dim strControl
Dim objExcel
Dim objWB
Dim obWS
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery _
("Select * From Win32_Process Where Name = 'outlook.exe'")
If colItems.Count = 0 Then
Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
objNamespace.Logon
Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)
Set colItems = objFolder.Items
Else
Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)
Set colItems = objFolder.Items
End If
For Each objMsg In objFolder.Items
With objMsg
If .Subject = "CASH052" Then
If Right(objMsg.Attachments.Item(1).FileName, 3) = "xls" Then
objMsg.Attachments.Item(1).SaveAsFile "C:\THEFILE.xls"
' .Delete
' MsgBox "THEFILE Data Saved"

End If
End If
End With
Next
Set objWMIService = Nothing
Set colItems = Nothing
Set objOutlook = Nothing
Set objNamespace = Nothing
Set objFolder = Nothing
Set objMsg = Nothing
Set objAttachments = Nothing
Set WshShell = Nothing

'and this works fine but I get the added size.
' to create the csv file I use

Set WshShell = WScript.CreateObject("WScript.Shell")
Dim objExcel
Dim objWB
Dim obWS
Dim objRange
Dim strFileName
strFileName = "C:\THEFILE.xls"
Set objExcel = CreateObject("Excel.Application")
'Set objWB = objExcel.Workbooks.Open("C:\THEFILE.xls")
Set objWB = objExcel.Workbooks.Open (strFileName)
Set objWS = objWB.worksheets(1)
objExcel.application.Visible = False
objExcel.application.DisplayAlerts = False
objWS.saveas "C:\CSVFile.csv", 6
objExcel.Workbooks("CSVFile.csv").Close, savechanges = True
objExcel.application.quit
Set objExcel = Nothing
Set objWB = Nothing
Set obWS = Nothing
Set objRange = Nothing[/VBA]

the trouble come with the following:
[VBA]'Set objWB = objExcel.Workbooks.Open("C:\THEFILE.xls") or
Set objWB = objExcel.Workbooks.Open (strFileName)[/VBA]

Hoping someone can point me in the right direction. I've looked up the "unable to open" error and it doesnt really apply to me but I may not savvy enough to know that it does apply.

[Mavyak]

I don't see any errors in the code except for a space between the word "Open" and the opening parenthese. It's kind of odd because the VBE automatically removes that space when I type it (after the cursor moves off the line). Usually when things like that happen, the error has to do with duplicate or errant variable declarations. But again, I don't see any in your code. The following code is very similar to what you have but it's my best rewrite attempt:
[vba]objExcel.Workbooks.Open strFileName
Set objWB = objExcel.Workbooks(1)[/vba]
If it were me, I'd set a reference to the Excel library and explicitly declare my variable types. That will allow the intellisense of the VBE to help you write the code.

[ladylexus]

Thanks for the suggestion Mavyak. I tried your option and I get a different message. "Open method of workbooks class failed". I tried the code on a machine with SP3 though.
The same save and open scenerio on the machine with SP3 gives the same "unable to get open ..." error.

But when I manually open the saved file in excel on the machine i get the new message
"excel found unreadable content in 'filename'. Do you want to recover the contents of the workbook?"

Choose the yes button and the file opens.

I'll try the changes on the SP2 machine to see what happens. I can't try that until Monday.

The frequency of the user emails is once or twice a month, so a workaround may be to create an add-in or maco the users can run from within excel.

[ladylexus]

Tried the code on the SP2 machine and got the message:
"open method of workbooks class failed".

I think I'll try the workaround.