[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

COPY AND PASTE

I AM TRYING TO OPEN A EXCEL FILE , COPY ITS CONTENTS INTO THE LAST ROW OF ANOTHER EXCEL FILE. THESE 2 EXCEL FILES HAVE THE SAME COLUMN HEADINGS AND NUMBER OF COLUMNS ETC. THEY ARE IDENTICAL. I AM GETTING AN ERROR IN THE PLACE WHERE I AM TRYING TO PASTE THE COPIED DATA. CAN YOU PL. HELP?

Private Sub mergeExcelFiles(ByVal excel_sFileName1 As String, ByVal excel_sFileName2 As String)
   'remove_file (sFileName)
     'Create a new instance of Excel
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   'Dim Sheet As Object
   Set oExcel = CreateObject("Excel.Application")
   Dim numberOfRows As Integer
   Dim rowNumber As Integer
   Dim x As Integer
   Dim Varchar As String
   Dim Varchar1 As String
   Dim aseries As String
   Dim How_Many_Rows As Integer
   Dim lastRow As Integer
   
   
  oExcel.Application.DisplayAlerts = False
   If Dir$(excel_sFileName2) = "" Then

   
   
'MsgBox "No records to export"
    Exit Sub

   Else


  'Open the text file
   'Set oBook = oExcel.Workbooks.Open(excel_sFileName2)
   
   'oExcel.Application.DisplayAlerts = True

‘OPEN THE SECOND EXCEL FILE
   Workbooks.Open FileName:=excel_sFileName2
   oExcel.Visible = True
   
' DELETE FIRST ROW WHICH IS THE COLUMN HEADING
   Rows("1:1").Select  ' select the first row
    Selection.Delete Shift:=xlUp ' delete first row which is the column heading

   
    Cells.Select      ' select contents of the sheet
    Selection.Copy ' copy it
   
ActiveWorkbook.Save  ' save current worksheet
   'ActiveWorkbook.Close  ' close current worksheet

‘OPEN THE FIRST EXCEL FILE
    Workbooks.Open FileName:=excel_sFileName1

‘ FIND THE LAST ROW
      Range("A1").Select
    'Selection.End(xlUp).Select
     How_Many_Rows = ActiveSheet.Range("A1").CurrentRegion.Rows.Count
'    How_Many_Rows = ActiveCell.Row
              lastRow = How_Many_Rows + 1

             
    'Rows("A" & lastRow).Select

    'ActiveSheet.Rows(ActiveSheet.Range("A1").CurrentRegion.Rows.Count).Select ' select the last row after values

‘I GET AN ERROR AT THIS POINT – UNABLE TO PASTE DATA INTO EXCEL FILE!!!!
    ActiveSheet.Rows("A" & lastRow).Paste

    'ActiveSheet.Paste ' paste data copied from excel_sFileName2

‘ADD A FILTER AND THEN SAVE EXCEL FILE.
      oExcel.Columns("AE:AE").Select
    oExcel.Selection.AutoFilter
    ActiveWorkbook.Save ' save excel file
    ActiveWindow.Close  ' close excel file






    oExcel.Application.DisplayAlerts = True


   oExcel.Quit
   Set oExcel = Nothing

    'remove_file (sFileName) 'this removes the csv file so that I only  see  the excel file
   End If

    'remove_file (sFileName) 'this removes the csv file so that I only  see  the excel file

End Sub






0
Sara_j_11
Asked:
Sara_j_11
  • 7
  • 4
  • 4
  • +2
2 Solutions
 
aikimarkCommented:
a couple of questions:
1. how many rows are in these  two workbooks?  (there is a 64K row limit)
2. are the rows in your Windows clipboard at the point of failure?  (i.e. was the copy successful?)
3. what is the value of the lastRow variable?
4. what is the error message you are receiving?

========================
I'm leaning toward a problem with a difference between what you are copying and what you are pasting...

Cells.Select    'selects cells

Rows("A" & lastRow).Paste   'attempts to paste rows

Although rows contain cells, they are not necessarily the same thing.
0
 
Sara_j_11Author Commented:
THERE ARE ABOUT 100 ROWS IN EACH EXCEL FILE (1 AND 2).
i AM NOT SURE IF THE COPY WAS SUCCESSFUL,

CAN ANYONE HELP ME WITH AN ALTERNATIVE SOLUTION?
0
 
tonym001Commented:
Are you importing this data from a CSV file?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Sara_j_11Author Commented:
NO THESE ARE SIMPLY 2 EXCEL FILES THAT i AM TRYING TO MAKE AS ONE EXCEL FILE , SINCE THEY HAVE THE SAME TYPE OF DATA(SAME COLUMN NAMES)
0
 
JohnBPriceCommented:
Instead of pasting, you can set the values directly, so instead of

‘I GET AN ERROR AT THIS POINT – UNABLE TO PASTE DATA INTO EXCEL FILE!!!!
    ActiveSheet.Rows("A" & lastRow).Paste

try using a do loop to process each row in the source table and then to copy the data do

for j = 1 to last row in sourceSheet
    ActiveSheet.Cells(LastRow + j, 1) = SourceSheet.Cells(j,1)
    ActiveSheet.Cells(LastRow + j, 2) = SourceSheet.Cells(j,2)

etc.
0
 
edwardiiiCommented:
Hi, Sara_j_11.  If possible, turn off your Caps Lock so save space:)

