ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Error I cannot figure out (https://www.excelbanter.com/excel-programming/363365-error-i-cannot-figure-out.html)

Antonio
Error I cannot figure out
 
The following code runs well if inventory xls is not open

(...)
Application.DisplayAlerts=True
Columns("A:C").Copy
Workbooks.Open ("c:\documents and settings\antonio\my
documents\inventory.xls")
Worksheets("rent").Activate

Columns("D:F").Select
ActiveSheet.Paste

However, if inventory.xls is already open, when I run the above I get error
at the last line: "Paste failed"


The strange thing is that when running line 3 it does not show the dialog
that inventory.xls is already open.

It seems that it has something to do with reopening inventory.xls but I
cannot figure out where the problem is.

If I have inventory.xls open and I do not reopen it with VBA it works fine.

Thanks,

Antonio

Dave Peterson
Error I cannot figure out
 
Sometimes just rearranging the order of the copy and open will work wonders:

dim rngtocopy as range
set rngtocopy = columns("a:c")

Workbooks.Open ("c:\documents and settings\antonio\my documents\inventory.xls")
Worksheets("rent").Activate
Columns("D:F").Select

rngtocopy.copy
ActiveSheet.Paste

=======
Or maybe...

dim rngtocopy as range
set rngtocopy = columns("a:c")

Workbooks.Open ("c:\documents and settings\antonio\my documents\inventory.xls")
rngtocopy.copy _
destination:=worksheets("rent").range("D1")



Antonio wrote:

The following code runs well if inventory xls is not open

(...)
Application.DisplayAlerts=True
Columns("A:C").Copy
Workbooks.Open ("c:\documents and settings\antonio\my
documents\inventory.xls")
Worksheets("rent").Activate

Columns("D:F").Select
ActiveSheet.Paste

However, if inventory.xls is already open, when I run the above I get error
at the last line: "Paste failed"

The strange thing is that when running line 3 it does not show the dialog
that inventory.xls is already open.

It seems that it has something to do with reopening inventory.xls but I
cannot figure out where the problem is.

If I have inventory.xls open and I do not reopen it with VBA it works fine.

Thanks,

Antonio

--

Dave Peterson

Antonio
Error I cannot figure out
 
Hi Dave,

I have tried the second alternative and it works. Many thanks.

However, I am unhappy about this. It seems to be a bug and I don't like the
feeling of not working with a robust application.

Thanks,

Antonio

"Dave Peterson" wrote:

Sometimes just rearranging the order of the copy and open will work wonders:

dim rngtocopy as range
set rngtocopy = columns("a:c")

Workbooks.Open ("c:\documents and settings\antonio\my documents\inventory.xls")
Worksheets("rent").Activate
Columns("D:F").Select

rngtocopy.copy
ActiveSheet.Paste

=======
Or maybe...

dim rngtocopy as range
set rngtocopy = columns("a:c")

Workbooks.Open ("c:\documents and settings\antonio\my documents\inventory.xls")
rngtocopy.copy _
destination:=worksheets("rent").range("D1")



Antonio wrote:

The following code runs well if inventory xls is not open

(...)
Application.DisplayAlerts=True
Columns("A:C").Copy
Workbooks.Open ("c:\documents and settings\antonio\my
documents\inventory.xls")
Worksheets("rent").Activate

Columns("D:F").Select
ActiveSheet.Paste

However, if inventory.xls is already open, when I run the above I get error
at the last line: "Paste failed"

The strange thing is that when running line 3 it does not show the dialog
that inventory.xls is already open.

It seems that it has something to do with reopening inventory.xls but I
cannot figure out where the problem is.

If I have inventory.xls open and I do not reopen it with VBA it works fine.

Thanks,

Antonio

--

Dave Peterson

Dave Peterson
Error I cannot figure out
 
There are lots of things that lose the clipboard when you're working in excel
manually.

I'm not sure I would classify it as a bug, but it is something that you (as a
programmer) have to be aware of.

Antonio wrote:

Hi Dave,

I have tried the second alternative and it works. Many thanks.

However, I am unhappy about this. It seems to be a bug and I don't like the
feeling of not working with a robust application.

Thanks,

Antonio

"Dave Peterson" wrote:

Sometimes just rearranging the order of the copy and open will work wonders:

dim rngtocopy as range
set rngtocopy = columns("a:c")

Workbooks.Open ("c:\documents and settings\antonio\my documents\inventory.xls")
Worksheets("rent").Activate
Columns("D:F").Select

rngtocopy.copy
ActiveSheet.Paste

=======
Or maybe...

dim rngtocopy as range
set rngtocopy = columns("a:c")

Workbooks.Open ("c:\documents and settings\antonio\my documents\inventory.xls")
rngtocopy.copy _
destination:=worksheets("rent").range("D1")



Antonio wrote:

The following code runs well if inventory xls is not open

(...)
Application.DisplayAlerts=True
Columns("A:C").Copy
Workbooks.Open ("c:\documents and settings\antonio\my
documents\inventory.xls")
Worksheets("rent").Activate

Columns("D:F").Select
ActiveSheet.Paste

However, if inventory.xls is already open, when I run the above I get error
at the last line: "Paste failed"

The strange thing is that when running line 3 it does not show the dialog
that inventory.xls is already open.

It seems that it has something to do with reopening inventory.xls but I
cannot figure out where the problem is.

If I have inventory.xls open and I do not reopen it with VBA it works fine.

Thanks,

Antonio

--

Dave Peterson


--

Dave Peterson

Antonio
Error I cannot figure out
 
Yes Dave, you are right, that is a better way to look at it.

In conclusion, it seems that copying to a range (or other object) is better
practice than using the clipboard. It may be even faster.

Regards,

Antonio

"Dave Peterson" wrote:

There are lots of things that lose the clipboard when you're working in excel
manually.

I'm not sure I would classify it as a bug, but it is something that you (as a
programmer) have to be aware of.

Antonio wrote:

Hi Dave,

I have tried the second alternative and it works. Many thanks.

However, I am unhappy about this. It seems to be a bug and I don't like the
feeling of not working with a robust application.

Thanks,

Antonio

"Dave Peterson" wrote:

Sometimes just rearranging the order of the copy and open will work wonders:

dim rngtocopy as range
set rngtocopy = columns("a:c")

Workbooks.Open ("c:\documents and settings\antonio\my documents\inventory.xls")
Worksheets("rent").Activate
Columns("D:F").Select

rngtocopy.copy
ActiveSheet.Paste

=======
Or maybe...

dim rngtocopy as range
set rngtocopy = columns("a:c")

Workbooks.Open ("c:\documents and settings\antonio\my documents\inventory.xls")
rngtocopy.copy _
destination:=worksheets("rent").range("D1")



Antonio wrote:

The following code runs well if inventory xls is not open

(...)
Application.DisplayAlerts=True
Columns("A:C").Copy
Workbooks.Open ("c:\documents and settings\antonio\my
documents\inventory.xls")
Worksheets("rent").Activate

Columns("D:F").Select
ActiveSheet.Paste

However, if inventory.xls is already open, when I run the above I get error
at the last line: "Paste failed"

The strange thing is that when running line 3 it does not show the dialog
that inventory.xls is already open.

It seems that it has something to do with reopening inventory.xls but I
cannot figure out where the problem is.

If I have inventory.xls open and I do not reopen it with VBA it works fine.

Thanks,

Antonio

--

Dave Peterson


--

Dave Peterson

Dave Peterson
Error I cannot figure out
 
And I've found using variables to represents the objects that I want is nice,
too.

I can set the variable and use it right before I need to. Then I don't have to
worry about any intermediate code destroying what I thought I did.

Antonio wrote:

Yes Dave, you are right, that is a better way to look at it.

In conclusion, it seems that copying to a range (or other object) is better
practice than using the clipboard. It may be even faster.

Regards,

Antonio

"Dave Peterson" wrote:

There are lots of things that lose the clipboard when you're working in excel
manually.

I'm not sure I would classify it as a bug, but it is something that you (as a
programmer) have to be aware of.

Antonio wrote:

Hi Dave,

I have tried the second alternative and it works. Many thanks.

However, I am unhappy about this. It seems to be a bug and I don't like the
feeling of not working with a robust application.

Thanks,

Antonio

"Dave Peterson" wrote:

Sometimes just rearranging the order of the copy and open will work wonders:

dim rngtocopy as range
set rngtocopy = columns("a:c")

Workbooks.Open ("c:\documents and settings\antonio\my documents\inventory.xls")
Worksheets("rent").Activate
Columns("D:F").Select

rngtocopy.copy
ActiveSheet.Paste

=======
Or maybe...

dim rngtocopy as range
set rngtocopy = columns("a:c")

Workbooks.Open ("c:\documents and settings\antonio\my documents\inventory.xls")
rngtocopy.copy _
destination:=worksheets("rent").range("D1")



Antonio wrote:

The following code runs well if inventory xls is not open

(...)
Application.DisplayAlerts=True
Columns("A:C").Copy
Workbooks.Open ("c:\documents and settings\antonio\my
documents\inventory.xls")
Worksheets("rent").Activate

Columns("D:F").Select
ActiveSheet.Paste

However, if inventory.xls is already open, when I run the above I get error
at the last line: "Paste failed"

The strange thing is that when running line 3 it does not show the dialog
that inventory.xls is already open.

It seems that it has something to do with reopening inventory.xls but I
cannot figure out where the problem is.

If I have inventory.xls open and I do not reopen it with VBA it works fine.

Thanks,

Antonio

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


All times are GMT +1. The time now is 09:18 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com