Links - how exactly do they work?

[ajm]

I have been using excel for a few years now and have used external file links in loads of my work. Recently, I discovered something unusual in a file I was working on. Lets call it File A. File A is a hold all for certain things that we must report to the state government. after these certain things have been manually copied and pasted into place, vlookups to external workbooks (lets call these "the linked files" ) add a bit of extra data, and bob's your uncle.

The linked files are created each month by VBScript. While the linked file name is standard (each month the new file overwrites the old file), the name of the single tab within each of the respective linked files consists of a date and time string which indicates exactly when it was created. For example, the linked file name for one is Tax Code Data and this month the tab is called "11.00.45 AM, 6.25.2012 - Output". Last month, the tab was called "12.41.05 PM, 5.21.2012 - Output.csv"

The link in this month's File A is "=VLOOKUP($V5,'Z:\Reporting\GOVT\Script for Tax Code and MG\[Tax Code from VBS.xlsm]10.51.22 AM, 6.25.2012 - Output'!$A:$B,2,0'!$A:$B,2,0) while last month's was: "=VLOOKUP($V5,'Z:\Reporting\GOVT\Script for Tax Code and MG\[Tax Code from VBS.xlsm]12.41.05 PM, 5.21.2012 - Output'!$A:$B,2,0'!$A:$B,2,0)

When the new data is copied into File A, the vlookups take a while to calculate, and then the data is returned and the links have been changed to reflect the newly created link file. How is the link changing without being explicitly edited by me? Could Big Bill be adding a dose of AI to excel to keep us on our toes?

 

[ZVI]

May be VBScript acts as follows:

1. Opens master workbook
2. Opens/imports external workbook/data
3. Clears previous values in data sheet of the external workbook or skip this point
4. Puts new values into that sheet or just add it if the point 3 was skipped
5. Renames the data sheet of the external workbook
6. Saves the external workbook
7. Saves the master workbook

 

[ajm]

thanks for having a look Vladimir. the VB Script only creates the external linked files and has nothing to do with the Master File (File A above). Once created, I open each one and resave it as an excel file.