If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed.
I am looking for some examples to get me going on a project to read a text file which contains in column a and column b file name pairs. I want to open the two files referred to in a1 and b1, copy a range from file b1 into a set location in file a1, save a1, close both, then loop to files a2 and b2 etc. Have about 1000 pairs to loop through so investing time into getting this to work in an automated fashion would be great.
Anyone have some examples or ideas regarding the toughest part which to me is getting the file name pairs, copying code from b to a?
Re: merge multiple files from text list of file pairs
Hi Rob,
Try something like:
Sub Tester()
Dim wbList As Workbook
Dim wbSrc As Workbook
Dim wbDest As Workbook
Dim rcell As Range
Const copyAddress As String = "F1:F4"
Const destAddress As String = "A1"
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
Set wbList = Workbooks.Open("YourFilelistBook")
For Each rcell In wbList.Sheets(1).Range("A1"). _
CurrentRegion.Columns(1).Cells
Set wbDest = Workbooks.Open(rcell.Value)
Set wbSrc = Workbooks.Open(rcell(1, 2).Value)
wbSrc.Sheets(1).Range(copyAddress).Copy _
Destination:=wbDest.Sheets(1).Range(destAddress)
wbSrc.Close savechanges:=False
wbDest.Close savechanges:=True
Set wbSrc = Nothing
Set wbDest = Nothing
Next
wbList.Close savechanges:=False
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub
---
Regards,
Norman
"rroach" <rroach.1qldeb_1118711105.1403@excelforum-nospam.com> wrote in
message news:rroach.1qldeb_1118711105.1403@excelforum-nospam.com...
>
> I am looking for some examples to get me going on a project to read a
> text file which contains in column a and column b file name pairs. I
> want to open the two files referred to in a1 and b1, copy a range from
> file b1 into a set location in file a1, save a1, close both, then loop
> to files a2 and b2 etc. Have about 1000 pairs to loop through so
> investing time into getting this to work in an automated fashion would
> be great.
>
> Anyone have some examples or ideas regarding the toughest part which to
> me is getting the file name pairs, copying code from b to a?
>
> Thanks.
>
> Rob
>
>
> --
> rroach
> ------------------------------------------------------------------------
> rroach's Profile:
> http://www.excelforum.com/member.php...o&userid=21093
> View this thread: http://www.excelforum.com/showthread...hreadid=378844
>
Linear Mode
