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