Where else to get help?
 

Hi -

I've been having strange programmatic freezes after a long series of
automated cut/paste/select operations to import data into a workbook (see
previous post by me - Macro runs several times, then crashes...). No one in
this forum has been able to give me any suggestions for solving the problem,
and I was wondering if there were any other resources for intractible
problems.

Thanks,
Reuel

Where else to get help?
 

Since you have not stayed in the same thread it is difficult to tell what
you have tried. But, it sounds like you need to use
application.cutcopymode=false to release the clipboard after each copy/paste
and before closing the file.

Usually this forum is used to answer specific questions. It appears that you
have a project that requires professional help which is what many of us do
for a living

--
Don Guillett
SalesAid Software

"Reuel" wrote in message
...
Hi -

I've been having strange programmatic freezes after a long series of
automated cut/paste/select operations to import data into a workbook (see
previous post by me - Macro runs several times, then crashes...). No one
in
this forum has been able to give me any suggestions for solving the
problem,
and I was wondering if there were any other resources for intractible
problems.

Thanks,
Reuel

Where else to get help?
 

Thanks for your reply. After a week of kicking this problem around, I have
just noticed that the problem only occurs when working within an active sheet
that has a space in its name (i.e. after several successful operations on the
sheet, cutting, pasting, assigning values to cells & graphs, the nth
operation fails and halts the code). I think I can work around this problem
by avoiding it. Is this a known problem with Excel?
Again many thanks,
Reuel

"Don Guillett" wrote:

Since you have not stayed in the same thread it is difficult to tell what
you have tried. But, it sounds like you need to use
application.cutcopymode=false to release the clipboard after each copy/paste
and before closing the file.

Usually this forum is used to answer specific questions. It appears that you
have a project that requires professional help which is what many of us do
for a living

--
Don Guillett
SalesAid Software

"Reuel" wrote in message
...
Hi -

I've been having strange programmatic freezes after a long series of
automated cut/paste/select operations to import data into a workbook (see
previous post by me - Macro runs several times, then crashes...). No one
in
this forum has been able to give me any suggestions for solving the
problem,
and I was wondering if there were any other resources for intractible
problems.

Thanks,
Reuel

Where else to get help?
 

Hi Reuel,
Similar to keeping to the same thread, You are not showing your code
leaving everyone completely in the dark.

I may just be a failure to enclose the sheetname in single quotes.
See tp://www.mvps.org/dmcritchie/excel/sheets.htm

For issues related to speed see
http://www.mvps.org/dmcritchie/excel/slowresp.htm

Check what you have for error recovery, if you don't check the
error codes and you have On Error Resume Next
then you could be doing processing that you did not know about.

You might also have a problem with double spaces, numbers, dates
perhaps use cell.text insead of cell.value

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Reuel" wrote ..
just noticed that the problem only occurs when working within an active sheet
that has a space in its name (

Where else to get help?
 

By your own admission your source code works fine for most of the input
files, so it is not specifically a problem with the code, but rather it is a
problem with the files that it is accessing and how the code handles the
problem files. Since we do not have those files there is no way for us to
debug the code. As to whether this is a known problem with Excel first we
would have to know what exactly the problem is. Let us know which line of
code is crashing and some details of the file it is accessing at the time and
we can take a look and advise you of some of the common pitfalls with that
type of code.
--
HTH...

Jim Thomlinson


"Reuel" wrote:

Thanks for your reply. After a week of kicking this problem around, I have
just noticed that the problem only occurs when working within an active sheet
that has a space in its name (i.e. after several successful operations on the
sheet, cutting, pasting, assigning values to cells & graphs, the nth
operation fails and halts the code). I think I can work around this problem
by avoiding it. Is this a known problem with Excel?
Again many thanks,
Reuel

"Don Guillett" wrote:

Since you have not stayed in the same thread it is difficult to tell what
you have tried. But, it sounds like you need to use
application.cutcopymode=false to release the clipboard after each copy/paste
and before closing the file.

Usually this forum is used to answer specific questions. It appears that you
have a project that requires professional help which is what many of us do
for a living

--
Don Guillett
SalesAid Software

"Reuel" wrote in message
...
Hi -

I've been having strange programmatic freezes after a long series of
automated cut/paste/select operations to import data into a workbook (see
previous post by me - Macro runs several times, then crashes...). No one
in
this forum has been able to give me any suggestions for solving the
problem,
and I was wondering if there were any other resources for intractible
problems.

Thanks,
Reuel

Where else to get help?
 

Thanks for all the replies. I apologize for the length of this post, but
it address the questions asked.

A) I must retract my prior claim of success - the code or input data
set hasn't been changed since I ran it several times successfully, but
now it crashes consistantly.

