Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.access.formscoding,microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy and paste problem in vb | Excel Discussion (Misc queries) | |||
Copy and paste problem | Excel Discussion (Misc queries) | |||
copy and paste problem | Excel Programming | |||
Copy and Paste Problem | Excel Discussion (Misc queries) | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) |