ladylexus
09-09-2008, 11:47 AM
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:
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
the trouble come with the following:
'Set objWB = objExcel.Workbooks.Open("C:\THEFILE.xls") or
Set objWB = objExcel.Workbooks.Open (strFileName)
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.
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:
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
the trouble come with the following:
'Set objWB = objExcel.Workbooks.Open("C:\THEFILE.xls") or
Set objWB = objExcel.Workbooks.Open (strFileName)
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.