Gather data from several workbooks.

[Art MacNeil]

Hello all,

I know this is probably an easy fix, but I'm at a loss as to how to do it.
I've copied the two Macro's in question at the bottom of this email.

I have a number of (Source) spreadsheets/workbooks and they all have a tab
called "today".

I have a separate (Destination) spreadsheet/workbook with a macro (primarily
written by Nikos Yannacopoulos from this newsgroup) called
Process_All_Workbooks. That spreadsheet/workbook is called "Update".


The Process_All_Workbooks Macro will call another macro and run it on all
spreadsheets/workbooks in a folder that I can define. It will also let me
save the changes or choose not to.

I've used the Process_All_Workbooks Macro a few times with a different macro
and it works great.

In this case, I want it to open all the spreadsheets/workbooks in a folder I
choose and go to the "today" tab and copy any data in Cells A1 and A2 and
then paste them to a tab on my "Update" spreadsheet called "Get Data".

The Get Data Tab has a header line:

Cell A1 is: Spreadsheet.
Cell B1 is: Message.

So I want to use the Process_All_Workbooks Macro to call a new macro that
will do the following:


copy Cell A1 from the "today" tab on spreadsheet #1, to the "Get Data" tab
of the "Update" Spreadsheet.
This info should be saved in Cell A2


copy Cell A2 from the "today" tab on spreadsheet #1, to the "Get Data" tab
of the "Update" Spreadsheet.
This info should be saved in Cell B2


I don't want to keep the formatting from the source spreadsheet.

When I attempted the macro (It's called "Get_Data") I used pastespecial and
transpose. It worked but it kept overwriting the data In row 2.

I then added offset and that worked, but then when I added more source
spreadsheets, it copied that data I wanted but then went back to overwriting
the data in row 2.


Source Spreadsheet:
Spreadsheet #1
Spreadsheet #2
Spreadsheet #3
etc.

Source Tab:
Today


Destination Spreadsheet: (This is where the Macro is saved).
Update

Destination Tab:
Get Data



I'm sure this is not explained as well as it could be, but it makes sense to
me. If you have any questions, just ask.


Thank you for your help,

Art.






=========================================================================================

I've included the Process_All_Workbooks Macro that I use.

Sub Process_All_Workbooks()

' Process_All_Workbooks Macro
' This Macro will open all spreadsheets in a particular folder.
' It will then run another Macro, and either save those spreadsheets or just
close them without saving.
' If you want to save the changes, use "ActiveWorkbook.Close
savechanges:=True" below.
' If you don't want the changes saved, use "ActiveWorkbook.Close
savechanges:=False".


' Macro created by Nikos Yannacopoulos. I modified it slightly to pass the
names of the spreadsheets - October 19th, 2006.



Dim fs, f, f1
Dim strFile As String
fldr = "D:\Test Area\" 'Put your folder path here.
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(fldr)
Set f1 = f.Files
i = 0
Application.ScreenUpdating = False
For Each f2 In f1
strFile = fldr & f2.Name
If Right(strFile, 4) = ".xls" Then
Workbooks.Open strFile



Call Get_Data(f2.Name) 'Call another macro or write code to do what you
want here.
Windows(f2.Name).Activate
ActiveWorkbook.Close savechanges:=False 'Change accordingly. True will
save the spreadsheet and False will not.

End If
Next
Application.ScreenUpdating = True
End Sub

=========================================================================================

To show my ignorance, I've attached my macro:



Sub Get_Data(ExcelFileName As String)


' This command prevents the screen from flickering. You have to turn it
on again at the bottom of the Macro.
Application.ScreenUpdating = False


' Go to the "Today" Tab and get any/all data in Cells A1 and A2.

Windows(ExcelFileName).Activate
Sheets("Today").Select
Range("A1:A2").Select
Selection.Copy
Windows("Update.xls").Activate
Sheets("Get Data").Select
Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False
Range("A2").Select









' This command turns off the screen flickering suppression.
Application.ScreenUpdating = True


End Sub

 

[Steve Schapel]

Art,

The focus of this newsgroup is macros in Access, which are totally
differnt creatures than macros in Excel. You will have a better chance
of a good answer in an Excel newsgroup.

 

[Art MacNeil]

Yup, sorry about that. I've re-posted my question in the Excel newsgroup.

Thanks.