|
-
Sep 8th, 2012, 10:09 AM
#1
Thread Starter
Addicted Member
Why does Excel keep opening in read-only mode (the 2nd time around) ???
I have this procedure where I am trying to refresh pivot tables in a workbook, save it, close it, and then attach it in an e-mail, then, open the workbook again and do the process all over again but when the workbook opens up the 2nd time through the loop, it always opens up as read-only. What am I missing or forgetting to do?
Code:
' ------------ '
' loop reports '
' ------------ '
For X As Integer = 1 To rrec
' -------------------------------- '
' assign globals to current record '
' -------------------------------- '
gReport_ID = R(X).Report_ID
gReport_Name = R(X).Report_Name
gReport_Header = R(X).Report_Header
gEmail_Subject = R(X).Email_Subject
gEmail_To = R(X).Email_To
gEmail_From = R(X).Email_From
gEmail_CC = R(X).Email_CC
gEmail_BCC = R(X).Email_BCC
gEmail_Body = R(X).Email_Body
' ------------------------- '
' update the report pointer '
' ------------------------- '
UpdateReportPointer(X)
' ----------------------------------- '
' create the excel application object '
' ----------------------------------- '
Dim oApp As Object = CreateObject("Excel.Application")
Try
' ------------------------------- '
' this is the excel refresh piece '
' ------------------------------- '
With oApp
' ----------------------------------------------------- '
' turn these properties to false to speed up processing '
' ----------------------------------------------------- '
.Visible = True
.DisplayAlerts = False
'.Interactive = False
'.ScreenUpdating = False
' ----------------------- '
' open the excel workbook '
' ----------------------- '
.Workbooks.Open(Excel_File_Path, , False)
'''''''''''''''''''''''
' make RptHdr visible '
'''''''''''''''''''''''
.Sheets("RptHdr").Visible = True
'''''''''''''''''''''''''''''''''
' activate the RptHdr worksheet '
'''''''''''''''''''''''''''''''''
.Sheets("RptHdr").Activate()
''''''''''''''''''''''''''
' point to starting cell '
''''''''''''''''''''''''''
.Range("Rpt_Starting_Cell_Hdr").Select()
'''''''''''''''''
' refresh query '
'''''''''''''''''
.Selection.ListObject.QueryTable.Refresh(False)
'''''''''''''''''''''''''''''''''
' activate the Report worksheet '
'''''''''''''''''''''''''''''''''
.Sheets("Report").Activate()
''''''''''''''''''''''''''
' point to starting cell '
''''''''''''''''''''''''''
.Range("Rpt_Starting_Cell").Select()
'''''''''''''''''
' refresh query '
'''''''''''''''''
.Selection.ListObject.QueryTable.Refresh(False)
''''''''''''''''''''''''''''''''''''''''''''''
' point to the 1st detail cell of the report '
''''''''''''''''''''''''''''''''''''''''''''''
.Range("Detail_Cell").Select()
'''''''''''''''''''''''''''''''
' point back to starting cell '
'''''''''''''''''''''''''''''''
.Range("Rpt_Starting_Cell").Select()
'''''''''''''''''''''''''
' make RptHdr invisible '
'''''''''''''''''''''''''
.Sheets("RptHdr").Visible = False
'''''''''''''''''''''''''''''''
' save and close the workbook '
'''''''''''''''''''''''''''''''
.ActiveWorkbook.SaveAs(Excel_File_Path)
.ActiveWorkbook.Close()
'''''''''''''''''''''''''''''''''''''''''''''''''
' turn these properties to true before quitting '
'''''''''''''''''''''''''''''''''''''''''''''''''
With oApp
.Visible = True
.DisplayAlerts = True
.Interactive = True
.ScreenUpdating = True
End With
' --------------------------- '
' quit the application object '
' --------------------------- '
oApp.Quit()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' destroy excel object twice to make sure resources have been released '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
oApp = Nothing
oApp = Nothing
' --------------------------------------------------------------------------------- '
' garbage collection needs to be called twice in order to get the Finalizers called '
' the first time in, it simply makes a list of what is to be finalized, '
' the second time in, it actually does the finalizing. '
' Only then will the object do its automatic ReleaseComObject. '
' --------------------------------------------------------------------------------- '
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' destroy excel object twice to make sure resources have been released '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
oApp = Nothing
oApp = Nothing
' ------------------------------------------------------------------- '
' pause app to pause for 10 seconds after we destroy the excel object '
' ------------------------------------------------------------------- '
Thread.Sleep(10000)
'''''''''''''
' send mail '
'''''''''''''
Dim SendAttempts As Integer = 0
''''''''''''''''''''''''''''''''''''''''''''''''
' make 3 attempts to send mail before skipping '
''''''''''''''''''''''''''''''''''''''''''''''''
Do
SendAttempts += 1
R(X).Email_Success = SendMail(X, SendAttempts)
Loop Until R(X).Email_Success = "Success" Or SendAttempts > 2
' ----------------------------- '
' update report type on success '
' ----------------------------- '
If R(X).Email_Success = "Success" Then
UpdateReportType()
End If
'''''''''''''
' log email '
'''''''''''''
AddToReportLog(X)
End With
Catch ex As Exception
' --------------------------- '
' quit the application object '
' --------------------------- '
oApp.Quit()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' destroy excel object twice to make sure resources have been released '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
oApp = Nothing
oApp = Nothing
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' garbage collection needs to be called twice in order to get the Finalizers called '
' the first time in, it simply makes a list of what is to be finalized, '
' the second time in, it actually does the finalizing. '
' Only then will the object do its automatic ReleaseComObject. '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' destroy excel object twice to make sure resources have been released '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
oApp = Nothing
oApp = Nothing
blnExcelError = True
strExcelError = ex.ToString
End Try
If blnExcelError = True Then
Throw New Exception(strExcelError)
End If
Next X
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|