Force Closing Excel in Outlook

[Hacktastic]

Hi Everybody,

I have a code that converts a CSV to a XLS within outlook. This is needed because it runs in conjunction with a rule i set up.

Everything works fine, however when i try to open the file, it refuses to open and I have to CNTL+ALT+DEL my way out of Excel just to open the file.

Something is getting stuck somewhere. Any Ideas?

Sub concsvxls()

 
Dim myolapp As Excel.Application
 
''Delete File
Dim File As String
File = "Z:\test\reportEXL.xls"
KillProperly (File)


''Create new File for SSIS Package
    Workbooks.OpenText FileName:="Z:\test\report.csv", Origin:=xlWindows, _
        StartRow:=7, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
        TrailingMinusNumbers:=True
    ActiveWorkbook.SaveAs FileName:="Z:\google\reportEXL.xls", FileFormat:= _
        xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
 
Set myolapp = Nothing

End Sub

 

[PHV]

[!]myolapp.[/!]Workbooks.OpenText ...
[!]myolapp.[/!]ActiveWorkbook.SaveAs ...
[!]myolapp.Quit[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Hacktastic]

Hi PHV,

Thanks for your help, unfortunately, this doesn't work, it wants me to either declare the variable or put it in a with block, when i do put it in a with block, myolapp.quit closes Outlook (I'm running this code out of Outlook not Excel)

 

[PHV]

What is your actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gruuuu]

Do you have this code declaring myolapp as an Outlook.Application elsewhere?

 

[Hacktastic]

Thanks for the replies,

no i do not have myolapp declared anywhere else.

here is the modified code, it saves the file, however doesn't close the background run of excel(which keeps the newly created file as read only), and closes out of Outlook.

Sub concsvxls()

 
Dim myolapp As Excel.Application


''Delete File
Dim File As String
File = "Z:\google\reportEXL.xls"
KillProperly (File)

''Create new File for SSIS Package
     myolapp.Workbooks.OpenText FileName:="Z:\google\report.csv", Origin:=xlWindows, _
        StartRow:=7, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
        TrailingMinusNumbers:=True
     myolapp.ActiveWorkbook.SaveAs FileName:="Z:\google\reportEXL.xls", FileFormat:= _
        xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
        
        
  myolapp.Quit
   
  


End Sub

 

[Hacktastic]

actually....

DUR....


never set the new excel application

Dim myolapp As Excel.Application
Set myolapp = New Excel.Application

now everything works perfect

Thanks guys