first post on these forums, and am fairly new to vba. i've written some code that opens a workbook called masterfile.xls, which features links to another workbook containing last month's figures, in a file called oldvalues.xls.
I'd like to update masterfile.xls to read in this month's figures instead, which are in newvalues.xls.
actually, i can do this OK in VBA. The code i use is below:
So what's the problem? Well, at work, I can't use the filepaths as above, because all the files are on a network drive, and different people map the same drive to different driveletters, e.g. possibly not "D:" as above. so, the obvious solution would be to use the UNC full server name.
e.g. "D:\folder1\folder 2\masterfile.xls" is really
so i could try to use the full version of the pathname for all the above files as in
unfortunately, this macro fails, and i get "run-time error '1004' method 'changelink' of object '_workbook' failed." i really don't know why this is, since both versions refer to exactly the same files, it's just that i've referred to them differently. that seems to make excel fall over though. i know that my code doesnt have any issues with understanding the UNC filename in general, since i use that format everywhere else in my program. in fact, the masterfile.xls even opens OK, it just refuses to update links.
any ideas greatly appreciated.