Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default Copy and Paste Problem

Below is some code I'm trying to get to copy a worksheet from one workbook
and add it to the end of a worksheet in another workbook.



The problem I'm having is in the paste. Windows or Office, not sure which,
gets into a loop display a message saying it can do the paste because the
system, or workbook is busy. I can give you the complete message because you
can't see the full message when it is displayed.



When I end the program, 2 strange things happen, 1) the worksheet I copied
is now in a workbook called Book1.xls which I didn't open a new workbook in
the program and 2) what ever I copied, that's programming code or comments,
in the process of changing the program before I run a test, sometimes that
is pasted at the last row of the workbook I want to append the data to.



This is an Access program so I'm not sure which NewsGroup to post to Access
or Excel, so I did both.

Any ideas or comments will be greatly appreciated.



Thanks,



Ron



Dim xlApp As Excel.Application

Dim xlwbBook As Excel.Workbook

Dim xlwsSheet As Excel.Worksheet



Dim xlwbBookWrk As Excel.Workbook

Dim xlwsSheetWrk As Excel.Worksheet



Dim xlAddress As String

Dim LastRow As Long

Dim LastRowWrk As Long



Set xlApp = New Excel.Application

Set xlwbBook = xlApp.Workbooks.Open(cInPutFileName)

xlwbBook.SaveAs (cOutPutFileName)



'Find select the last row

'-------------------------------

Set xlwsSheet = xlwbBook.Worksheets("Order Report")

xlwsSheet.Activate

LastRow = xlwsSheet.UsedRange.Rows.Count + 1

xlAddress = LastRow & ":" & LastRow

xlwsSheet.Rows(xlAddress).Select



'Get the second workbook

'find the last row

'Select the data from row 1 thru the last row

'----------------------------------------------------------

Set xlwbBookWrk = xlApp.Workbooks.Open(cInPutFileName)

Set xlwsSheetWrk = xlwbBookWrk.Worksheets("Order Report")

LastRowWrk = xlwsSheetWrk.UsedRange.Rows.Count + 1

xlAddress = "1:" & LastRowWrk

xlwsSheetWrk.Rows(xlAddress).Select

xlwsSheetWrk.Copy



'Switch back to the first workbook/worksheet

'------------------------------------------------------

xlwsSheet.Activate

xlwsSheet.Paste

xlApp.CutCopyMode = False

xlwsSheet.Range("A1").Select

xlwbBookWrk.CLOSE



xlwbBook.Save

xlwbBook.CLOSE

xlApp.Quit


  #2   Report Post  
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default Copy and Paste Problem

when you copy a worksheet if you don't use BEFORE or AFTER excel
automatically creates a new workbook

from
xlwsSheetWrk.Copy

to
with xlwbBookWrk
xlwsSheetWrk.Copy after:=.sheets(.sheets.count)
end with

which will add the new sheet as the last sheet


"Ronald W. Roberts" wrote:

Below is some code I'm trying to get to copy a worksheet from one workbook
and add it to the end of a worksheet in another workbook.



The problem I'm having is in the paste. Windows or Office, not sure which,
gets into a loop display a message saying it can do the paste because the
system, or workbook is busy. I can give you the complete message because you
can't see the full message when it is displayed.



When I end the program, 2 strange things happen, 1) the worksheet I copied
is now in a workbook called Book1.xls which I didn't open a new workbook in
the program and 2) what ever I copied, that's programming code or comments,
in the process of changing the program before I run a test, sometimes that
is pasted at the last row of the workbook I want to append the data to.



This is an Access program so I'm not sure which NewsGroup to post to Access
or Excel, so I did both.

Any ideas or comments will be greatly appreciated.



Thanks,



Ron



Dim xlApp As Excel.Application

Dim xlwbBook As Excel.Workbook

Dim xlwsSheet As Excel.Worksheet



Dim xlwbBookWrk As Excel.Workbook

Dim xlwsSheetWrk As Excel.Worksheet



Dim xlAddress As String

Dim LastRow As Long

Dim LastRowWrk As Long



Set xlApp = New Excel.Application

Set xlwbBook = xlApp.Workbooks.Open(cInPutFileName)

xlwbBook.SaveAs (cOutPutFileName)



'Find select the last row

'-------------------------------

Set xlwsSheet = xlwbBook.Worksheets("Order Report")

xlwsSheet.Activate

LastRow = xlwsSheet.UsedRange.Rows.Count + 1

xlAddress = LastRow & ":" & LastRow

xlwsSheet.Rows(xlAddress).Select



'Get the second workbook

'find the last row

'Select the data from row 1 thru the last row

'----------------------------------------------------------

Set xlwbBookWrk = xlApp.Workbooks.Open(cInPutFileName)

