Quick one for the archives.

Error message launching Excel 03 as shown in the title to this post.

Lots of complex solutions about nested named ranges and pivot tables on msdn
that I messsed with for a while when this started appearing while working on
a workbook with thousands of defined names.

The actual solution was a corrupted toolbar. So, search for the excel11.xlb
file and delete it. Problem solved.

Hope this helps someone in the future.

--
Robin Hammond
www.enhanceddatasystems.com

Thanks for sharing.

By the way, "thousands of defined names"? May I ask what project that is
and how you manage them all?

Matthew Pfluger

"Robin Hammond" wrote:

Quick one for the archives.

Error message launching Excel 03 as shown in the title to this post.

Lots of complex solutions about nested named ranges and pivot tables on msdn
that I messsed with for a while when this started appearing while working on
a workbook with thousands of defined names.

The actual solution was a corrupted toolbar. So, search for the excel11.xlb
file and delete it. Problem solved.

Hope this helps someone in the future.

--
Robin Hammond
www.enhanceddatasystems.com

Matthew,

It was an application for a large commercial bank. I can't elaborate I'm
afraid. We decided that everything had to work off named ranges to allow the
code and the interface to work seemlessly together, and had to make the
whole thing look like a web front end - an interesting challenge in itself.

Jan Karel Pieterse name manager was invaluable as was Rob Bovey's code
cleaner since we got out to about 20000 lines of code. We also built a lot
of our own utilities to manage name definitions, consistency, code control,
interface handling and such.

--
Robin Hammond
www.enhanceddatasystems.com


"Matthew Pfluger" wrote in
message ...
Thanks for sharing.

By the way, "thousands of defined names"? May I ask what project that is
and how you manage them all?

Matthew Pfluger

"Robin Hammond" wrote:

Quick one for the archives.

Error message launching Excel 03 as shown in the title to this post.

Lots of complex solutions about nested named ranges and pivot tables on
msdn
that I messsed with for a while when this started appearing while working
on
a workbook with thousands of defined names.

The actual solution was a corrupted toolbar. So, search for the
excel11.xlb
file and delete it. Problem solved.

Hope this helps someone in the future.

--
Robin Hammond
www.enhanceddatasystems.com

On Sep 27, 7:40 pm, "Robin Hammond"
wrote:
Matthew,

It was an application for a large commercial bank. I can't elaborate I'm
afraid. We decided that everything had to work off named ranges to allow the
code and the interface to work seemlessly together, and had to make the
whole thing look like a web front end - an interesting challenge in itself.

Jan Karel Pieterse name manager was invaluable as was Rob Bovey's code
cleaner since we got out to about 20000 lines of code. We also built a lot
of our own utilities to manage name definitions, consistency, code control,
interface handling and such.

--
Robin Hammondwww.enhanceddatasystems.com

"Matthew Pfluger" wrote in
...



Thanks for sharing.

By the way, "thousands of defined names"? May I ask what project that is
and how you manage them all?

Matthew Pfluger

"Robin Hammond" wrote:

Quick one for the archives.

Error message launching Excel 03 as shown in the title to this post.

Lots of complex solutions about nested named ranges and pivot tables on
msdn
that I messsed with for a while when this started appearing while working
on
a workbook with thousands of defined names.

The actual solution was a corrupted toolbar. So, search for the
excel11.xlb
file and delete it. Problem solved.

Hope this helps someone in the future.

--
Robin Hammond
www.enhanceddatasystems.com- Hide quoted text -

- Show quoted text -

Thanks for the post Robin. I have been fighting this issue for some
time. Had some eMail code that worked just great. Continued with the
project and on final testing the eMail routine created this same
error. I only have 155 NamedRanges with lots of calcs but somewhere
in the process something I did triggered the "Excel cannot complete
with available resources" error.
I searched my HD for excel11.xlb to try your cure but not there. I
have NO file named *.xlb.

Did you come across any other reason for the error in your testing?

Dennis

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 Hammond
www.enhanceddatasystems.com


"ssGuru" wrote in message
ps.com...
On Sep 27, 7:40 pm, "Robin Hammond"
wrote:
Matthew,

It was an application for a large commercial bank. I can't elaborate I'm
afraid. We decided that everything had to work off named ranges to allow
the
code and the interface to work seemlessly together, and had to make the
whole thing look like a web front end - an interesting challenge in
itself.

Jan Karel Pieterse name manager was invaluable as was Rob Bovey's code
cleaner since we got out to about 20000 lines of code. We also built a
lot
of our own utilities to manage name definitions, consistency, code
control,
interface handling and such.

--
Robin Hammondwww.enhanceddatasystems.com

"Matthew Pfluger" wrote in
...



Thanks for sharing.

By the way, "thousands of defined names"? May I ask what project that
is
and how you manage them all?

Matthew Pfluger

"Robin Hammond" wrote:

Quick one for the archives.

Error message launching Excel 03 as shown in the title to this post.

