Outlook Macro sorts Excel worksheet but corrupts file?

[pebcak]

Hello everyone -

I wrote a macro in OUTLOOK that loops through five folders and compiles a list of emails in each and then moves the emails into an archive. The list builds on itself, finding the last row of data and adding the email information to the rows below it. I want to then sort the list by date received in descending order so the newest are on top. The below code works but sometimes the resulting Excel file is fine and sometimes it's corrupted. I get this message: "We found a problem with some contention in"Filename". Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes."

I had a lot of trouble with the releasing the Excel file from memory. Not sure if one has to do with the other but here's what I have - code for opening and closing the Excel file and the code for the sort in between.


Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlFile As String

'OPEN THE EXCEL FILE
xlFile = "U:\Temp\VBATest.xlsx"
Set xlBook = Workbooks.Open(xlFile)

'START LOOP FOR EACH FOLDER

'EXTRACT EMAIL INFO FROM FOLDER

'SORT THE WORKSHEET
With xlBook.Worksheets(strSheetName).Sort
.SortFields.Add Key:=Range("d1"), Order:=xlDescending
.SetRange Range("a:g")
.Header = xlYes
.Apply
End With

'LOOP BACK TO FINISH THE REMAINING FOLDERS

KillSub:

'CLOSE EXCEL FILE
xlBook.Close savechanges:=True
Set xlBook = Nothing



ADDITIONALLY - this forum, specifically the Outlook forum and @GMayor, have been a GREAT HELP to me as I've slowly (and painfully) learned this stuff. Is there anything I can do to give back - I'm not sure I'm experienced enough to be offering advice, but I also feel like I'm all taking and no giving!

Thanks.

-Joseph

[snb]

The strsheetrname variable seems to be empty

Keep it simple:


Sub M_snb()
with getobject("U:\Temp\VBATest.xlsx")
.sheets(1).usedrange.Sort .sheets(1).cells(1,4),2,,,,,,1
.close -1
end with
End Sub

NB. If you run this in Outlook xlDescending will not be recognized. Use it's parameter: 2
This also applies to xlYes

[Dave]

I'm not that familiar with outlook VBA, but it seems to me that the code neither creates an Excel application or terminates it? I don't understand how this code would run at all? Dave
To open the XL wb it seems like something like this...

Dim xExcelApp As Excel.Application
Set xExcelApp = New Excel.Application
'OPEN THE EXCEL FILE
xlFile = "U:\Temp\VBATest.xlsx"
'Set xlbook = Workbooks.Open(xlFile)
Set xlbook = xExcelApp.Workbooks.Open(xlFile)
to close and quit ...

'CLOSE EXCEL FILE
xlBook.Close savechanges:=True
Set xlBook = Nothing
xExcelApp.Quit
Set xExcelApp = Nothing