J
jonnyreeves
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.
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.