Set xlwsSheetWrk = xlwbBookWrk.Worksheets("Order Report")

LastRowWrk = xlwsSheetWrk.UsedRange.Rows.Count + 1

xlAddress = "1:" & LastRowWrk

xlwsSheetWrk.Rows(xlAddress).Select

xlwsSheetWrk.Copy



'Switch back to the first workbook/worksheet

'------------------------------------------------------

xlwsSheet.Activate

xlwsSheet.Paste

xlApp.CutCopyMode = False

xlwsSheet.Range("A1").Select

xlwbBookWrk.CLOSE



xlwbBook.Save

xlwbBook.CLOSE

xlApp.Quit



  #3   Report Post  
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default Copy and Paste Problem

Here are some changes that I would use

Dim xlApp As Excel.Application

Dim xlwbBook As Excel.Workbook

Dim xlwsSheet As Excel.Worksheet



Dim xlwbBookWrk As Excel.Workbook

Dim xlwsSheetWrk As Excel.Worksheet



Dim xlAddress As String

Dim LastRow As Long

Dim LastRowWrk As Long



Set xlApp = New Excel.Application

Set xlwbBook = xlApp.Workbooks.Open(cInPutFileName)

xlwbBook.SaveAs (cOutPutFileName)



'Find select the last row

'-------------------------------

Set xlwsSheet = xlwbBook.Worksheets("Order Report")


'----------------Theses rows do nothing ------------------------------------
'xlwsSheet.Activate

'LastRow = xlwsSheet.UsedRange.Rows.Count + 1

'xlAddress = LastRow & ":" & LastRow

set PasteRange = xlwsSheet.Rows(xlAddress)

Set xlwbBookWrk = xlApp.Workbooks.Open(cInPutFileName)

Set xlwsSheetWrk = xlwbBookWrk.Worksheets("Order Report")

LastRowWrk = xlwsSheetWrk.UsedRange.Rows.Count + 1

xlAddress = "1:" & LastRowWrk

set CopyRange = xlwsSheetWrk.Rows(xlAddress)

CopyRange.Copy destination:=PasteRange


xlwbBookWrk.CLOSE

xlwbBook.Save

xlwbBook.CLOSE

xlApp.Quit


"Ronald W. Roberts" wrote:

Below is some code I'm trying to get to copy a worksheet from one workbook
and add it to the end of a worksheet in another workbook.



The problem I'm having is in the paste. Windows or Office, not sure which,
gets into a loop display a message saying it can do the paste because the
system, or workbook is busy. I can give you the complete message because you
can't see the full message when it is displayed.



When I end the program, 2 strange things happen, 1) the worksheet I copied
is now in a workbook called Book1.xls which I didn't open a new workbook in
the program and 2) what ever I copied, that's programming code or comments,
in the process of changing the program before I run a test, sometimes that
is pasted at the last row of the workbook I want to append the data to.



This is an Access program so I'm not sure which NewsGroup to post to Access
or Excel, so I did both.

Any ideas or comments will be greatly appreciated.



Thanks,



Ron



Dim xlApp As Excel.Application

Dim xlwbBook As Excel.Workbook

Dim xlwsSheet As Excel.Worksheet



Dim xlwbBookWrk As Excel.Workbook

Dim xlwsSheetWrk As Excel.Worksheet



Dim xlAddress As String

Dim LastRow As Long

Dim LastRowWrk As Long



Set xlApp = New Excel.Application

Set xlwbBook = xlApp.Workbooks.Open(cInPutFileName)

xlwbBook.SaveAs (cOutPutFileName)



'Find select the last row

'-------------------------------

Set xlwsSheet = xlwbBook.Worksheets("Order Report")

xlwsSheet.Activate

LastRow = xlwsSheet.UsedRange.Rows.Count + 1

xlAddress = LastRow & ":" & LastRow

xlwsSheet.Rows(xlAddress).Select



'Get the second workbook

'find the last row

'Select the data from row 1 thru the last row

'----------------------------------------------------------

Set xlwbBookWrk = xlApp.Workbooks.Open(cInPutFileName)

Set xlwsSheetWrk = xlwbBookWrk.Worksheets("Order Report")

LastRowWrk = xlwsSheetWrk.UsedRange.Rows.Count + 1

xlAddress = "1:" & LastRowWrk

xlwsSheetWrk.Rows(xlAddress).Select

xlwsSheetWrk.Copy



'Switch back to the first workbook/worksheet

'------------------------------------------------------

xlwsSheet.Activate

xlwsSheet.Paste

xlApp.CutCopyMode = False

xlwsSheet.Range("A1").Select

xlwbBookWrk.CLOSE



xlwbBook.Save

xlwbBook.CLOSE

xlApp.Quit



  #4   Report Post  
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default Copy and Paste Problem

