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

 

[Guest]

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

 

[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