8 Replies - 15993 Views - Last Post: 18 March 2011 - 11:51 AM Rate Topic: -----

#1 3P1C_F@!l  Icon User is offline

  • D.I.C Head

Reputation: 8
  • View blog
  • Posts: 98
  • Joined: 01-March 11

Copy and paste from one excel sheet to another

Posted 18 March 2011 - 06:37 AM

So I have a problem.. a BIG problem. I have 300+ excel files that need to be combined. There are 4 different types and in 4 different directories on my network. I need to copy the data out of each one and combine the data into a new single workbook (1 for each type). I have come up with a solution that does MOST of what it is supposed to do. Only problem is I can't figure out how to have the paste operation go to the same single workbook. No matter what I have tried it opens a new copy of excel to paste into everytime. Here is what I have so far...


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        
        Dim eAPP As Excel.Application
        Dim eBOOK As Excel.Workbook
        Dim eSHEET As Excel.Worksheet
        Dim files As String() = Directory.GetFiles(TextBox1.Text)
        Dim filename As String



        For Each filename In files

            ' Opens each excel file in the directory specified in textbox1 and copys the information from the active sheet
            eAPP = New Excel.Application
            eAPP.Visible = False
            eBOOK = eAPP.Workbooks.Open(filename)
            eBOOK = eAPP.ActiveWorkbook
            eSHEET = CType(eBOOK.ActiveSheet, Excel.Worksheet)
            eSHEET.Cells.Range("A2", "v500").Copy() "Would like to copy only cells with data in them but I don't know how"

            ' makes sure that only one copy of the receiving workbook is open "HAHA I WISH! Need help here!"
            If eAPP.Workbooks.Count <= 1 Then
                eAPP = New Excel.Application
                eAPP.Visible = True
                eAPP.Workbooks.Open("This is where my new excel instance would go for pasting")
            End If

            ' Sets active sheet and pastes information
            eBOOK = eAPP.ActiveWorkbook
            eSHEET = CType(eBOOK.ActiveSheet, Excel.Worksheet)
            eSHEET.Paste()



        Next

    End Sub




Is This A Good Question/Topic? 0
  • +

Replies To: Copy and paste from one excel sheet to another

#2 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,200
  • Joined: 25-September 09

Re: Copy and paste from one excel sheet to another

Posted 18 March 2011 - 07:29 AM

This is just a guess without actually testing anything but I would think you would want something like

Create a WorkBook Object that is the main workbook. (we'll cool it eMainBook)
Do this outside the iteration of the files

Next...
Iterate through your files like you're doing and use eBook like you're doing
Open the found file
Copy the data

Create a new SHEET in eMainBook
Paste To New SHEET
Close eBook
Loop back for next iteration until done
Finish up by saving and closing eMainBook

If I get time later, I'll play around with it as I could use a similar application later for something I'm going to be doing.

This post has been edited by CharlieMay: 18 March 2011 - 07:30 AM

Was This Post Helpful? 0
  • +
  • -

#3 3P1C_F@!l  Icon User is offline

  • D.I.C Head

Reputation: 8
  • View blog
  • Posts: 98
  • Joined: 01-March 11

Re: Copy and paste from one excel sheet to another

Posted 18 March 2011 - 08:57 AM

I like where you are going with this idea but I don't want the pasted data on a new sheet. I need it all on the same sheet unless you know of a way to combine sheets in excel. The other problem I face with this is once I figure out how to paste to the same sheet I also have to tell it to paste to the first empty cell in column A but I don't think that should be too hard.

This post has been edited by 3P1C_F@!l: 18 March 2011 - 08:58 AM

Was This Post Helpful? 0
  • +
  • -

#4 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,200
  • Joined: 25-September 09

Re: Copy and paste from one excel sheet to another

Posted 18 March 2011 - 09:31 AM

OK, so you would still create the main workbook but just create it with 1 sheet and then use a counter so you can multiply it by the rows of your range. A2:V500 would be 499 rows (as A2 is inclusive) You may have to play around with this though.

Then when you paste, you paste to Sheet1 of the MAIN workbook.
Was This Post Helpful? 0
  • +
  • -

#5 3P1C_F@!l  Icon User is offline

  • D.I.C Head

Reputation: 8
  • View blog
  • Posts: 98
  • Joined: 01-March 11

Re: Copy and paste from one excel sheet to another

Posted 18 March 2011 - 09:46 AM

I am almost done. As you were writing your last post I was closing 120+ instances of Excel because I'm an idiot and forgot to close each instance after the loop LOL! My VMWare client didn't like it much. I will post the working code when I am finished. Thank you for your help CharlieMay :w00t:
Was This Post Helpful? 0
  • +
  • -

#6 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,200
  • Joined: 25-September 09

Re: Copy and paste from one excel sheet to another

Posted 18 March 2011 - 09:59 AM

OK, well, here's a version that doesn't create a new sheet.
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
  
    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        'Create the excel objects that will hold the copy and pasted information
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        xlApp = New Excel.Application
        'Create the main workbook with 1 sheet (must have at least 1 sheet it will be named "Sheet1")
        xlApp.SheetsInNewWorkbook = 1
        xlWorkBook = xlApp.Workbooks.Add
        'Your objects remain the same
        Dim eAPP As Excel.Application
        Dim eBOOK As Excel.Workbook
        Dim eSHEET As Excel.Worksheet
        Dim files As String() = Directory.GetFiles(TextBox1.Text)
        Dim filename As String

        Dim x As Integer = 0 'A counter to keep a unique sheet name

        For Each filename In files

            eAPP = New Excel.Application
            eAPP.Visible = False
            eBOOK = eAPP.Workbooks.Open(filename)
            eBOOK = eAPP.ActiveWorkbook
            eSHEET = CType(eBOOK.ActiveSheet, Excel.Worksheet)
            eSHEET.Cells.Range("A1", "C2").Copy()
            'Clean up the open objects that were created.
            eBOOK.Close()
            eAPP.Quit()
            releaseObject(eAPP)
            releaseObject(eBOOK)
            releaseObject(eSHEET)


            'Set the sheet to the only sheet in the  main workbook
            xlWorkSheet = CType(xlWorkBook.ActiveSheet, Excel.Worksheet)
            'Here is where you're setting the range for My range is 2 rows (A1, C2) I want to start at row 1 for the first file so (0*2)+1
            'Next interation will be (1*2)+1 so 3rd row to start the next file and so on...
            xlWorkSheet.Cells.Range("A" & (x * 2) + 1).Select()
            'Paste what you copied from the source workbook above
            xlWorkSheet.Paste()
            x += 1 'increase x for the next file
            releaseObject(xlWorkSheet)

        Next
        'Save your main workbook
        xlWorkBook.SaveAs("E:\MainSheet.xlsx")
        'Clean up the open objects that were created
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)

        MsgBox("I think we're done")
    End Sub

