vba help please

  • Thread starter Thread starter Coyote
  • Start date Start date
C

Coyote

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
 
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??
 
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)
 
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
 
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
 
What is the text in that dialog box before you click the debug
button?

Ray at work
 
As Gordon said:

Sub Macro1()

Application.Workbooks("research.xls") _
.Worksheets("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
 
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






.
Click to expand...
 
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
 
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...
 
you are right, all the macro sees is book1. it cannot see
research.xls - which is running in a separate instance of
excel
 
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
 
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 said:
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 said:
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...
Click to expand...
Click to expand...
 
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"
Click to expand...
 
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 said:
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"
Click to expand...
message news:5DE756F1-953F-4197-B8FB- (e-mail address removed)... instance of
Excel. If that the
macro can not


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

Coyote said:
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"
Click to expand...
message news:5DE756F1-953F-4197-B8FB- (e-mail address removed)... instance of
Excel. If that the
macro can not


.
Click to expand...
Click to expand...
 
Back
Top