Support NeoGAF

[bishoptl]

Any help would be appreciated

I have this workbook that reads info in from data files (not excel workbooks) which is then in turn used in another workbook to calculate values. The problem is that these files were moved to another location and now I either have to manually relink them all, or find a way to change where the excel workbook points when it tries to refresh data.

Before you say go to Edit > Links, that only works to restore broken links between workbooks I need to know how to restore a link between where Excel is referencing the data files for import/refreshing. Right now the workbook keeps looking in the old location.

The structure is like this:

Workbook1 links to Workbook2 links to raw data

Let's say for example, it was in C: but now the data and workbooks are in D: but the workbook still looks for the data in C:. It wouldn't be a big deal but in Workbook2 there are several hundred references to the raw data that all need to be relinked manually if I can't find a way to effect a global relink.

 

[Hooker]

You can't move those files back to their original place?!


(sorry for the "stupid" suggestion, but lots of people forget about the easier way out)

 

[bishoptl]

No, that's not an option.

 

[8bit]

I'd check if the logic is contained in a macro, or perhaps referenced as a dsn from settings>admin tools>data sources.

 

[bishoptl]

Maybe I'm blind but I don't see this 'settings' tab anywhere. Help?

 

[Maxwell House]

Oh n/m, i misread what your problem is. Sorry, I am not sure how to help you.

 

[AirBrian]

Without a little more info, it's hard to say where they're coming from. Are they macros you run? Does it load automatically when you open the file?

To check the macros:

Tools --> Marcro --> Macros...

To check automatic external data links:

Data --> Import External Data --> Edit Query...

These are the first two places I'd check.

EDIT: This is assuming you're using Excel XP/2003.

 

[8bit]

Maybe I'm blind but I don't see this 'settings' tab anywhere. Help?

I meant the external one, not contained within Excel.

Start Menu>Settings>Control Panel>Administrative Tools>Data Sources

You can sometimes have external data sources there referenced by a Spreadsheet or Database.

 

[AirBrian]

Before you say go to Edit > Links, that only works to restore broken links between workbooks I need to know how to restore a link between where Excel is referencing the data files for import/refreshing. Right now the workbook keeps looking in the old location.

And you've checked the "Change Source" in the "Edit Links" box? That should work for the non-excel file too. I've got a file that does the same thing and automatically imports a .txt file. I changed the location there when IT moved some servers awhile back.