Thanks, I'll give it a try.

Ron
"Joel" wrote in message
...
Here are some changes that I would use

Dim xlApp As Excel.Application

Dim xlwbBook As Excel.Workbook

Dim xlwsSheet As Excel.Worksheet



Dim xlwbBookWrk As Excel.Workbook

Dim xlwsSheetWrk As Excel.Worksheet



Dim xlAddress As String

Dim LastRow As Long

Dim LastRowWrk As Long



Set xlApp = New Excel.Application

Set xlwbBook = xlApp.Workbooks.Open(cInPutFileName)

xlwbBook.SaveAs (cOutPutFileName)



'Find select the last row

'-------------------------------

Set xlwsSheet = xlwbBook.Worksheets("Order Report")


'----------------Theses rows do
nothing ------------------------------------
'xlwsSheet.Activate

'LastRow = xlwsSheet.UsedRange.Rows.Count + 1

'xlAddress = LastRow & ":" & LastRow

set PasteRange = xlwsSheet.Rows(xlAddress)

Set xlwbBookWrk = xlApp.Workbooks.Open(cInPutFileName)

Set xlwsSheetWrk = xlwbBookWrk.Worksheets("Order Report")

LastRowWrk = xlwsSheetWrk.UsedRange.Rows.Count + 1

xlAddress = "1:" & LastRowWrk

set CopyRange = xlwsSheetWrk.Rows(xlAddress)

CopyRange.Copy destination:=PasteRange


xlwbBookWrk.CLOSE

xlwbBook.Save

xlwbBook.CLOSE

xlApp.Quit


"Ronald W. Roberts" wrote:

Below is some code I'm trying to get to copy a worksheet from one
workbook
and add it to the end of a worksheet in another workbook.



The problem I'm having is in the paste. Windows or Office, not sure
which,
gets into a loop display a message saying it can do the paste because the
system, or workbook is busy. I can give you the complete message because
you
can't see the full message when it is displayed.



When I end the program, 2 strange things happen, 1) the worksheet I
copied
is now in a workbook called Book1.xls which I didn't open a new workbook
in
the program and 2) what ever I copied, that's programming code or
comments,
in the process of changing the program before I run a test, sometimes
that
is pasted at the last row of the workbook I want to append the data to.



This is an Access program so I'm not sure which NewsGroup to post to
Access
or Excel, so I did both.

Any ideas or comments will be greatly appreciated.



Thanks,



Ron



Dim xlApp As Excel.Application

Dim xlwbBook As Excel.Workbook

Dim xlwsSheet As Excel.Worksheet



Dim xlwbBookWrk As Excel.Workbook

Dim xlwsSheetWrk As Excel.Worksheet



Dim xlAddress As String

Dim LastRow As Long

Dim LastRowWrk As Long



Set xlApp = New Excel.Application

Set xlwbBook = xlApp.Workbooks.Open(cInPutFileName)

xlwbBook.SaveAs (cOutPutFileName)



'Find select the last row

'-------------------------------

Set xlwsSheet = xlwbBook.Worksheets("Order Report")

xlwsSheet.Activate

LastRow = xlwsSheet.UsedRange.Rows.Count + 1

xlAddress = LastRow & ":" & LastRow

xlwsSheet.Rows(xlAddress).Select



'Get the second workbook

'find the last row

'Select the data from row 1 thru the last row

'----------------------------------------------------------

Set xlwbBookWrk = xlApp.Workbooks.Open(cInPutFileName)

Set xlwsSheetWrk = xlwbBookWrk.Worksheets("Order Report")

LastRowWrk = xlwsSheetWrk.UsedRange.Rows.Count + 1

xlAddress = "1:" & LastRowWrk

xlwsSheetWrk.Rows(xlAddress).Select

xlwsSheetWrk.Copy



'Switch back to the first workbook/worksheet

'------------------------------------------------------

xlwsSheet.Activate

xlwsSheet.Paste

xlApp.CutCopyMode = False

xlwsSheet.Range("A1").Select

xlwbBookWrk.CLOSE



xlwbBook.Save

xlwbBook.CLOSE

xlApp.Quit





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
copy and paste problem in vb Blubber Excel Discussion (Misc queries) 5 November 13th 08 06:54 PM
Copy and paste problem Germano Excel Discussion (Misc queries) 1 August 24th 07 12:45 PM
copy and paste problem Fred Kruger[_2_] Excel Programming 1 August 9th 07 12:52 PM
Copy and Paste Problem Skc Excel Discussion (Misc queries) 1 March 8th 06 08:21 PM
Excel cut/Paste Problem: Year changes after data is copy and paste Asif Excel Discussion (Misc queries) 2 December 9th 05 06:16 PM


All times are GMT +1. The time now is 04:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"