Lots of complex solutions about nested named ranges and pivot tables
on
msdn
that I messsed with for a while when this started appearing while
working
on
a workbook with thousands of defined names.

The actual solution was a corrupted toolbar. So, search for the
excel11.xlb
file and delete it. Problem solved.

Hope this helps someone in the future.

--
Robin Hammond
www.enhanceddatasystems.com- Hide quoted text -

- Show quoted text -

Thanks for the post Robin. I have been fighting this issue for some
time. Had some eMail code that worked just great. Continued with the
project and on final testing the eMail routine created this same
error. I only have 155 NamedRanges with lots of calcs but somewhere
in the process something I did triggered the "Excel cannot complete
with available resources" error.
I searched my HD for excel11.xlb to try your cure but not there. I
have NO file named *.xlb.

Did you come across any other reason for the error in your testing?

Dennis

On Sep 28, 6:19 pm, "Robin Hammond"
wrote:
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

"ssGuru" wrote in message

ps.com...



On Sep 27, 7:40 pm, "Robin Hammond"
wrote:
Matthew,

It was an application for a large commercial bank. I can't elaborate I'm
afraid. We decided that everything had to work off named ranges to allow
the
code and the interface to work seemlessly together, and had to make the
whole thing look like a web front end - an interesting challenge in
itself.

Jan Karel Pieterse name manager was invaluable as was Rob Bovey's code
cleaner since we got out to about 20000 lines of code. We also built a
lot
of our own utilities to manage name definitions, consistency, code
control,
interface handling and such.

--
Robin Hammondwww.enhanceddatasystems.com

"Matthew Pfluger" wrote in
...

Thanks for sharing.

By the way, "thousands of defined names"? May I ask what project that
is
and how you manage them all?

Matthew Pfluger

"Robin Hammond" wrote:

Quick one for the archives.

Error message launching Excel 03 as shown in the title to this post.

Lots of complex solutions about nested named ranges and pivot tables
on
msdn
that I messsed with for a while when this started appearing while
working
on
a workbook with thousands of defined names.

The actual solution was a corrupted toolbar. So, search for the
excel11.xlb
file and delete it. Problem solved.

Hope this helps someone in the future.

--
Robin Hammond
www.enhanceddatasystems.com-Hide quoted text -

- Show quoted text -

Thanks for the post Robin. I have been fighting this issue for some
time. Had some eMail code that worked just great. Continued with the
project and on final testing the eMail routine created this same
error. I only have 155 NamedRanges with lots of calcs but somewhere
in the process something I did triggered the "Excel cannot complete
with available resources" error.
I searched my HD for excel11.xlb to try your cure but not there. I
have NO file named *.xlb.

Did you come across any other reason for the error in your testing?

Dennis- Hide quoted text -

- 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

Dennis,

It took me a while to get to this. Busy week.

I tried it here on Excel03 and ExcelXP and it works fine for me, which
doesn't really help.

By the way, you might want to have a look at Outlook Redemption to get
around the security warning in Outlook.

--
Robin Hammond
www.enhanceddatasystems.com


"ssGuru" wrote in message
ps.com...
On Sep 28, 6:19 pm, "Robin Hammond"
wrote:
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

"ssGuru" wrote in message

ps.com...



On Sep 27, 7:40 pm, "Robin Hammond"
wrote:
Matthew,

It was an application for a large commercial bank. I can't elaborate
I'm
afraid. We decided that everything had to work off named ranges to
allow
the
code and the interface to work seemlessly together, and had to make
the
whole thing look like a web front end - an interesting challenge in
itself.

Jan Karel Pieterse name manager was invaluable as was Rob Bovey's code
cleaner since we got out to about 20000 lines of code. We also built
a
lot
of our own utilities to manage name definitions, consistency, code
control,
interface handling and such.

--
Robin Hammondwww.enhanceddatasystems.com

"Matthew Pfluger" wrote in
...

Thanks for sharing.

By the way, "thousands of defined names"? May I ask what project
that
is
and how you manage them all?

Matthew Pfluger

"Robin Hammond" wrote:

Quick one for the archives.

Error message launching Excel 03 as shown in the title to this
post.

Lots of complex solutions about nested named ranges and pivot
tables
on
msdn
that I messsed with for a while when this started appearing while
working
on
a workbook with thousands of defined names.

The actual solution was a corrupted toolbar. So, search for the
excel11.xlb
file and delete it. Problem solved.

Hope this helps someone in the future.

--
Robin Hammond
www.enhanceddatasystems.com-Hide quoted text -

- Show quoted text -

Thanks for the post Robin. I have been fighting this issue for some
time. Had some eMail code that worked just great. Continued with the
project and on final testing the eMail routine created this same
error. I only have 155 NamedRanges with lots of calcs but somewhere
in the process something I did triggered the "Excel cannot complete
with available resources" error.
I searched my HD for excel11.xlb to try your cure but not there. I
have NO file named *.xlb.

Did you come across any other reason for the error in your testing?

Dennis- Hide quoted text -

- 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