vba editor does not like something about this????


Sub Macro1()



Application.Workbooks("research.xls").Worksheets
("sheet1").Range("a1").ClearContents

End Sub

What makes you think that it "does not like" that? Do you get an
error? If so, what does the error say?

Ray at work

"Coyote" wrote in message
...
vba editor does not like something about this????


Sub Macro1()



Application.Workbooks("research.xls").Worksheets
("sheet1").Range("a1").ClearContents

End Sub

I get the standard error dialog box where I press the
debug button and the statement is highlighted in yellow.

Is that what you mena??
-----Original Message-----
What makes you think that it "does not like" that? Do
you get an
error? If so, what does the error say?

Ray at work

"Coyote" wrote in
message
...
vba editor does not like something about this????


Sub Macro1()



Application.Workbooks("research.xls").Worksheets
("sheet1").Range("a1").ClearContents

End Sub



.

Is that all on one line?

Do you have an open workbook Research.xls, which has a sheet names Sheet1?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ray Costanzo [MVP]" <my first name at lane 34 dot commercial wrote in
message ...
What makes you think that it "does not like" that? Do you get an
error? If so, what does the error say?

Ray at work

"Coyote" wrote in message
...
vba editor does not like something about this????


Sub Macro1()



Application.Workbooks("research.xls").Worksheets
("sheet1").Range("a1").ClearContents

End Sub

Is the workbook open? If not, open it.

Perhaps you have misspelled the workbook name or the worksheet name or either
does not exist?

The two lines should be one line.

Try a continuation character

Application.Workbooks("research.xls").Worksheets _
("sheet1").Range("a1").ClearContents


Gord Dibben Excel MVP

On Thu, 30 Dec 2004 12:12:34 -0800, "Coyote"
wrote:

vba editor does not like something about this????


Sub Macro1()



Application.Workbooks("research.xls").Worksheet s
("sheet1").Range("a1").ClearContents

End Sub

That is all there is in the macro:

Application.Workbooks("research.xls").Worksheets
("sheet1").Range("a1").ClearContents

Yes the workbook research.xls is open

The macro is in another workbook - macros.xls



-----Original Message-----
Is that all on one line?

Do you have an open workbook Research.xls, which has a
sheet names Sheet1?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ray Costanzo [MVP]" <my first name at lane 34 dot
commercial wrote in
message ...
What makes you think that it "does not like" that? Do
you get an
error? If so, what does the error say?

Ray at work

"Coyote" wrote in
message
...
vba editor does not like something about this????


Sub Macro1()



Application.Workbooks("research.xls").Worksheets
("sheet1").Range("a1").ClearContents

End Sub





.

What is the text in that dialog box before you click the debug
button?

Ray at work

"Coyote" wrote in message
...
I get the standard error dialog box where I press the
debug button and the statement is highlighted in yellow.

Is that what you mena??
-----Original Message-----
What makes you think that it "does not like" that? Do
you get an
error? If so, what does the error say?

Ray at work

"Coyote" wrote in
message
...
vba editor does not like something about this????


Sub Macro1()



Application.Workbooks("research.xls").Worksheets
("sheet1").Range("a1").ClearContents

End Sub



.

checked spelling and made sure both exist.

error says subscript out of range

error #9
-----Original Message-----
Is the workbook open? If not, open it.

Perhaps you have misspelled the workbook name or the
worksheet name or either
does not exist?

The two lines should be one line.

Try a continuation character

Application.Workbooks("research.xls").Worksheet s _
("sheet1").Range("a1").ClearContents


Gord Dibben Excel MVP

On Thu, 30 Dec 2004 12:12:34 -0800, "Coyote"
wrote:

vba editor does not like something about this????


Sub Macro1()



Application.Workbooks("research.xls").Workshee ts
("sheet1").Range("a1").ClearContents

End Sub

.

runtime error #9
subscript out of range


-----Original Message-----
What is the text in that dialog box before you click the
debug
button?

Ray at work

"Coyote" wrote in
message
...
I get the standard error dialog box where I press the
debug button and the statement is highlighted in yellow.

Is that what you mena??
-----Original Message-----
What makes you think that it "does not like" that? Do
you get an
error? If so, what does the error say?

Ray at work

"Coyote" wrote in
message
...
vba editor does not like something about this????


Sub Macro1()



Application.Workbooks("research.xls").Worksheets
("sheet1").Range("a1").ClearContents

End Sub



.



.

As Gordon said:

Sub Macro1()

Application.Workbooks("research.xls") _
.Worksheets("sheet1") _
.Range("a1").ClearContents