Was This Post Helpful? 0
  • +
  • -

#7 3P1C_F@!l  Icon User is offline

  • D.I.C Head

Reputation: 8
  • View blog
  • Posts: 98
  • Joined: 01-March 11

Re: Copy and paste from one excel sheet to another

Posted 18 March 2011 - 11:25 AM

Here is my complete and working code :bananaman: Thanks CharlieMay for the suggestions and the last bit about releasing the COM object! I do however have one final question. As you can see I added a text box to display the # of sheets it has copied but it always stops at 2.. why?? Any idea on how to fix??


Imports System
Imports System.IO
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        BackgroundWorker1.RunWorkerAsync()
        Label2.Visible = True
        TextBox2.Visible = True
    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Label2.Visible = False
        TextBox2.Visible = False



    End Sub

    Private Sub BackgroundWorker1_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        Dim eAPP As Excel.Application
        Dim eMainBook As Excel.Application
        Dim eBOOK As Excel.Workbook
        Dim eSHEET As Excel.Worksheet
        Dim files As String() = Directory.GetFiles(TextBox1.Text)
        Dim filename As String


        eMainBook = New Excel.Application
        eMainBook.Workbooks.Open("file directory here")
        eMainBook.Visible = True
        eMainBook.DisplayAlerts = False

        For Each filename In files

            BackgroundWorker1.ReportProgress(1)


            ' Opens each excel file in the directory specified in textbox1 and copys the information from the active sheet
            eAPP = New Excel.Application
            eAPP.Visible = False
            eAPP.DisplayAlerts = False
            eBOOK = eAPP.Workbooks.Open(filename)
            eBOOK = eAPP.ActiveWorkbook
            eSHEET = CType(eBOOK.ActiveSheet, Excel.Worksheet)
            eSHEET.Cells.Range("A2", "v500").Copy()
            eBOOK = eAPP.ActiveWorkbook
            eSHEET = CType(eMainBook.ActiveSheet, Excel.Worksheet)
            eSHEET.Paste()
            eSHEET.Range("A65536").End(Excel.XlDirection.xlUp).Offset(1, 0).Select()

            eBOOK.Close()
            eAPP.Application.Quit()
            releaseobject(eAPP)
            releaseobject(eBOOK)



        Next

    End Sub

    Private Sub BackgroundWorker1_ProgressChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ProgressChangedEventArgs) Handles BackgroundWorker1.ProgressChanged
        Dim R As Integer

        R = 1

        If e.ProgressPercentage.Equals(1) Then
            R = R + 1
            TextBox2.Text = R.ToString
        End If

    End Sub

    Private Sub releaseobject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try

    End Sub

End Class



This post has been edited by 3P1C_F@!l: 18 March 2011 - 11:26 AM

Was This Post Helpful? 0
  • +
  • -

#8 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,200
  • Joined: 25-September 09

Re: Copy and paste from one excel sheet to another

Posted 18 March 2011 - 11:28 AM

You're setting R=1 in the background worker progressedchanged event and then adding 1 to it Each time. So it's seeing 1 + 1 every time the background worker is called and then displaying it.

This post has been edited by CharlieMay: 18 March 2011 - 11:29 AM

Was This Post Helpful? 0
  • +
  • -

#9 3P1C_F@!l  Icon User is offline

  • D.I.C Head

Reputation: 8
  • View blog
  • Posts: 98
  • Joined: 01-March 11

Re: Copy and paste from one excel sheet to another

Posted 18 March 2011 - 11:51 AM

I figured it out. I added in my BackgroundWorker1_DoWork..


Dim R As Integer

R = 1

' Then to the end my For Each statement

R = R + 1
Backgroundworker1.ReportProgress(r)




And changed my BackgroundWorker1_ProgressChanged to...


textbox2.text = e.ProgressPercentage.ToString



Was This Post Helpful? 0
  • +
  • -

Page 1 of 1