Hi, hoping someone can help out a clueless person here :)

We have a massive amount of Metadata store in excel files that needs to
be converted from one layout to another - a macro seems like the
sensible thing to save me from weeks of tedious copying and pasting.

I have tried to have a go at this with the following code I found on
usenet by Bob Phillips:


Code:
--------------------
Sub ProcessFiles()
Dim oThis As Worksheet
Dim oFSO As Object
Dim oFiles As Object
Dim oFile As Object
Dim sFolder As String
Dim oFolder As Object
Dim i As Long

Application.ScreenUpdating = False

Set oThis = ActiveSheet
Set oFSO = CreateObject("Scripting.FileSystemObject")
sFolder = "C:\MyTest"

If sFolder < "" Then
Set oFolder = oFSO.GetFolder(sFolder)
Set oFiles = oFolder.Files
For Each oFile In oFiles
If oFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=oFile.Path
With ActiveWorkbook
oThis.Cells(1, "A").Value = .ActiveSheet.Range("A1").Value
.Close savechanges:=False
End With
End If
Next oFile
End If ' sFolder < ""

Application.ScreenUpdating = True

End Sub
--------------------


This sucessfully copies cell 1,A from a workbook in the "c:\MyTest"
folder to the active spreadsheet, but I can't figure out how to specify
a range to copy (I tried the following)


Code:
--------------------
oThis.Cells("1,A":"20,"A").Value = .ActiveSheet.Range("A1").Value
--------------------


But that just gave my a synax error. If anyone could shed any light on
this I would be eternally greatful! :)

Thanks
jonny.


--
jonnyreeves
------------------------------------------------------------------------
jonnyreeves's Profile: http://www.excelforum.com/member.php...o&userid=31308
View this thread: http://www.excelforum.com/showthread...hreadid=509993

multiple cell contiguous ranges are specified as Range("A1:Z26"), not
whatever notation you are using. The cells object doesn't support multiple
cell contiguous ranges, but you can append a resize to get around it
..Cells(1,1).Resize(20,1) would be A1:A20


you can change the code to

oThis.Range("A1:Z26").Value = .ActiveSheet.Range("A1:Z26").Value

the number of cells and the shape of the range must be the same on both
sides of the equal sign. They don't have to have the upper left corner in
the same position.

another appoach using cells would be (20 rows, 10 columns)

oThis.Cells(1,1).Resize(20,10).Value =
..Activesheet.Cells(35,2).Resize(20,10).Value

--
Regards,
Tom Ogilvy


"jonnyreeves"
wrote in message
...

Hi, hoping someone can help out a clueless person here :)

We have a massive amount of Metadata store in excel files that needs to
be converted from one layout to another - a macro seems like the
sensible thing to save me from weeks of tedious copying and pasting.

I have tried to have a go at this with the following code I found on
usenet by Bob Phillips:


Code:
--------------------
Sub ProcessFiles()
Dim oThis As Worksheet
Dim oFSO As Object
Dim oFiles As Object
Dim oFile As Object
Dim sFolder As String
Dim oFolder As Object
Dim i As Long

Application.ScreenUpdating = False

Set oThis = ActiveSheet
Set oFSO = CreateObject("Scripting.FileSystemObject")
sFolder = "C:\MyTest"

If sFolder < "" Then
Set oFolder = oFSO.GetFolder(sFolder)
Set oFiles = oFolder.Files
For Each oFile In oFiles
If oFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=oFile.Path
With ActiveWorkbook
oThis.Cells(1, "A").Value = .ActiveSheet.Range("A1").Value
.Close savechanges:=False
End With
End If
Next oFile
End If ' sFolder < ""

Application.ScreenUpdating = True

End Sub
--------------------


This sucessfully copies cell 1,A from a workbook in the "c:\MyTest"
folder to the active spreadsheet, but I can't figure out how to specify
a range to copy (I tried the following)


Code:
--------------------
oThis.Cells("1,A":"20,"A").Value = .ActiveSheet.Range("A1").Value
--------------------


But that just gave my a synax error. If anyone could shed any light on
this I would be eternally greatful! :)

Thanks
jonny.


--
jonnyreeves
------------------------------------------------------------------------
jonnyreeves's Profile:
http://www.excelforum.com/member.php...o&userid=31308
View this thread: http://www.excelforum.com/showthread...hreadid=509993