End Sub

--
Regards,
Tom Ogilvy


"Coyote" wrote in message
...
checked spelling and made sure both exist.

error says subscript out of range

error #9
-----Original Message-----
Is the workbook open? If not, open it.

Perhaps you have misspelled the workbook name or the
worksheet name or either
does not exist?

The two lines should be one line.

Try a continuation character

Application.Workbooks("research.xls").Worksheet s _
("sheet1").Range("a1").ClearContents


Gord Dibben Excel MVP

On Thu, 30 Dec 2004 12:12:34 -0800, "Coyote"
wrote:

vba editor does not like something about this????


Sub Macro1()



Application.Workbooks("research.xls").Workshee ts
("sheet1").Range("a1").ClearContents

End Sub

.

Alright, either you don't have a workbook named research.xls, or that
workbook doesn't have a sheet named sheet1. Try this:

Sub kjasdf()
Dim x
On Error Resume Next
Set x = Application.Workbooks("research.xls")
MsgBox "After attempting to get the workbook, research.xls, the error
is " & Err.Number & ": " & Err.Description
Err.Clear
Set x = x.Worksheets("sheet1")
MsgBox "After attempting to get the worksheet, sheet1, the error is "
& Err.Number & ": " & Err.Description
Err.Clear
End Sub

Which of those msgboxes has an err.number that is not 0? Both or the
second one?

Ray at work



"Coyote" wrote in message
...
runtime error #9
subscript out of range


-----Original Message-----
What is the text in that dialog box before you click the
debug
button?

Ray at work

"Coyote" wrote in
message
...
I get the standard error dialog box where I press the
debug button and the statement is highlighted in yellow.

Is that what you mena??
-----Original Message-----
What makes you think that it "does not like" that? Do
you get an
error? If so, what does the error say?

Ray at work

"Coyote" wrote in
message
...
vba editor does not like something about this????


Sub Macro1()



Application.Workbooks("research.xls").Worksheets
("sheet1").Range("a1").ClearContents

End Sub



.



.

both
-----Original Message-----
Alright, either you don't have a workbook named
research.xls, or that
workbook doesn't have a sheet named sheet1. Try this:

Sub kjasdf()
Dim x
On Error Resume Next
Set x = Application.Workbooks("research.xls")
MsgBox "After attempting to get the workbook,
research.xls, the error
is " & Err.Number & ": " & Err.Description
Err.Clear
Set x = x.Worksheets("sheet1")
MsgBox "After attempting to get the worksheet, sheet1,
the error is "
& Err.Number & ": " & Err.Description
Err.Clear
End Sub

Which of those msgboxes has an err.number that is not 0?
Both or the
second one?

Ray at work



"Coyote" wrote in
message
...
runtime error #9
subscript out of range


-----Original Message-----
What is the text in that dialog box before you click
the
debug
button?

Ray at work

"Coyote" wrote in
message
...
I get the standard error dialog box where I press the
debug button and the statement is highlighted in
yellow.

Is that what you mena??
-----Original Message-----
What makes you think that it "does not like" that?
Do
you get an
error? If so, what does the error say?

Ray at work

"Coyote"
wrote in
message
...
vba editor does not like something about this????


Sub Macro1()



Application.Workbooks("research.xls").Worksheets
("sheet1").Range("a1").ClearContents

End Sub



.



.



.

Alright, so then there is no workbook open called research.xls.

What does this code display?

Sub kj39iadf()

For Each x In Application.Workbooks
MsgBox x.Name
Next

End Sub


"Coyote" wrote in message
...
both
-----Original Message-----
Alright, either you don't have a workbook named
research.xls, or that
workbook doesn't have a sheet named sheet1. Try this:

Sub kjasdf()
Dim x
On Error Resume Next
Set x = Application.Workbooks("research.xls")
MsgBox "After attempting to get the workbook,
research.xls, the error
is " & Err.Number & ": " & Err.Description
Err.Clear

Just a thought but is Research.xls open in a seperate instance of Excel. If
so then it is possible that although research is open that the macro can not
see it...

Just a thought...

"Ray Costanzo [MVP]" wrote:

Alright, so then there is no workbook open called research.xls.

What does this code display?

Sub kj39iadf()

For Each x In Application.Workbooks
MsgBox x.Name
Next

End Sub


"Coyote" wrote in message
...
both
-----Original Message-----
Alright, either you don't have a workbook named
research.xls, or that
workbook doesn't have a sheet named sheet1. Try this:

