updating from external spreasheets: Bestway?

[warhorse927]

I need some ideas. I created a little grade sheet for myself and the other teachers liked it. Now, the admin. wants all the "gradesheets" consolidated for statical analysis. all internal would not be to hard but many will have to come in from email. All gradesheets (workbooks acutally) are identical in form but obviously have different data in them. Besides copy and paste, is there an way to append the data from each emailed spreadsheet (roughly 30) to a "master sheet" and is this even the best way? I need to stay out of access for this. HELP!

[Tom Schreiner]

Hi there...
Just to let you know that it is easily possible.
I am unsure as to the best way???
There are likely several good ways to go about this. If you want to go the VBA route, which may or may not be the best way, I can help you out.
You would need to list the specifics such as sheet names, cell ranges, how the data will be manipulated... We try to keep all replies on the board and avoid emailing solutions to keep the solution accessible to others with similiar goals. However, it might be easier, in this case, to email a sample workbook with a description of the outcome in the master workbook. I would be willing to take a look and then post a summary of my solution. Others then might want to improve upon or suggest better alternatives.
Click on the mail icon below if you wish.
Please zip the file(s) up. 28k connection here.
Tom

[Tom Schreiner]

Hi Mike.
This will solve the problem stated in your initial post to Mr Excel. I will post the macro in case it might help others as well. Create or choose an existing folder which will house all of your inboxed workbooks. Place this attached file in the same folder as this is the path which will be searched for the source data. The code associated with your commandbutton(UpDate) will step through and open each Excel file in this folder and take the Range A3:AH3 on worksheet "Data" and place the info on the next available line in your Master workbook in Columns B to AH.
There is no error handling. You must also remove the files already processed to avoid duplication and, therefore, incorrect results when you manipulate your collected data.
I am unsure as to your experience with VBA. If the code needs to be commented, let me know. If you need to make changes, post and someone will, doubtless, be able to help you out. You may want to monitor this thread for other suggestions as well. As far as your concern about the workbook becoming too saturated, it seems Excel will be fine for this project indefinitely.

Thanks to Mudface for the assistance with the Workbook.Open method.

A link to your Mr Excel post: http://www.mrexcel.com/board/viewtop...8809&forum=2&1

Hope this helps,
Tom

Private Sub cmdUpdate_Click()

Dim FS, i

Dim PlaceRow As Long

Dim OpenedName As String

Dim DoNotReopenActiveWB_Name As String

Dim MasterWorkBook As String



Application.DisplayAlerts = False

Application.EnableEvents = False

Application.ScreenUpdating = False



DoNotReopenActiveWB_Name = ActiveWorkbook.FullName

MasterWorkBook = ActiveWorkbook.Name

PlaceRow = Sheets("MasterSheet").Range("c1:c" & Range("c65536").End(xlUp).Row).Rows.Count

If PlaceRow < 3 Then PlaceRow = 3

Set FS = Application.FileSearch

With FS

    .LookIn = ActiveWorkbook.Path

    .Filename = "*.xls"

    If .Execute Then

        For i = 1 To .FoundFiles.Count

            If .FoundFiles(i) <> DoNotReopenActiveWB_Name Then

                PlaceRow = PlaceRow + 1

                Workbooks.Open .FoundFiles(i), 0

                OpenedName = ActiveWorkbook.Name

                Workbooks(MasterWorkBook).Sheets("MasterSheet") _

                    .Range("B" & PlaceRow & ":AH" & PlaceRow).Value = _

                Workbooks(OpenedName).Sheets("Data") _

                    .Range("A3:AG3").Value

                Workbooks(OpenedName).Close savechanges:=False

            End If

        Next i

    End If

End With



Application.DisplayAlerts = True

Application.EnableEvents = True

Application.ScreenUpdating = True

End Sub

[ This Message was edited by: TsTom on 2002-05-22 03:06 ]

[warhorse927]

Thanks Tom. I think this will do exactly what I want for now. One question: Is it possible to use range names instead of cell ranges? OK two questions, If every imported workbook had that row named the same?

[Tom Schreiner]

Sure.
Replace the current line"

Workbooks(OpenedName).Sheets("Data") _

	.Range("A3:AG3").Value

with

Workbooks(OpenedName).Sheets("Data") _

        .Range("SendRange").Value

...where "SendRange" would be the name of the range.

Tom

[aeroballer]

I tried this code and got a subscript out of range error at this line:
PlaceRow = Sheets("MasterSheet").Range("c1:c" & Range("c100").End(xlUp).row).Rows.Count

I just wanted to see if the code worked, but am unable to get it to work.

What I would like to do is have a master workbook that contains data from 50 source workbooks. I need this macro to add all of the workbooks in a folder and pull the data from A3:S6, and place it in the master workbook, one after the other.
Example: Master Workbook
1----
2----
3 Source 1 A3.....
4 Source 1 A4....
................
6 Source 1 A6....
7 Source 2 A3...
8 Source 2 A4.....