Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update WorkBooks in Folder
D17 is really meaningless if there is more than one sheet in the workbook.
You don't say what your situation is. This assumes you don't have to test the current value of the cells. Sub UpdateBooks() Dim i as long, j as long, sStr as String dim myArray(1 to 500) as String i = 1 sStr = Dir("C:\Myfolder\*.xls") do while sStr < "" myArray(i) = "C:\MyFolder\" & sStr sStr = dir() Loop for j = 1 to i-1 set wkbk = workbooks.Open(myarray(i)) with wkbk.worksheets(1) .Range("D17,C25").Value = 1.3325 End with wkbk.Close SaveChanges:=True Next End Sub -- Regards, Tom Ogilvy "JavyD" wrote in message ... Hey guys, Lets see if this possible with a VBA. I have over 100 work books in a folder. There's two specific cells in each work book that I need to change. Currently the cell is multiplying a figure times 1.40, I need a VBA if possible to take each work book in that folder, and replace 1.40 with 1.3325 in those two cells. Those two cells being D17 and C25. Is this possible. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update WorkBooks in Folder
Sub UpdateBooks()
Dim i as long, j as long, sStr as String dim myArray(1 to 500) as String i = 1 sStr = Dir("C:\Myfolder\*.xls") do while sStr < "" myArray(i) = "C:\MyFolder\" & sStr sStr = dir() Loop for j = 1 to i-1 set wkbk = workbooks.Open(myarray(i)) with wkbk.worksheets(1) .Range("D17").formula = "= C17*1.3352" .Range("C25").formula = _ " =(SUM(B25*F20)/F19)/1.3352" End with wkbk.Close SaveChanges:=True Next End Sub -- Regards, Tom Ogilvy "JavyD" wrote in message ... This is what I want D17 to be = C17*1.3352 and C25 I want it to be =(SUM(B25*F20)/F19)/1.3352, both formulas, not values like the code below has. "JavyD" wrote: Tom, this sounds great, but would it replace D17 and C25 as value 1.3325. That's not exactly what I want, I want it to replace 1.40 with 1.3352? Is this possible? "Tom Ogilvy" wrote: D17 is really meaningless if there is more than one sheet in the workbook. You don't say what your situation is. This assumes you don't have to test the current value of the cells. Sub UpdateBooks() Dim i as long, j as long, sStr as String dim myArray(1 to 500) as String i = 1 sStr = Dir("C:\Myfolder\*.xls") do while sStr < "" myArray(i) = "C:\MyFolder\" & sStr sStr = dir() Loop for j = 1 to i-1 set wkbk = workbooks.Open(myarray(i)) with wkbk.worksheets(1) .Range("D17,C25").Value = 1.3325 End with wkbk.Close SaveChanges:=True Next End Sub -- Regards, Tom Ogilvy "JavyD" wrote in message ... Hey guys, Lets see if this possible with a VBA. I have over 100 work books in a folder. There's two specific cells in each work book that I need to change. Currently the cell is multiplying a figure times 1.40, I need a VBA if possible to take each work book in that folder, and replace 1.40 with 1.3325 in those two cells. Those two cells being D17 and C25. Is this possible. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update WorkBooks in Folder
It's not working Tom, no updating, why do you think?
"Tom Ogilvy" wrote: Sub UpdateBooks() Dim i as long, j as long, sStr as String dim myArray(1 to 500) as String i = 1 sStr = Dir("C:\Myfolder\*.xls") do while sStr < "" myArray(i) = "C:\MyFolder\" & sStr sStr = dir() Loop for j = 1 to i-1 set wkbk = workbooks.Open(myarray(i)) with wkbk.worksheets(1) .Range("D17").formula = "= C17*1.3352" .Range("C25").formula = _ " =(SUM(B25*F20)/F19)/1.3352" End with wkbk.Close SaveChanges:=True Next End Sub -- Regards, Tom Ogilvy "JavyD" wrote in message ... This is what I want D17 to be = C17*1.3352 and C25 I want it to be =(SUM(B25*F20)/F19)/1.3352, both formulas, not values like the code below has. "JavyD" wrote: Tom, this sounds great, but would it replace D17 and C25 as value 1.3325. That's not exactly what I want, I want it to replace 1.40 with 1.3352? Is this possible? "Tom Ogilvy" wrote: D17 is really meaningless if there is more than one sheet in the workbook. You don't say what your situation is. This assumes you don't have to test the current value of the cells. Sub UpdateBooks() Dim i as long, j as long, sStr as String dim myArray(1 to 500) as String i = 1 sStr = Dir("C:\Myfolder\*.xls") do while sStr < "" myArray(i) = "C:\MyFolder\" & sStr sStr = dir() Loop for j = 1 to i-1 set wkbk = workbooks.Open(myarray(i)) with wkbk.worksheets(1) .Range("D17,C25").Value = 1.3325 End with wkbk.Close SaveChanges:=True Next End Sub -- Regards, Tom Ogilvy "JavyD" wrote in message ... Hey guys, Lets see if this possible with a VBA. I have over 100 work books in a folder. There's two specific cells in each work book that I need to change. Currently the cell is multiplying a figure times 1.40, I need a VBA if possible to take each work book in that folder, and replace 1.40 with 1.3325 in those two cells. Those two cells being D17 and C25. Is this possible. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update WorkBooks in Folder
I left out one step, incrementing i
Sub UpdateBooks() Dim i as long, j as long, sStr as String dim myArray(1 to 500) as String i = 1 sStr = Dir("C:\Myfolder\*.xls") do while sStr < "" myArray(i) = "C:\MyFolder\" & sStr i = i + 1 sStr = dir() Loop for j = 1 to i-1 set wkbk = workbooks.Open(myarray(i)) with wkbk.worksheets(1) .Range("D17").formula = "= C17*1.3352" .Range("C25").formula = _ " =(SUM(B25*F20)/F19)/1.3352" End with wkbk.Close SaveChanges:=True Next End Sub -- Regards, Tom Ogilvy "JavyD" wrote in message ... It's not working Tom, no updating, why do you think? "Tom Ogilvy" wrote: Sub UpdateBooks() Dim i as long, j as long, sStr as String dim myArray(1 to 500) as String i = 1 sStr = Dir("C:\Myfolder\*.xls") do while sStr < "" myArray(i) = "C:\MyFolder\" & sStr sStr = dir() Loop for j = 1 to i-1 set wkbk = workbooks.Open(myarray(i)) with wkbk.worksheets(1) .Range("D17").formula = "= C17*1.3352" .Range("C25").formula = _ " =(SUM(B25*F20)/F19)/1.3352" End with wkbk.Close SaveChanges:=True Next End Sub -- Regards, Tom Ogilvy "JavyD" wrote in message ... This is what I want D17 to be = C17*1.3352 and C25 I want it to be =(SUM(B25*F20)/F19)/1.3352, both formulas, not values like the code below has. "JavyD" wrote: Tom, this sounds great, but would it replace D17 and C25 as value 1.3325. That's not exactly what I want, I want it to replace 1.40 with 1.3352? Is this possible? "Tom Ogilvy" wrote: D17 is really meaningless if there is more than one sheet in the workbook. You don't say what your situation is. This assumes you don't have to test the current value of the cells. Sub UpdateBooks() Dim i as long, j as long, sStr as String dim myArray(1 to 500) as String i = 1 sStr = Dir("C:\Myfolder\*.xls") do while sStr < "" myArray(i) = "C:\MyFolder\" & sStr sStr = dir() Loop for j = 1 to i-1 set wkbk = workbooks.Open(myarray(i)) with wkbk.worksheets(1) .Range("D17,C25").Value = 1.3325 End with wkbk.Close SaveChanges:=True Next End Sub -- Regards, Tom Ogilvy "JavyD" wrote in message ... Hey guys, Lets see if this possible with a VBA. I have over 100 work books in a folder. There's two specific cells in each work book that I need to change. Currently the cell is multiplying a figure times 1.40, I need a VBA if possible to take each work book in that folder, and replace 1.40 with 1.3325 in those two cells. Those two cells being D17 and C25. Is this possible. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update WorkBooks in Folder
That was for the code I originally provided - the code you said did nothing.
It is not for what you have cobbled together. -- Regards, Tom Ogilvy "JavyD" wrote in message ... Tom, the code is working fine without it, what is it for, do I have to put it in? "Tom Ogilvy" wrote: I left out one step, incrementing i Sub UpdateBooks() Dim i as long, j as long, sStr as String dim myArray(1 to 500) as String i = 1 sStr = Dir("C:\Myfolder\*.xls") do while sStr < "" myArray(i) = "C:\MyFolder\" & sStr i = i + 1 sStr = dir() Loop for j = 1 to i-1 set wkbk = workbooks.Open(myarray(i)) with wkbk.worksheets(1) .Range("D17").formula = "= C17*1.3352" .Range("C25").formula = _ " =(SUM(B25*F20)/F19)/1.3352" End with wkbk.Close SaveChanges:=True Next End Sub -- Regards, Tom Ogilvy "JavyD" wrote in message ... It's not working Tom, no updating, why do you think? "Tom Ogilvy" wrote: Sub UpdateBooks() Dim i as long, j as long, sStr as String dim myArray(1 to 500) as String i = 1 sStr = Dir("C:\Myfolder\*.xls") do while sStr < "" myArray(i) = "C:\MyFolder\" & sStr sStr = dir() Loop for j = 1 to i-1 set wkbk = workbooks.Open(myarray(i)) with wkbk.worksheets(1) .Range("D17").formula = "= C17*1.3352" .Range("C25").formula = _ " =(SUM(B25*F20)/F19)/1.3352" End with wkbk.Close SaveChanges:=True Next End Sub -- Regards, Tom Ogilvy "JavyD" wrote in message ... This is what I want D17 to be = C17*1.3352 and C25 I want it to be =(SUM(B25*F20)/F19)/1.3352, both formulas, not values like the code below has. "JavyD" wrote: Tom, this sounds great, but would it replace D17 and C25 as value 1.3325. That's not exactly what I want, I want it to replace 1.40 with 1.3352? Is this possible? "Tom Ogilvy" wrote: D17 is really meaningless if there is more than one sheet in the workbook. You don't say what your situation is. This assumes you don't have to test the current value of the cells. Sub UpdateBooks() Dim i as long, j as long, sStr as String dim myArray(1 to 500) as String i = 1 sStr = Dir("C:\Myfolder\*.xls") do while sStr < "" myArray(i) = "C:\MyFolder\" & sStr sStr = dir() Loop for j = 1 to i-1 set wkbk = workbooks.Open(myarray(i)) with wkbk.worksheets(1) .Range("D17,C25").Value = 1.3325 End with wkbk.Close SaveChanges:=True Next End Sub -- Regards, Tom Ogilvy "JavyD" wrote in message ... Hey guys, Lets see if this possible with a VBA. I have over 100 work books in a folder. There's two specific cells in each work book that I need to change. Currently the cell is multiplying a figure times 1.40, I need a VBA if possible to take each work book in that folder, and replace 1.40 with 1.3325 in those two cells. Those two cells being D17 and C25. Is this possible. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
linking cells from workbooks in a folder | Excel Worksheet Functions | |||
Loop through folder of workbooks and add rows | Excel Worksheet Functions | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions | |||
Update WorkBooks in Folder | Excel Programming | |||
Reading information from all workbooks in a folder... | Excel Programming |