Sub kjasdf()
Dim x
On Error Resume Next
Set x = Application.Workbooks("research.xls")
MsgBox "After attempting to get the workbook,
research.xls, the error
is " & Err.Number & ": " & Err.Description
Err.Clear

you are right, all the macro sees is book1. it cannot see
research.xls - which is running in a separate instance of
excel

-----Original Message-----
Alright, so then there is no workbook open called
research.xls.

What does this code display?

Sub kj39iadf()

For Each x In Application.Workbooks
MsgBox x.Name
Next

End Sub


"Coyote" wrote in
message
...
both
-----Original Message-----
Alright, either you don't have a workbook named
research.xls, or that
workbook doesn't have a sheet named sheet1. Try this:

Sub kjasdf()
Dim x
On Error Resume Next
Set x = Application.Workbooks("research.xls")
MsgBox "After attempting to get the workbook,
research.xls, the error
is " & Err.Number & ": " & Err.Description
Err.Clear


.

I've been sitting here just ~assuming~ the whole time that Excel was
one of those application that didn't support multiple instances of
itself running at the same time. But, well, sure enough I just
opened a second instance of Excel with two separate process IDs.
Blushing. :]

The two instances are aware of each other though. Instance 2 opened
with Book4 as the default workbook name, as I already had 1, 2, 3 in
the first instance. I then created book5 in instance 1, went back to
instance 2, created another workbook, and that created as book6.

But I think you're probably right about what's going on.

Ray at work

"Jim Thomlinson" wrote in
message ...
Just a thought but is Research.xls open in a seperate instance of
Excel. If
so then it is possible that although research is open that the
macro can not
see it...

Good thing you caught me on a Thursday. If this was a Friday or a Monday you
would have been screwed. That is a tough one to catch...

"Ray Costanzo [MVP]" wrote:

I've been sitting here just ~assuming~ the whole time that Excel was
one of those application that didn't support multiple instances of
itself running at the same time. But, well, sure enough I just
opened a second instance of Excel with two separate process IDs.
Blushing. :]

The two instances are aware of each other though. Instance 2 opened
with Book4 as the default workbook name, as I already had 1, 2, 3 in
the first instance. I then created book5 in instance 1, went back to
instance 2, created another workbook, and that created as book6.

But I think you're probably right about what's going on.

Ray at work

"Jim Thomlinson" wrote in
message ...
Just a thought but is Research.xls open in a seperate instance of
Excel. If
so then it is possible that although research is open that the
macro can not
see it...

In my present setup - I am running two instances of excel
and transfering data between the two via a huge pasted
link array; but have the idea that a vba program that
transmits the data a piece at a time would be more
efficient than the big pasted link.

That is why I am trying to figure out how to accomplish
the task I have been asking about. I figured if I could
get that little part to work, then I could write the macro
to accomplish the larger task.

But so far, I am unable to get vba in one instance to see
the other instance.


-----Original Message-----
I've been sitting here just ~assuming~ the whole time
that Excel was
one of those application that didn't support multiple
instances of
itself running at the same time. But, well, sure enough
I just
opened a second instance of Excel with two separate
process IDs.
Blushing. :]

The two instances are aware of each other though.
Instance 2 opened
with Book4 as the default workbook name, as I already had
1, 2, 3 in
the first instance. I then created book5 in instance 1,
went back to
instance 2, created another workbook, and that created as
book6.

But I think you're probably right about what's going on.

Ray at work

"Jim Thomlinson"
wrote in
message news:5DE756F1-953F-4197-B8FB-
...
Just a thought but is Research.xls open in a seperate
instance of
Excel. If
so then it is possible that although research is open
that the
macro can not
see it...



.

There is probably no reason to have two instances of excel. Excel can have
multiple workbooks open in a single instance and you will not run into this
problem.

--
Regards,
Tom Ogilvy

"Coyote" wrote in message
...
In my present setup - I am running two instances of excel
and transfering data between the two via a huge pasted
link array; but have the idea that a vba program that
transmits the data a piece at a time would be more
efficient than the big pasted link.

That is why I am trying to figure out how to accomplish
the task I have been asking about. I figured if I could
get that little part to work, then I could write the macro
to accomplish the larger task.

But so far, I am unable to get vba in one instance to see
the other instance.


-----Original Message-----
I've been sitting here just ~assuming~ the whole time
that Excel was
one of those application that didn't support multiple
instances of
itself running at the same time. But, well, sure enough
I just
opened a second instance of Excel with two separate
process IDs.
Blushing. :]

The two instances are aware of each other though.
Instance 2 opened
with Book4 as the default workbook name, as I already had
1, 2, 3 in
the first instance. I then created book5 in instance 1,
went back to
instance 2, created another workbook, and that created as
book6.