B) For test purposes, the data files are all identical except for the
file name (they're just copies). So I don't really think the problem
lies in the data file.

C) The code is posted in the original thread, both in the body, and as
a zipped attachment, accessible he

http://www.excelforum.com/showthread.php?t=467235

The zipped attachment includes the workbook as well as sample data
files that the macro is designed to import and format.

D) I am including a new copy of the problem workbook/macro as an
attachment to this post as well (attachment may be inaccessible through
the microsoft forum; use www.excelforum.com).

The code in this post's attachment is now slightly modified from the
original post (a code segment that was run in a loop is now placed into
a subroutine so that I could clear the variables after each time the
code segment was run).

E) The specific line that causes the crash has varied over the duration
of the debugging project, here are some examples:

The code in the *original* post crashes at the pastelink command in
this code segment:

' Make offsets dynamic from Summary sheet
ActiveSheet.Range("e31:f31").Copy
Sheets(mySheet).Select
ActiveSheet.Range(PasteRange).Offset(-1, 7).Select
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False

The latest version of the code (included as an attachment to this post)
crashes on the 6th iteration at the Clear command in this code segment:

'Clear out old data
j = 0
Do While Not ActiveSheet.Range(PasteRange).Offset(j, 0).Value =
"Current(A)"
j = j + 1
Loop
ActiveSheet.Range(PasteRange).Select
ActiveSheet.Range(ActiveSheet.Range(PasteRange).Of fset(j + 1, 0),
ActiveCell.SpecialCells(xlLastCell)).Clear
'Selection.Clear 'ClearContents

Another common line that causes the crash is the assignment to the
Xdata series in this code segment:

With ActiveChart
HasTitle = True
ChartTitle.Characters.Text = mySheet &
ActiveSheet.Range(PasteRange).Offset(1, 1) 'Diode number
SeriesCollection(1).XValues = tempA 'Current

Sometimes the code just halts, other times I haveI get an error code,
e.g.:

Run time error -2147417847 (80010108)
Automation error
The object invoked has disconnected from its clients

Before posting this thread, I noticed a couple of other people had
posted inquiries about this error code, but no one had replied with
working solution.

When the code halts, sometimes Excel has completely crashed as well
(has to be restarted). For debugging purposes, I always kill and
restart Excel before testing the code; sometimes I reboot as well.

F) As for the suggestion to enclose the sheet name in single quotes:
the sheet name is held in a variable - so it could be done, but it
would be a little cumbersome (i.e. needed_string = "'" &
sheetnamevariable & "'" ). Perhaps that is a moot point now that the
code crashes even when the file name has no spaces.

G)

I would be very greatful if someone can point to what I've done that is
so objectionable to Excel.

Thanks again,
Reuel


+-------------------------------------------------------------------+
|Filename: ProblemWorkbook2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3831 |
+-------------------------------------------------------------------+

--
Reuel
------------------------------------------------------------------------
Reuel's Profile: http://www.excelforum.com/member.php...o&userid=27210
View this thread: http://www.excelforum.com/showthread...hreadid=468270

Where else to get help?
 

I took a quick look at your code and there a re a few things that should be
cleaned up before we continue. Your variables are declared incorrectly. Most
of them will be of type variant which can be more difficult to debug (not to
mention less efficient). Take a look at this page. I doubt it will fix your
problem but it will make the debugging easier...

http://www.cpearson.com/excel/variables.htm

Additionally you are mixing range objects with the active cell. If you
understand range objects you are better off to avoid referring to the active
cell as it is a lot more difficult to use in code as it is sheet dependant
and requires selections and a bunch of hoops to jump through that are not
necessary with regular range objects.

HTH...
Jim Thomlinson


"Reuel" wrote:


Thanks for all the replies. I apologize for the length of this post, but
it address the questions asked.

A) I must retract my prior claim of success - the code or input data
set hasn't been changed since I ran it several times successfully, but
now it crashes consistantly.

