Links - how exactly do they work?

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,794
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?
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,814
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Feb 5, 2003
Messages
1,794
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,932
Messages
5,621,682
Members
415,849
Latest member
PhoenixRising2015

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top