But I think you're probably right about what's going on.

Ray at work

"Jim Thomlinson"
wrote in
message news:5DE756F1-953F-4197-B8FB-
...
Just a thought but is Research.xls open in a seperate
instance of
Excel. If
so then it is possible that although research is open
that the
macro can not
see it...



.

But why 2 instances? Why not 2 spread sheets open in the same instance? That
would be a lot easier to deal with...

"Coyote" wrote:

In my present setup - I am running two instances of excel
and transfering data between the two via a huge pasted
link array; but have the idea that a vba program that
transmits the data a piece at a time would be more
efficient than the big pasted link.

That is why I am trying to figure out how to accomplish
the task I have been asking about. I figured if I could
get that little part to work, then I could write the macro
to accomplish the larger task.

But so far, I am unable to get vba in one instance to see
the other instance.


-----Original Message-----
I've been sitting here just ~assuming~ the whole time
that Excel was
one of those application that didn't support multiple
instances of
itself running at the same time. But, well, sure enough
I just
opened a second instance of Excel with two separate
process IDs.
Blushing. :]

The two instances are aware of each other though.
Instance 2 opened
with Book4 as the default workbook name, as I already had
1, 2, 3 in
the first instance. I then created book5 in instance 1,
went back to
instance 2, created another workbook, and that created as
book6.

But I think you're probably right about what's going on.

Ray at work

"Jim Thomlinson"
wrote in
message news:5DE756F1-953F-4197-B8FB-
...
Just a thought but is Research.xls open in a seperate
instance of
Excel. If
so then it is possible that although research is open
that the
macro can not
see it...



.

Just an idea - is it possible to use an excel style
address in a macro? The following is the address used by
excel for the big pasted link array that I was describing.

=Excel.Sheet.8|'C:\Documents and Settings\trader1
\Desktop\research.xls'!'!Sheet1!R10C3:R3909C3'

I am thinking that maybe if vba cannot see the
research.xls through its onw addressing - perhaps it might
be able to use the excel style.??




-----Original Message-----
In my present setup - I am running two instances of excel
and transfering data between the two via a huge pasted
link array; but have the idea that a vba program that
transmits the data a piece at a time would be more
efficient than the big pasted link.

That is why I am trying to figure out how to accomplish
the task I have been asking about. I figured if I could
get that little part to work, then I could write the
macro
to accomplish the larger task.

But so far, I am unable to get vba in one instance to see
the other instance.


-----Original Message-----
I've been sitting here just ~assuming~ the whole time
that Excel was
one of those application that didn't support multiple
instances of
itself running at the same time. But, well, sure enough
I just
opened a second instance of Excel with two separate
process IDs.
Blushing. :]

The two instances are aware of each other though.
Instance 2 opened
with Book4 as the default workbook name, as I already
had
1, 2, 3 in
the first instance. I then created book5 in instance 1,
went back to
instance 2, created another workbook, and that created
as
book6.

But I think you're probably right about what's going on.

Ray at work

"Jim Thomlinson"
wrote in
message news:5DE756F1-953F-4197-B8FB-
...
Just a thought but is Research.xls open in a seperate
instance of
Excel. If
so then it is possible that although research is open
that the
macro can not
see it...



.

.

The reason I use two instances of excel is that I create
charts of the data that is generated on the first instance
and during the day, I have to adjust them as the day
unfolds. If they (the charts) are in the same instance
where the data is being collected, it makes that instance
crash.

However, transmitting the collected data to a second
instance and charting it there, solves the crashing
problem since I am then able to manipulate the charts
without disturbing the vba processes in the first instance.

Make sense??


-----Original Message-----
But why 2 instances? Why not 2 spread sheets open in the
same instance? That
would be a lot easier to deal with...

"Coyote" wrote:

In my present setup - I am running two instances of
excel
and transfering data between the two via a huge pasted
link array; but have the idea that a vba program that
transmits the data a piece at a time would be more
efficient than the big pasted link.

That is why I am trying to figure out how to accomplish
the task I have been asking about. I figured if I
could
get that little part to work, then I could write the
macro
to accomplish the larger task.

But so far, I am unable to get vba in one instance to
see
the other instance.


-----Original Message-----
I've been sitting here just ~assuming~ the whole time
that Excel was
one of those application that didn't support multiple
instances of
itself running at the same time. But, well, sure
enough
I just
opened a second instance of Excel with two separate
process IDs.
Blushing. :]