For starters maybe the following adjustment will help.  To move to the last row of your Excel1 document (so you can paste the contents of Excel2 that you copied to the clipboard), then do the paste:

     Range("A1").Select
     How_Many_Rows = ActiveSheet.Range("A1").CurrentRegion.Rows.Count
     lastRow = How_Many_Rows + 1
     ActiveSheet.Range("A1").CurrentRegion.Rows(lastRow).Select
     ActiveSheet.Paste
0
 
tonym001Commented:
Ok have you tried using an ado connection and insert into statement
I have the code but not on this PC so I will wing it a bit and hopefully if its not correct you will get the idea

Dim AdoConn As New ADODB.Connection
Dim ConnStr As String
Dim SourceSheetName as string
Dim DestinationSheetName as string
Dim strColHeaders as string
Dim Count1 as integer
Dim strSql as string
Dim intColHeadCount as integer
Dim dwRecsTransferred as long

ConnStr = "ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & excel_sFileName2 & _
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
'"HDR=Yes;" indicates that the first row contains columnnames, not data
'"IMEX=1;" tells the driver to always read "intermixed" data columns as text
AdoConn.Open

'when transferring from excel the source sheet needs a $ after it e.g sheet1$
'am not sure if the destination sheet needs it but it cant hurt
' now to execute
strSql = strSQL = "INSERT INTO [" & DestinationSheetName & "] IN '" & excel_sFileName2 & " 'Excel 8.0;' SELECT * FROM [" & SourceSheetName & "]"

'dwRecsTransferred = the number of records transferred
AdoConn.Execute StrSql, dwRecsTransferred

Hope this helps - if it doesn't work properly its probably a syntax error or a stupid mistake I have made in the strSql statement and I will sort it out on Monday

0
 
tonym001Commented:
oops
AdoConn.Open
should read
AdoConn.open ConnStr
0
 
tonym001Commented:
oops again
ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & excel_sFileName2 & _
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

Did say I was winging it
0
 
tonym001Commented:
sara forgot to mention you will need a reference to the Microsoft ActiveX Data Objects 2.(whatever) library within your project.

Good luck :-)
0
 
Sara_j_11Author Commented:
I tried the solution given by edwardiii:

     Range("A1").Select
     How_Many_Rows = ActiveSheet.Range("A1").CurrentRegion.Rows.Count
     lastRow = How_Many_Rows + 1
     ActiveSheet.Range("A1").CurrentRegion.Rows(lastRow).Select
     ActiveSheet.Paste

It looks like this is helping but it is given m a mesaage that:
the information cannot be pasted because the copy are and the paste are differnet in size and shape.
click a single cell and then paste..

How do I just select a single cell in the beginning of that last row, Vs a range as given above????
0
 
edwardiiiCommented:
What happens if you set DisplayAlerts to False?:

     Range("A1").Select
     How_Many_Rows = ActiveSheet.Range("A1").CurrentRegion.Rows.Count
     lastRow = How_Many_Rows + 1
     ActiveSheet.Range("A1").CurrentRegion.Rows(lastRow).Select
     Excel.Application.DisplayAlerts = False
     ActiveSheet.Paste
     Excel.Application.DisplayAlerts = True
0
 
edwardiiiCommented:
Or, if that's not an option, use "Cells(lastRow, "a").Select.  I don't get the "size/shape" error with this, even with DisplayAlerts = True.

     Range("A1").Select
     How_Many_Rows = ActiveSheet.Range("A1").CurrentRegion.Rows.Count
     lastRow = How_Many_Rows + 1
     ActiveSheet.Range("A1").CurrentRegion.Rows(lastRow).Select
     Cells(lastRow, "a").Select
     'Excel.Application.DisplayAlerts = False
     ActiveSheet.Paste
     'Excel.Application.DisplayAlerts = Tru
0
 
aikimarkCommented:
activesheet.usedrange.select   'to select just the cells in the used range

selection.copy     'copy the selected cells to the clipboard

ActiveWorkbook.Save  ' save current worksheet
   'ActiveWorkbook.Close  ' close current worksheet

‘OPEN THE FIRST EXCEL FILE
    Workbooks.Open FileName:=excel_sFileName1

activesheet.cells(activesheet.usedrange.rows.count+1,1).select  'next row after current data in the worksheet in column A

activesheet.paste  'plop in the data from the clipboard
0
 
tonym001Commented:
sara just try ado method is quicker, cleaner and more reliable just takes a bit of setting up
0
 
aikimarkCommented:
tonym001,

Using ADO, how do you insert the rows at the first blank row in the target worksheet?  Positioning of the insert is a requirement that doesn't seem to be met by the ADO solution.
0
 
tonym001Commented:
aikimark it itself positions automatically - try it  
0
 
aikimarkCommented:
good.  thanks.
0
 
edwardiiiCommented:
Hi, GPrentice00. Sara went with my solution and had one follow up question (per her 5/6/05 4:19 pm comment) which I answered within a couple of hours. I tested aikimark's solution, which I believe is more streamline than my own. I haven't tested the other solutions. I have no problem with a points split, but I do believe points should be awarded because working solutions were presented.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now