Error I cannot figure out

[Antonio]

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]

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]

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]

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]

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]

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