Change Excel Link Using VBA

[sukantaswain]

Hi Friends,

I have a Excel file ,which is linked with two diffrnet excel files (C:\For\333.xls)
and (C:\Cob\C_333.xls).

like 333.xls and C_333.xls i have similar files 111.xls and C_111.xls in the respective folders.i have these kind of 200 or more files

I am trying to develop a vba module to calculate the variance between 333.xls and C_333.xls and looping the same for other files

please help me out for the vba code

Requirment
code to changing the excel link and update the value
loop the same code for other files like 111.xls and C_111.xls (file name mentioned in a templete)

please help me out !!

Sukant

[D_Marcel]

Hi Sukant, be welcome to VBAX! Hum... what kind of variances you're calculating? Will you put the results in each Worksheet that is linked to this two kind of files (000.xls and C_000.xls)? If possible, send your Sheet so we can take a look and think together in a better way to help you.

Douglas Marcel

[raji2678]

If all else fails, you can write a macro to copy the contents to a single file and delete the file when everything is done.

[sukantaswain]

Hi Douglas,

I mean here i have sales number generated from two systems. one system generate 000.xls and other system generate C_000.xls for one vendor only . I have more then 200 vendors.

here i have a separate file where the month wise sales report is there.In this report file i am reconciling the sales report for two systems .

please look the steps how i work

environment
files are in our server and in a share drive. in a particular folder called sales we have two separete folder 1.FOR 2.COB
in folder "Sales" i have a file called sales report.xls

how i do the work
1. open the sales report .xls
2. change the link for 000.xls
3.change the link for C_000.xls
4. update the values
5. save the file as paste special values in a specific path.

i hv to do the exercise for all 200 files

so plesae help me out with a solution

thanks i

[georgiboy]

Hmmm without demo spreadsheets it's hard to understand what you are trying to calculate. Going on a guess I think I would pull all spreadsheets into one workbook and work fro there.