|
|
Open file, copy field and paste into an Excel doc! - Pl help .. |
|
Author |
Message |
tcidawn
|
Posted: Thu Aug 04 10:01:26 CDT 2005 |
Top |
Excel Programming >> Open file, copy field and paste into an Excel doc! - Pl help ..
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.hide-link.com/ ;userid=6332
View this thread: http://www.hide-link.com/
Excel524
|
|
|
|
|
Toppers
|
Posted: Thu Aug 04 10:01:26 CDT 2005 |
Top |
Excel Programming >> Open file, copy field and paste into an Excel doc! - Pl help ..
Hi,
See reply to previous posting (based on B4 not B3) .
>
> 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?action=getinfo&userid=6332
> View this thread: http://www.excelforum.com/showthread.php?threadid=392859
>
>
|
|
|
|
|
Harish
|
Posted: Thu Aug 04 10:24:02 CDT 2005 |
Top |
Excel Programming >> Open file, copy field and paste into an Excel doc! - Pl help ..
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
--
Harish Mohanbab
------------------------------------------------------------------------
Harish Mohanbab's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6332
View this thread: http://www.excelforum.com/showthread.php?threadid=392859
|
|
|
|
|
|
|