I need to create a macro that does all of the following in order;

I open a new workbook and start my macro which
1. goes to a specific folder and opens all of the files in there (excell
spreadsheets)
2. for each file I need the macro to highlight all of column g and do a
"text to column" with the output being input into the workbook I opened at
the start with the output from each individual file being inserted under the
output from the last one.

Is this possible?

Thanks

Martina,

Why don't you do it once with the macro recorder on ... it will store
precisely all your steps ...
HTH

Carim

Well, basically the point behind this is, i need to open all of the files
that are in a folder, and this needs to be done at the end of each month (it
is to create monthly reports).

But there could be 2 files in there at the end of one month and then at the
end of the next month their might be 20.



"Carim" wrote:

Martina,

Why don't you do it once with the macro recorder on ... it will store
precisely all your steps ...
HTH

Carim

Martina,

Then you should try in the object "In this Workbook" an automatic open
such as

Private Sub Workbook_Open()
Workbooks.Open("D:\My Documents\Excel\Data Files.xls")
End Sub

HTH
Carim

Option Explicit

Sub GetData()

Dim rw As Long
Dim sfile As String
Dim wb As Workbook ' book being opend
Dim ws As Worksheet
Dim wsResult As Worksheet
Dim wbResult As Workbook
Dim root As String
Dim source As Range

rw = 1
root = "C:\temp\*.xls"

sfile = Dir(root)
If sfile = "" Then
MsgBox "Nofiles found!"
Else

Set wbResult = Workbooks.Add(xlWBATWorksheet)
Set wsResult = wbResult.ActiveSheet

Do

Set wb = Workbooks.Open(sfile)
Set ws = wb.ActiveSheet
With ws
Set source = .Range(.Range("G1"), .Range("G1").End(xlDown))
End With
With wsResult
.Cells(rw, 1).Resize(source.Rows.Count, 1).Value =
source.Value
End With
rw = rw + source.Rows.Count
wb.Close False

sfile = Dir()
Loop Until sfile = ""

MsgBox "Done"
End If

' now add the code to text to column for the resulting column A

End Sub

Patrick Molloy
Microsoft Excel MVP


"MartinaL" wrote:

I need to create a macro that does all of the following in order;

I open a new workbook and start my macro which
1. goes to a specific folder and opens all of the files in there (excell
spreadsheets)
2. for each file I need the macro to highlight all of column g and do a
"text to column" with the output being input into the workbook I opened at
the start with the output from each individual file being inserted under the
output from the last one.

Is this possible?

Thanks