B) For test purposes, the data files are all identical except for the
file name (they're just copies). So I don't really think the problem
lies in the data file.

C) The code is posted in the original thread, both in the body, and as
a zipped attachment, accessible he

http://www.excelforum.com/showthread.php?t=467235

The zipped attachment includes the workbook as well as sample data
files that the macro is designed to import and format.

D) I am including a new copy of the problem workbook/macro as an
attachment to this post as well (attachment may be inaccessible through
the microsoft forum; use www.excelforum.com).

The code in this post's attachment is now slightly modified from the
original post (a code segment that was run in a loop is now placed into
a subroutine so that I could clear the variables after each time the
code segment was run).

E) The specific line that causes the crash has varied over the duration
of the debugging project, here are some examples:

The code in the *original* post crashes at the pastelink command in
this code segment:

' Make offsets dynamic from Summary sheet
ActiveSheet.Range("e31:f31").Copy
Sheets(mySheet).Select
ActiveSheet.Range(PasteRange).Offset(-1, 7).Select
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False

The latest version of the code (included as an attachment to this post)
crashes on the 6th iteration at the Clear command in this code segment:

'Clear out old data
j = 0
Do While Not ActiveSheet.Range(PasteRange).Offset(j, 0).Value =
"Current(A)"
j = j + 1
Loop
ActiveSheet.Range(PasteRange).Select
ActiveSheet.Range(ActiveSheet.Range(PasteRange).Of fset(j + 1, 0),
ActiveCell.SpecialCells(xlLastCell)).Clear
'Selection.Clear 'ClearContents

Another common line that causes the crash is the assignment to the
Xdata series in this code segment:

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = mySheet &
ActiveSheet.Range(PasteRange).Offset(1, 1) 'Diode number
.SeriesCollection(1).XValues = tempA 'Current

Sometimes the code just halts, other times I haveI get an error code,
e.g.:

Run time error -2147417847 (80010108)
Automation error
The object invoked has disconnected from its clients

Before posting this thread, I noticed a couple of other people had
posted inquiries about this error code, but no one had replied with
working solution.

When the code halts, sometimes Excel has completely crashed as well
(has to be restarted). For debugging purposes, I always kill and
restart Excel before testing the code; sometimes I reboot as well.

F) As for the suggestion to enclose the sheet name in single quotes:
the sheet name is held in a variable - so it could be done, but it
would be a little cumbersome (i.e. needed_string = "'" &
sheetnamevariable & "'" ). Perhaps that is a moot point now that the
code crashes even when the file name has no spaces.

G)

I would be very greatful if someone can point to what I've done that is
so objectionable to Excel.

Thanks again,
Reuel


+-------------------------------------------------------------------+
|Filename: ProblemWorkbook2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3831 |
+-------------------------------------------------------------------+

--
Reuel
------------------------------------------------------------------------
Reuel's Profile: http://www.excelforum.com/member.php...o&userid=27210
View this thread: http://www.excelforum.com/showthread...hreadid=468270

Where else to get help?
 

Thanks for the education on Dim!

Unfortunately, as you predicted, it did not solve the problem (I thought it
would).

As for your further suggestion, I take it to mean replace all instances of:

Range(somerange).select
Selection.Clear

with
Range(somerange).clear

Is this correct? If so, I have done that and the problem persists. If that's
not exactly what you were referring to, please explain further with an
example.

The only items of that nature that I wasn't able to condense were where I
select charts/ work with ActiveChart or select Shape, work with Active shape.
If those are important to eliminate as well, I'll have to study to figure out
how; I don't yet understand how to work with those directly.

Thanks again for your reply,
Reuel


"Jim Thomlinson" wrote:

I took a quick look at your code and there a re a few things that should be
cleaned up before we continue. Your variables are declared incorrectly. Most
of them will be of type variant which can be more difficult to debug (not to
mention less efficient). Take a look at this page. I doubt it will fix your
problem but it will make the debugging easier...

http://www.cpearson.com/excel/variables.htm

Additionally you are mixing range objects with the active cell. If you
understand range objects you are better off to avoid referring to the active
cell as it is a lot more difficult to use in code as it is sheet dependant
and requires selections and a bunch of hoops to jump through that are not
necessary with regular range objects.

HTH...
Jim Thomlinson


"Reuel" wrote:


Thanks for all the replies. I apologize for the length of this post, but
it address the questions asked.

