vba help please

[Coyote]

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


Sub Macro1()



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

End Sub

 

[Ray Costanzo [MVP]]

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]

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??

 

[Bob Phillips]

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)

 

[Gord Dibben]

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

 

[Coyote]

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

 

[Ray Costanzo [MVP]]

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

Ray at work

 

[Coyote]

checked spelling and made sure both exist.

error says subscript out of range

error #9

 

[Coyote]

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




.

 

[Tom Ogilvy]

As Gordon said:

Sub Macro1()

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

End Sub

 

[Ray Costanzo [MVP]]

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]

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






.

 

[Ray Costanzo [MVP]]

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

 

[Guest]

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...

 

[Coyote]

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

 

[Ray Costanzo [MVP]]

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

 

[Guest]

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...

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

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...

 

[Coyote]

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"

 

[Tom Ogilvy]

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

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"

message news:5DE756F1-953F-4197-B8FB- (e-mail address removed)... instance of
Excel. If that the
macro can not


.

 

[Guest]

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

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"

message news:5DE756F1-953F-4197-B8FB- (e-mail address removed)... instance of
Excel. If that the
macro can not


.