The two instances are aware of each other though.
Instance 2 opened
with Book4 as the default workbook name, as I already
had
1, 2, 3 in
the first instance. I then created book5 in instance
1,
went back to
instance 2, created another workbook, and that created
as
book6.

But I think you're probably right about what's going
on.

Ray at work

"Jim Thomlinson"
wrote in
message news:5DE756F1-953F-4197-B8FB-
...
Just a thought but is Research.xls open in a
seperate
instance of
Excel. If
so then it is possible that although research is
open
that the
macro can not
see it...



.


.

I have tried running a second workbook in the first
instance to do my charting, but for some reason,
manipulating the charts while the vba is running in the
other workbook makes the whole thing crash.

Running my charting in a second instance allows me to
manipulate/adjust the charts during the day without
interefering with the running macro.



-----Original Message-----
There is probably no reason to have two instances of
excel. Excel can have
multiple workbooks open in a single instance and you will
not run into this
problem.

--
Regards,
Tom Ogilvy

"Coyote" wrote in
message
...
In my present setup - I am running two instances of
excel
and transfering data between the two via a huge pasted
link array; but have the idea that a vba program that
transmits the data a piece at a time would be more
efficient than the big pasted link.

That is why I am trying to figure out how to accomplish
the task I have been asking about. I figured if I could
get that little part to work, then I could write the
macro
to accomplish the larger task.

But so far, I am unable to get vba in one instance to
see
the other instance.


-----Original Message-----
I've been sitting here just ~assuming~ the whole time
that Excel was
one of those application that didn't support multiple
instances of
itself running at the same time. But, well, sure
enough
I just
opened a second instance of Excel with two separate
process IDs.
Blushing. :]

The two instances are aware of each other though.
Instance 2 opened
with Book4 as the default workbook name, as I already
had
1, 2, 3 in
the first instance. I then created book5 in instance
1,
went back to
instance 2, created another workbook, and that created
as
book6.

But I think you're probably right about what's going
on.

Ray at work

"Jim Thomlinson"
wrote in
message news:5DE756F1-953F-4197-B8FB-
...
Just a thought but is Research.xls open in a seperate
instance of
Excel. If
so then it is possible that although research is open
that the
macro can not
see it...



.



.

Just an idea - is it possible to use an excel style
address in a macro?
Sure.

ActiveCell.FormulaR1C1 = _
"=Excel.Sheet.8|'C:\Documents and Settings\trader1" & _
"\Desktop\research.xls'!'!Sheet1!R10C3:R3909C3 '"

--
Regards,
Tom Ogilvy


"Coyote" wrote in message
...
Just an idea - is it possible to use an excel style
address in a macro? The following is the address used by
excel for the big pasted link array that I was describing.

=Excel.Sheet.8|'C:\Documents and Settings\trader1
\Desktop\research.xls'!'!Sheet1!R10C3:R3909C3'

I am thinking that maybe if vba cannot see the
research.xls through its onw addressing - perhaps it might
be able to use the excel style.??




-----Original Message-----
In my present setup - I am running two instances of excel
and transfering data between the two via a huge pasted
link array; but have the idea that a vba program that
transmits the data a piece at a time would be more
efficient than the big pasted link.

That is why I am trying to figure out how to accomplish
the task I have been asking about. I figured if I could
get that little part to work, then I could write the
macro
to accomplish the larger task.

But so far, I am unable to get vba in one instance to see
the other instance.


-----Original Message-----
I've been sitting here just ~assuming~ the whole time
that Excel was
one of those application that didn't support multiple
instances of
itself running at the same time. But, well, sure enough
I just
opened a second instance of Excel with two separate
process IDs.
Blushing. :]

The two instances are aware of each other though.
Instance 2 opened
with Book4 as the default workbook name, as I already
had
1, 2, 3 in
the first instance. I then created book5 in instance 1,
went back to
instance 2, created another workbook, and that created
as
book6.

But I think you're probably right about what's going on.

Ray at work

"Jim Thomlinson"
wrote in
message news:5DE756F1-953F-4197-B8FB-
...
Just a thought but is Research.xls open in a seperate
instance of
Excel. If
so then it is possible that although research is open
that the
macro can not
see it...



.

.

Hi, the code needs to be on all one line:

If you need/want a single line of code to be on two or more lines you must
connect them with a space..then underscore...then hit return.

-Like this:

Application.Workbooks("research.xls").Worksheets _
("sheet1").Range("a1").ClearContents

-Hope this helps.

Jim S

"Coyote" wrote:

vba editor does not like something about this????


Sub Macro1()



Application.Workbooks("research.xls").Worksheets
("sheet1").Range("a1").ClearContents

End Sub