A) I must retract my prior claim of success - the code or input data
set hasn't been changed since I ran it several times successfully, but
now it crashes consistantly.

B) For test purposes, the data files are all identical except for the
file name (they're just copies). So I don't really think the problem
lies in the data file.

C) The code is posted in the original thread, both in the body, and as
a zipped attachment, accessible he

http://www.excelforum.com/showthread.php?t=467235

The zipped attachment includes the workbook as well as sample data
files that the macro is designed to import and format.

D) I am including a new copy of the problem workbook/macro as an
attachment to this post as well (attachment may be inaccessible through
the microsoft forum; use www.excelforum.com).

The code in this post's attachment is now slightly modified from the
original post (a code segment that was run in a loop is now placed into
a subroutine so that I could clear the variables after each time the
code segment was run).

E) The specific line that causes the crash has varied over the duration
of the debugging project, here are some examples:

The code in the *original* post crashes at the pastelink command in
this code segment:

' Make offsets dynamic from Summary sheet
ActiveSheet.Range("e31:f31").Copy
Sheets(mySheet).Select
ActiveSheet.Range(PasteRange).Offset(-1, 7).Select
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False

The latest version of the code (included as an attachment to this post)
crashes on the 6th iteration at the Clear command in this code segment:

'Clear out old data
j = 0
Do While Not ActiveSheet.Range(PasteRange).Offset(j, 0).Value =
"Current(A)"
j = j + 1
Loop
ActiveSheet.Range(PasteRange).Select
ActiveSheet.Range(ActiveSheet.Range(PasteRange).Of fset(j + 1, 0),
ActiveCell.SpecialCells(xlLastCell)).Clear
'Selection.Clear 'ClearContents

Another common line that causes the crash is the assignment to the
Xdata series in this code segment:

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = mySheet &
ActiveSheet.Range(PasteRange).Offset(1, 1) 'Diode number
.SeriesCollection(1).XValues = tempA 'Current

Sometimes the code just halts, other times I haveI get an error code,
e.g.:

Run time error -2147417847 (80010108)
Automation error
The object invoked has disconnected from its clients

Before posting this thread, I noticed a couple of other people had
posted inquiries about this error code, but no one had replied with
working solution.

When the code halts, sometimes Excel has completely crashed as well
(has to be restarted). For debugging purposes, I always kill and
restart Excel before testing the code; sometimes I reboot as well.

F) As for the suggestion to enclose the sheet name in single quotes:
the sheet name is held in a variable - so it could be done, but it
would be a little cumbersome (i.e. needed_string = "'" &
sheetnamevariable & "'" ). Perhaps that is a moot point now that the
code crashes even when the file name has no spaces.

G)

I would be very greatful if someone can point to what I've done that is
so objectionable to Excel.

Thanks again,
Reuel


+-------------------------------------------------------------------+
|Filename: ProblemWorkbook2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3831 |
+-------------------------------------------------------------------+

--
Reuel
------------------------------------------------------------------------
Reuel's Profile: http://www.excelforum.com/member.php...o&userid=27210
View this thread: http://www.excelforum.com/showthread...hreadid=468270

Where else to get help?
 

Lets get the non chart stuff working first. Charting is just a fancy add on
at the end. The object model is also quite different and requires a bit of
practice.

You are correct in that the idea is not to select anything, but rather to
reference it. Your code should end up with virtually no ".select" or
"selection." code in it. Also it should not have activecell or
activeworksheet in it. They cause more difficulty than they solve. I have not
opened up the zip file that you created as I was hoping you could fix it up
first. If that is the case then you can e-mail me the files and I will take a
look at them...
--
HTH...

Jim Thomlinson


"Reuel" wrote:

Thanks for the education on Dim!

Unfortunately, as you predicted, it did not solve the problem (I thought it
would).

As for your further suggestion, I take it to mean replace all instances of:

Range(somerange).select
Selection.Clear

with
Range(somerange).clear

Is this correct? If so, I have done that and the problem persists. If that's
not exactly what you were referring to, please explain further with an
example.

The only items of that nature that I wasn't able to condense were where I
select charts/ work with ActiveChart or select Shape, work with Active shape.
If those are important to eliminate as well, I'll have to study to figure out
how; I don't yet understand how to work with those directly.

Thanks again for your reply,
Reuel


"Jim Thomlinson" wrote:

I took a quick look at your code and there a re a few things that should be
cleaned up before we continue. Your variables are declared incorrectly. Most
of them will be of type variant which can be more difficult to debug (not to
mention less efficient). Take a look at this page. I doubt it will fix your
problem but it will make the debugging easier...

http://www.cpearson.com/excel/variables.htm

Additionally you are mixing range objects with the active cell. If you
understand range objects you are better off to avoid referring to the active
cell as it is a lot more difficult to use in code as it is sheet dependant
and requires selections and a bunch of hoops to jump through that are not
necessary with regular range objects.

HTH...
Jim Thomlinson


"Reuel" wrote:


Thanks for all the replies. I apologize for the length of this post, but
it address the questions asked.

A) I must retract my prior claim of success - the code or input data
set hasn't been changed since I ran it several times successfully, but
now it crashes consistantly.

B) For test purposes, the data files are all identical except for the
file name (they're just copies). So I don't really think the problem
lies in the data file.

C) The code is posted in the original thread, both in the body, and as
a zipped attachment, accessible he

http://www.excelforum.com/showthread.php?t=467235

The zipped attachment includes the workbook as well as sample data
files that the macro is designed to import and format.

D) I am including a new copy of the problem workbook/macro as an
attachment to this post as well (attachment may be inaccessible through
the microsoft forum; use www.excelforum.com).

The code in this post's attachment is now slightly modified from the
original post (a code segment that was run in a loop is now placed into
a subroutine so that I could clear the variables after each time the
code segment was run).

E) The specific line that causes the crash has varied over the duration
of the debugging project, here are some examples:

The code in the *original* post crashes at the pastelink command in
this code segment:

' Make offsets dynamic from Summary sheet
ActiveSheet.Range("e31:f31").Copy
Sheets(mySheet).Select
ActiveSheet.Range(PasteRange).Offset(-1, 7).Select
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False

The latest version of the code (included as an attachment to this post)
crashes on the 6th iteration at the Clear command in this code segment:

'Clear out old data
j = 0
Do While Not ActiveSheet.Range(PasteRange).Offset(j, 0).Value =
"Current(A)"
j = j + 1
Loop
ActiveSheet.Range(PasteRange).Select
ActiveSheet.Range(ActiveSheet.Range(PasteRange).Of fset(j + 1, 0),
ActiveCell.SpecialCells(xlLastCell)).Clear
'Selection.Clear 'ClearContents

Another common line that causes the crash is the assignment to the
Xdata series in this code segment:

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = mySheet &
ActiveSheet.Range(PasteRange).Offset(1, 1) 'Diode number
.SeriesCollection(1).XValues = tempA 'Current

Sometimes the code just halts, other times I haveI get an error code,
e.g.:

Run time error -2147417847 (80010108)
Automation error
The object invoked has disconnected from its clients

Before posting this thread, I noticed a couple of other people had
posted inquiries about this error code, but no one had replied with
working solution.

When the code halts, sometimes Excel has completely crashed as well
(has to be restarted). For debugging purposes, I always kill and
restart Excel before testing the code; sometimes I reboot as well.

F) As for the suggestion to enclose the sheet name in single quotes:
the sheet name is held in a variable - so it could be done, but it
would be a little cumbersome (i.e. needed_string = "'" &
sheetnamevariable & "'" ). Perhaps that is a moot point now that the
code crashes even when the file name has no spaces.

G)

I would be very greatful if someone can point to what I've done that is
so objectionable to Excel.

Thanks again,
Reuel


+-------------------------------------------------------------------+
|Filename: ProblemWorkbook2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3831 |
+-------------------------------------------------------------------+

--
Reuel
------------------------------------------------------------------------
Reuel's Profile: http://www.excelforum.com/member.php...o&userid=27210
View this thread: http://www.excelforum.com/showthread...hreadid=468270

Where else to get help?
 

Jim,

You are welcome to this one, but I wanted to point out one
thing that may/maynot contribute to Reuel's problem...

"ActiveSheet.Range(PasteRange).Offset(-1, 7).Select"

If PasteRange is a "named" range it needs quote marks.
If it is a range object then just PasteRange.Offset(-1, 7).Select should be used.
If PasteRange is not on the active sheet then...
ActiveSheet.Range(PasteRange.Address).Offset(-1, 7).Select should be used.

I know you know all this, but Reuel provided a awful lot of code to examine.

Regards,
Jim Cone
San Francisco, USA


"Jim Thomlinson"

wrote in message
...
Lets get the non chart stuff working first. Charting is just a fancy add on
at the end. The object model is also quite different and requires a bit of
practice.

You are correct in that the idea is not to select anything, but rather to
reference it. Your code should end up with virtually no ".select" or
"selection." code in it. Also it should not have activecell or
activeworksheet in it. They cause more difficulty than they solve. I have not
opened up the zip file that you created as I was hoping you could fix it up
first. If that is the case then you can e-mail me the files and I will take a
look at them...
--
HTH...

Jim Thomlinson

Where else to get help?
 

I was going to remove all of the activesheet, selects and the like. They
cause more problems than you can shake a stick at...
--
HTH...

Jim Thomlinson


"Jim Cone" wrote:

Jim,

You are welcome to this one, but I wanted to point out one
thing that may/maynot contribute to Reuel's problem...

"ActiveSheet.Range(PasteRange).Offset(-1, 7).Select"

If PasteRange is a "named" range it needs quote marks.
If it is a range object then just PasteRange.Offset(-1, 7).Select should be used.
If PasteRange is not on the active sheet then...
ActiveSheet.Range(PasteRange.Address).Offset(-1, 7).Select should be used.

I know you know all this, but Reuel provided a awful lot of code to examine.

Regards,
Jim Cone
San Francisco, USA


"Jim Thomlinson"

wrote in message
...
Lets get the non chart stuff working first. Charting is just a fancy add on
at the end. The object model is also quite different and requires a bit of
practice.

You are correct in that the idea is not to select anything, but rather to
reference it. Your code should end up with virtually no ".select" or
"selection." code in it. Also it should not have activecell or
activeworksheet in it. They cause more difficulty than they solve. I have not
opened up the zip file that you created as I was hoping you could fix it up
first. If that is the case then you can e-mail me the files and I will take a
look at them...
--
HTH...

Jim Thomlinson

Where else to get help?
 

OK - Thanks to all that have contributed, and a very special thanks to Jim
Thomlinson and David McRitchie. There seems to have been a couple of problems
that led to intermittent, apparently pseudorandom crashes with the error code
Run time error -2147417847 (80010108).

Problem 1: Repeated use Range.Select/Selection.Action seems to cause
problems, ie:

Range("A1").Select
Selection.Copy 'or some other action

Should be condensed to

Range("A1").Copy 'or some other action



Problem 2: I think this is related to the 1st problem - The following code
section would randomly fail after a number of iterations:

'Range("A1", Range("A1").Offset(j, 0). _
SpecialCells(xlLastCell)).Clear

I think there was some problem with using SpecialCells(xlLastCell). Rather
than calling this special cell, I replaced the statement with a loop that
looked for a blank cell, indicating the end of the cell range:

end_index = 0 'Find out length of range
Do
end_index = end_index + 1
Loop While Not IsEmpty(Range("A1").Offset(end_index + 1, 0))

Range("A1",Range("A1").Offset(end_index,0).Clear



Problem 3: When calling a range on a different sheet, be sure to enclose the
referenced sheetname in single quotes, i.e:

Don't just use: Range("mySheet!A1")
Better: Range(" 'mySheet'!A1")

This prevents problems when the sheetname has spaces in it.
_______________________________

Again, a special thanks to all took the time to review my code and
contribute to this thread!!! Your assistance is greatly appreciated!!!

There is another problem with this iterative code (only ~30 copysheet
actions are allowed in the workbook), but as it is distinct from the problem
addressed in this thread, I'll post it in a new thread.

-Reuel





"Jim Thomlinson" wrote:

Lets get the non chart stuff working first. Charting is just a fancy add on
at the end. The object model is also quite different and requires a bit of
practice.

You are correct in that the idea is not to select anything, but rather to
reference it. Your code should end up with virtually no ".select" or
"selection." code in it. Also it should not have activecell or
activeworksheet in it. They cause more difficulty than they solve. I have not
opened up the zip file that you created as I was hoping you could fix it up
first. If that is the case then you can e-mail me the files and I will take a
look at them...
--
HTH...

Jim Thomlinson


"Reuel" wrote:

Thanks for the education on Dim!

Unfortunately, as you predicted, it did not solve the problem (I thought it
would).

As for your further suggestion, I take it to mean replace all instances of:

Range(somerange).select
Selection.Clear

with
Range(somerange).clear

Is this correct? If so, I have done that and the problem persists. If that's
not exactly what you were referring to, please explain further with an
example.

The only items of that nature that I wasn't able to condense were where I
select charts/ work with ActiveChart or select Shape, work with Active shape.
If those are important to eliminate as well, I'll have to study to figure out
how; I don't yet understand how to work with those directly.

Thanks again for your reply,
Reuel


"Jim Thomlinson" wrote:

I took a quick look at your code and there a re a few things that should be
cleaned up before we continue. Your variables are declared incorrectly. Most
of them will be of type variant which can be more difficult to debug (not to
mention less efficient). Take a look at this page. I doubt it will fix your
problem but it will make the debugging easier...

http://www.cpearson.com/excel/variables.htm

Additionally you are mixing range objects with the active cell. If you
understand range objects you are better off to avoid referring to the active
cell as it is a lot more difficult to use in code as it is sheet dependant
and requires selections and a bunch of hoops to jump through that are not
necessary with regular range objects.

HTH...
Jim Thomlinson


"Reuel" wrote:


Thanks for all the replies. I apologize for the length of this post, but
it address the questions asked.

A) I must retract my prior claim of success - the code or input data
set hasn't been changed since I ran it several times successfully, but
now it crashes consistantly.

B) For test purposes, the data files are all identical except for the
file name (they're just copies). So I don't really think the problem
lies in the data file.

C) The code is posted in the original thread, both in the body, and as
a zipped attachment, accessible he

http://www.excelforum.com/showthread.php?t=467235

The zipped attachment includes the workbook as well as sample data
files that the macro is designed to import and format.

D) I am including a new copy of the problem workbook/macro as an
attachment to this post as well (attachment may be inaccessible through
the microsoft forum; use www.excelforum.com).

The code in this post's attachment is now slightly modified from the
original post (a code segment that was run in a loop is now placed into
a subroutine so that I could clear the variables after each time the
code segment was run).

E) The specific line that causes the crash has varied over the duration
of the debugging project, here are some examples:

The code in the *original* post crashes at the pastelink command in
this code segment:

' Make offsets dynamic from Summary sheet
ActiveSheet.Range("e31:f31").Copy
Sheets(mySheet).Select
ActiveSheet.Range(PasteRange).Offset(-1, 7).Select
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False

The latest version of the code (included as an attachment to this post)
crashes on the 6th iteration at the Clear command in this code segment:

'Clear out old data
j = 0
Do While Not ActiveSheet.Range(PasteRange).Offset(j, 0).Value =
"Current(A)"
j = j + 1
Loop
ActiveSheet.Range(PasteRange).Select
ActiveSheet.Range(ActiveSheet.Range(PasteRange).Of fset(j + 1, 0),
ActiveCell.SpecialCells(xlLastCell)).Clear
'Selection.Clear 'ClearContents

Another common line that causes the crash is the assignment to the
Xdata series in this code segment:

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = mySheet &
ActiveSheet.Range(PasteRange).Offset(1, 1) 'Diode number
.SeriesCollection(1).XValues = tempA 'Current

Sometimes the code just halts, other times I haveI get an error code,
e.g.:

Run time error -2147417847 (80010108)
Automation error
The object invoked has disconnected from its clients

Before posting this thread, I noticed a couple of other people had
posted inquiries about this error code, but no one had replied with
working solution.

When the code halts, sometimes Excel has completely crashed as well
(has to be restarted). For debugging purposes, I always kill and
restart Excel before testing the code; sometimes I reboot as well.

F) As for the suggestion to enclose the sheet name in single quotes:
the sheet name is held in a variable - so it could be done, but it
would be a little cumbersome (i.e. needed_string = "'" &
sheetnamevariable & "'" ). Perhaps that is a moot point now that the
code crashes even when the file name has no spaces.

G)

I would be very greatful if someone can point to what I've done that is
so objectionable to Excel.

Thanks again,
Reuel


+-------------------------------------------------------------------+
|Filename: ProblemWorkbook2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3831 |
+-------------------------------------------------------------------+

--
Reuel
------------------------------------------------------------------------
Reuel's Profile: http://www.excelforum.com/member.php...o&userid=27210
View this thread: http://www.excelforum.com/showthread...hreadid=468270