Dennis,
Sounds annoying.
Are you running Office 2003? If not, you might need to search for
Excel10.xlb, or equivalent.
Is the problem just with one workbook?
If so, does the problem occur when you open the workbook, or when you run
the code? If it's code, is there a particular line that causes the fault?
Alternatively, there is something on msdn about the level of nested names.
Name1 refers to Name2 to Name3.... to NameXX exceeding 20 nests. Seems
unlikely given the scale of what you are doing.
Robin.
--
Robin Hammondwww.enhanceddatasystems.com
- Show quoted text -
Apparently you have chased the Excel resource ghost as well.
Using Excel 2003.
No "nested" RangeNames.
No *.xlb at all.
ERROR only occurs when I RUN the code which is mostly Ron deBruins
that I customized so it is well tested and documented.
I have two separate workbook templates that have the same code and
mostly the same RangeNames. Each uses range names and calculations
that refer to an external workbook that I use to house lookup tables.
Everything works great. It is just the email code in each that causes
the ERROR. Ron has offered to take a look at it but I don't want to
unnecessarily waste his time until I have exhausted my search.
Probably something I have or haven't done correctly.
I get two instances of the error during my email code.
The first occurs when the code creates a temporary file and renames
it. Later the file is attached to the email and then the tempory
file is deleted from the TEMP folder.
'DD Save file
.SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum
On Error Resume Next
I have checked the variable values and they look good.
The ERROR dialogue pops up while there is a temporary file named
Book?? which is open at this point and just BEFORE it gets renamed to
the TempFilePath & TempFileName. "070915TEST.xls" in this test case.
So I have apparently confused Excel about what it should do. I don't
really need nor want the temporary file open in order to attach it to
the email.
How can I change the SaveAs to save and close the file immediately?
OR, what can I do to resolve this ERROR? "Excel cannot complete this
task with available resources. Choose less data or close other
applications"
The second instance occurs when I try and SendMail. I believe that
this is because the temporary file now in the TEMP folder is still
open.
'DD .SendMail
.SendMail Recipients:=Array(eMain, eCopy1, eCopy2, eCopy3),
Subject:=(RptCreator & " Forcast " & ReportDate)
On Error GoTo 0
.Close savechanges:=False
End With
Complete code shown below.
Dennis
The Values for
TempFilePath VALUE IS: "C:\DOCUME~1\DDuffy\LOCALS~1\Temp\"
TempFileName VALUE IS: "070915TEST.xls"
FileFormatNum = -4143
Private Sub btnEMail_Click()
'Base Code courtesy of Ron DeBruin, modified by DDuffy
'070702-DD Mail Prospect WkSheet to selected Reciepient
'Works in Excel 97-2007, Tested with Outlook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim sh As Worksheet
Dim RptCreator As String
Dim ReportDate As String
Dim eMain As String, eCopy1 As String, eCopy2 As String, eCopy3 As
String
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set Sourcewb = ActiveWorkbook
'Copy the sheets to a new workbook
Sourcewb.Sheets(Array("DDInstructionPrice", "DDProspects")).Copy
Set Destwb = ActiveWorkbook
'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
'We exit the sub when your answer is NO in the security dialog that
you only
'see when you copy sheets from a xlsm file with macro's disabled.
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is NO in the security dialog"
Exit Sub
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With
'DD Save the new workbook/Mail it/Delete it
TempFilePath = Environ$("temp") & "\"
eMain = Range("eMailMain").Value
eCopy1 = Range("eMailCopy1").Value
eCopy2 = Range("eMailCopy2").Value
eCopy3 = Range("eMailCopy3").Value
RptCreator = Range("RptCreator").Value
ReportDate = Range("RptDate").Value
TempFileName = Format(ReportDate, "yymmdd") & "TEST" & FileExtStr
With Destwb
'DD Save file
.SaveAs TempFilePath & TempFileName, FileFormat:=FileFormatNum
On Error Resume Next
'DD .SendMail
.SendMail Recipients:=Array(eMain, eCopy1, eCopy2, eCopy3),
Subject:=(RptCreator & " Forcast " & ReportDate)
On Error GoTo 0
.Close savechanges:=False
End With
'DD Kill Temp File
Kill TempFilePath & TempFileName
'DD reset variables
eCopy1 = ""
eCopy2 = ""
eCopy3 = ""
RptCreator = ""
ReportDate = ""
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub