Copy Data from one workbook to another workbook

[Alexjj]

Good evening,

So, I'm trying to assist my fellow teachers with collecting and analyzing data. What I need to be able to do is the following:
1. Each grade level will have access to one workbook.
2. Each teacher will have their own page in that workbook. For example, the 4th grade team would have a workbook called 4th Grade where inside each teacher will have their own sheets: Bob, Carl, Jane, Julie
3. Each of those sheets has columns labeled Student Name, Book Title, and Level. As the teachers test their students, they will input the students' names, the book they read, and the level of that book.

What I need to be able to do is copy each teacher's data into a workbook called School Scores so that I can filter the data for analysis later. I know that I could copy the data from each sheet and paste it into another sheet within the same workbook, but I would rather have the School Scores because I intend to do this with all the grade levels so that we can see how we're doing as a school. Does this make sense?

Is there a code or function that would allow me to do this? Obviously, the School Scores workbook will be closed and it would be wonderful to have the data update after the teacher's close their individual workbooks...am I asking the impossible?

Thank you all in advance for at least considering this question.

Alex

 

[Glory]

Public Sub Active_X_Data_Objects()
 
Dim rsCon       As Object
Dim rsData      As Object
Dim szConnect   As String
Dim szSQL       As String
Dim sPath       As String
Dim sWB         As String
 
ThisWorkbook.Sheets("Sheet1").Cells.ClearContents
 
 
'==================================================
'Book1 in this section is the source workbook
'Change sPath and the workbook name to suit your needs
'==================================================
 
sPath = ThisWorkbook.Path
sWB = "\Book1.xls"
 
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & sPath & sWB & ";" & _
            "Extended Properties=""Excel 8.0;HDR=No"";"
 
 
'==================================================
'Sheet1 in this section is the destination Sheet
'Change the range and sheet name to suit your needs
'==================================================
 
szSQL = "SELECT * FROM [" & "Sheet1$A:C" & "];"
    Set rsCon = CreateObject("ADODB.Connection")
    Set rsData = CreateObject("ADODB.Recordset")
    rsCon.Open szConnect
    rsData.Open szSQL, rsCon, 0, 1, 1
 
 
'==================================================
'Paste information into the current workbook
'==================================================
 
ThisWorkbook.Sheets("Sheet1").Range("A1").CopyFromRecordset rsData
 
End Sub

Make them all read good...

 

[Alexjj]

Glory,

Thank you for the information. I'm excited to get this working. I'm still pretty new to EXCEL...would you mind telling me how I go about using this code? I understand if you don't want to take the time...I really do, but any guidance you can provide would be tremendously appreciated.

Sincerely,
Alex

 

[Glory]

1) Open the Excel workbook where you want to put the data. This is your destination workbook.
2) Press "Alt+F11" or "Tools-> Macro-> Visual Basic Editor"
3) Press "Alt+I->M" or "Insert-> Module"
4) Paste in the code

5) Make sure there is a book named "Book1.xls" that's got some kind of data in "Sheet1" Range "A[whatever]:C[whatever]". That source book should be located at the same path as your destination workbook (the one where you've pasted the code).

6) Make sure you've got a sheet named "Sheet1" in your destination workbook.

7) If you're using Excel 2007 or 2010, then the connection string will need to look like this:

szConnect = "Provider=Microsoft.[B][COLOR=green]Ace[/COLOR][/B].OLEDB.[B][COLOR=green]12[/COLOR][/B].0;" & _
            "Data Source=" & sPath & sWB & ";" & _
            "Extended Properties=""Excel [B][COLOR=green]12[/COLOR][/B].0;HDR=No"";"

Run the code from the VBE by pressing F5 or the "Play" button, or: From Excel, press "Alt+F8", select the macro from the list, and press "Run".

 

[Alexjj]

Thank you Glory!!! I appreciate the time and effort you put in to assist me with this project.