update excel files using a vba script
 

Hello. Is there a way i could update one or several files at once, using
data from another excel file? For exemple in file exemple1.xls i have in
cells a2 and a3 certain values that i want in files exemple2.xls and
exemple3.xls. I would need this for 2 or more files Thank you!


--
adinic
------------------------------------------------------------------------
adinic's Profile: http://www.excelforum.com/member.php...o&userid=31529
View this thread: http://www.excelforum.com/showthread...hreadid=533385

update excel files using a vba script
 

In the files you want to share the data, you could open them and put in a
formula

in one cell
='C:\Myfolder\[exemple1.xls]Sheet1'!A2

and in another cell another formula

='C:\Myfolder\[exemple1.xls]Sheet1'!A3

Do this in each of the workbooks.

If you don't want a formula, then you would have to both the source workbook
and the destination workbook open at the same time (which you can do in code)

so in the source workbook you would have code like

Sub UpdateValues()

Dim rng1 as Range, bk as Workbook
Dim i as Long
set rng1 = Thisworkbook.Worksheets(1).Range("A2")
for i = 2 to 5
set bk = workbooks.open("C:\myfolder\exemple" & i & ".xls")
bk.worksheets(1).Range("A2") = rng1.Value
bk.Worksheets(1).Range("A3") = rng1.offset(1,0).Value
bk.Close SaveChanges:=True
Next

End Sub

--
Regards,
Tom Ogilvy

"adinic" wrote:


Hello. Is there a way i could update one or several files at once, using
data from another excel file? For exemple in file exemple1.xls i have in
cells a2 and a3 certain values that i want in files exemple2.xls and
exemple3.xls. I would need this for 2 or more files Thank you!


--
adinic
------------------------------------------------------------------------
adinic's Profile: http://www.excelforum.com/member.php...o&userid=31529
View this thread: http://www.excelforum.com/showthread...hreadid=533385

update excel files using a vba script
 

Thank you very much for your help. But i would need an "open" window so
that i can select the files that i want to update. I would also need
this window to open at a specified location. Thank you again!


--
adinic
------------------------------------------------------------------------
adinic's Profile: http://www.excelforum.com/member.php...o&userid=31529
View this thread: http://www.excelforum.com/showthread...hreadid=533385

update excel files using a vba script
 

use
application.GetOpenFileName

with the multiselect property set to true.

then loop through the returned array with the code I provided.

You can use Chdrive and Chdir to specify the location where GetOpenFileName
would open.


--
Regards,
Tom Ogilvy


"adinic" wrote:


Thank you very much for your help. But i would need an "open" window so
that i can select the files that i want to update. I would also need
this window to open at a specified location. Thank you again!


--
adinic
------------------------------------------------------------------------
adinic's Profile: http://www.excelforum.com/member.php...o&userid=31529
View this thread: http://www.excelforum.com/showthread...hreadid=533385

update excel files using a vba script
 

Unfortunately i have no idea how to do that. I would apreciate very much
if you could post the entire code. Thank you so much!


--
adinic
------------------------------------------------------------------------
adinic's Profile: http://www.excelforum.com/member.php...o&userid=31529
View this thread: http://www.excelforum.com/showthread...hreadid=533385

update excel files using a vba script
 

Sub UpdateFiles()
Dim v As Variant
Dim rng1 As Range, bk As Workbook
Dim i As Long
ChDrive "C"
ChDir "C:\MyFolder"

v = Application.GetOpenFilename( _
FileFilter:="Excel Files (*.xls),*.xls", _
Title:="Select Multiple Files", _
MultiSelect:=True)
If IsArray(v) Then
Set rng1 = ThisWorkbook.Worksheets(1).Range("A2")
For i = LBound(v) To UBound(v)
Set bk = Workbooks.Open(v(i))
bk.Worksheets(1).Range("A2") = rng1.Value
bk.Worksheets(1).Range("A3") = rng1.Offset(1, 0).Value
bk.Close SaveChanges:=True
Next
Else
MsgBox "No files to process"
End If

End Sub

--
Regards,
Tom Ogilvy


"adinic" wrote:


Unfortunately i have no idea how to do that. I would apreciate very much
if you could post the entire code. Thank you so much!


--
adinic
------------------------------------------------------------------------
adinic's Profile: http://www.excelforum.com/member.php...o&userid=31529
View this thread: http://www.excelforum.com/showthread...hreadid=533385

update excel files using a vba script
 

Thank you! It worked! What if i want to update a range of cells. Let's
say from cell d7 to d107. How do i do that? Thank you again! Have a
nice day!


--
adinic
------------------------------------------------------------------------
adinic's Profile: http://www.excelforum.com/member.php...o&userid=31529
View this thread: http://www.excelforum.com/showthread...hreadid=533385

update excel files using a vba script
 

Thank you! It worked! What if i want to update a range of cells. Let's
say from cell d7 to d107. How do i do that? Thank you again! Have a
nice day!


--
adinic
------------------------------------------------------------------------
adinic's Profile: http://www.excelforum.com/member.php...o&userid=31529
View this thread: http://www.excelforum.com/showthread...hreadid=533385

update excel files using a vba script
 

Sub UpdateFiles()
Dim v As Variant
Dim rng1 As Range, bk As Workbook
Dim i As Long
ChDrive "C"
ChDir "C:\MyFolder"

v = Application.GetOpenFilename( _
FileFilter:="Excel Files (*.xls),*.xls", _
Title:="Select Multiple Files", _
MultiSelect:=True)
If IsArray(v) Then
Set rng1 = ThisWorkbook.Worksheets(1).Range("D7")
For i = LBound(v) To UBound(v)
Set bk = Workbooks.Open(v(i))
bk.Worksheets(1).Range("D7:D107") = rng1.Resize(101,1).Value
bk.Close SaveChanges:=True
Next
Else
MsgBox "No files to process"
End If

End Sub


--
Regards,
Tom Ogilvy

"adinic" wrote:


Thank you! It worked! What if i want to update a range of cells. Let's
say from cell d7 to d107. How do i do that? Thank you again! Have a
nice day!


--
adinic
------------------------------------------------------------------------
adinic's Profile: http://www.excelforum.com/member.php...o&userid=31529
View this thread: http://www.excelforum.com/showthread...hreadid=533385