Open file, copy field and paste into an Excel doc! - Pl help ..

[Harish Mohanbab]

Hi,

There are around 700 files in a particular directory. This directory is mapped to my drive (F:\)

What I want to do is -

1) Open the files one by one in the above directory
2) Copy just a particular column (B3) alone in all those files
3) And paste them all into a different Excel file

These files are in XML format, but could be opened with Excel.

Can someone help me please.

Thanks,

Harish Mohanbabu

[Toppers]

Hi,
See reply to previous posting (based on B4 not B3) .

"Harish Mohanbab" wrote:

>
> Hi,
>
> There are around 700 files in a particular directory. This directory is
> mapped to my drive (F:\)
>
> What I want to do is -
>
> 1) Open the files one by one in the above directory
> 2) Copy just a particular column (B3) alone in all those files
> 3) And paste them all into a different Excel file
>
> These files are in XML format, but could be opened with Excel.
>
> Can someone help me please.
>
> Thanks,
>
> Harish Mohanbabu
>
>
> --
> Harish Mohanbab
>
>
> ------------------------------------------------------------------------
> Harish Mohanbab's Profile: http://www.excelforum.com/member.php...fo&userid=6332
> View this thread: http://www.excelforum.com/showthread...hreadid=392859
>
>

[Harish Mohanbab]

Hi there,

Thank you very much for you help I had to tweak it slightly. But otherwise it was great Thanks for your help again.

I am posting the code below which I hope might be of use to somebody some day. Here I go -

.................................................
Private Sub CommandButton1_Click()

Dim i As Integer
Dim wb As Workbook
Dim outrng As Range

i = 1
Set fs = Application.FileSearch
Set wb = ThisWorkbook


With fs
.LookIn = "X:/"
.Filename = "*.*"
.SearchSubFolders = False
.Execute
For i = 1 To .FoundFiles.Count
Range("A" & i).Value = .FoundFiles(i)

'THIS IS TO COPY THE AMOUNT
Workbooks.Open .FoundFiles(i)
Worksheets(1).Range("B3").Copy
Workbooks(FileNameOnly(.FoundFiles(i))).Close savechanges = False
With wb.Worksheets("Sheet1")
.Range("B" & i).PasteSpecial Operation:=xlPasteSpecialOperationAdd
End With

Next
End With

End Sub


Function FileNameOnly(pname) As String
' Returns the filename from a path/filename string
Dim i As Integer, length As Integer, temp As String
length = Len(pname)
temp = ""
For i = length To 1 Step -1
If Mid(pname, i, 1) = Application.PathSeparator Then
FileNameOnly = temp
Exit Function
End If
temp = Mid(pname, i, 1) & temp
Next i
FileNameOnly = pname
End Function
.................................................

Harish Mohanbabu