Exporting & Importing with macros
 

It's possible to write a macro that copy a value that is locate always
in the same position but in different files, this files follow a
nomeclature with logic. And after that paste it in other file.

Example:
In "file1.xls" I want to copy the value in cell "B6" and copy in the
file "all.xls" in cell "B1"
In "file2.xls" I want to copy the value in cell "B6" and copy in the
file "all.xls" in cell "B2"
In "file3.xls"...
And then towards...
Thanks

Exporting & Importing with macros
 

Hi there

One way to do it would be to use the file system object. This method
is handy if you don't want to hard code all the file names you want to
check. It works by checking all the files in the given folder (C:
\Test) in this example. It will loop through all the files and check
for the excel file extension when it files the files it will open them
read the value in B6 and then close the file and write the stored
value into the next available cell in column B of the workbook that
holds the code.

Sub GetCellB6FromEachFile()

Dim intRow As Integer
Dim objFSO As Object
Dim objFile As Object
Dim objFolder As Object
Dim vntB6Value As Variant
Dim wkbk As Workbook

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.getfolder("C:\Test")
intRow = 1

For Each objFile In objFolder.Files
If Right(objFile.Name, 4) = ".xls" Then
Set wkbk = Workbooks.Open(objFile)
With wkbk
vntB6Value = Sheets(1).[B6].Value
wkbk.Saved = True
.Close
Set wkbk = Nothing
End With
ThisWorkbook.Sheets(1).Cells(intRow, 2).Value = _
vntB6Value
intRow = intRow + 1
End If
Next

Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing

End Sub

I hope this helps

Steve

Exporting & Importing with macros
 

Option Explicit

Sub getdata()

Dim fn As String
Dim wb As Workbook
Dim index As Long

Const filepath As String = "H:\excel\test\"

fn = Dir(filepath & "*.xls")

Do Until fn = ""
index = Mid(fn, 5, Len(fn) - 8)

Set wb = Workbooks.Open(filepath & fn)
ThisWorkbook.ActiveSheet.Cells(index, "B") =
wb.ActiveSheet.Range("B6").Value
wb.Close False
fn = Dir()
Loop


End Sub


"CAUÃ" wrote:

It's possible to write a macro that copy a value that is locate always
in the same position but in different files, this files follow a
nomeclature with logic. And after that paste it in other file.

Example:
In "file1.xls" I want to copy the value in cell "B6" and copy in the
file "all.xls" in cell "B1"
In "file2.xls" I want to copy the value in cell "B6" and copy in the
file "all.xls" in cell "B2"
In "file3.xls"...
And then towards...
Thanks

Exporting & Importing with macros
 

my files were BOOK1.xls, BOOK2.xls and so on
so the index started at the 5th character and the number of characters was
the len() of the file name less 8 characters (BOOK + .XLS)

yours will be the same if th efiles all start with the word 'file'


"Patrick Molloy" wrote:

Option Explicit

Sub getdata()

Dim fn As String
Dim wb As Workbook
Dim index As Long

Const filepath As String = "H:\excel\test\"

fn = Dir(filepath & "*.xls")

Do Until fn = ""
index = Mid(fn, 5, Len(fn) - 8)

Set wb = Workbooks.Open(filepath & fn)
ThisWorkbook.ActiveSheet.Cells(index, "B") =
wb.ActiveSheet.Range("B6").Value
wb.Close False
fn = Dir()
Loop


End Sub


"CAUÃ" wrote:

It's possible to write a macro that copy a value that is locate always
in the same position but in different files, this files follow a
nomeclature with logic. And after that paste it in other file.

Example:
In "file1.xls" I want to copy the value in cell "B6" and copy in the
file "all.xls" in cell "B1"
In "file2.xls" I want to copy the value in cell "B6" and copy in the
file "all.xls" in cell "B2"
In "file3.xls"...
And then towards...
Thanks