Excel Macro that Auto Updates Links/Refreshes

[SeanCook]

So i am trying to make a macro on a workbook(WB1). It has cells that link to other Workbooks (WB2-WB10). These (WB2-WB10) have two sheets that are linked to an access point which contains "1.xls" and "2.xls" (sheet1 linked to 1.xls, and sheet2 to 2.xls). I have tried a macro that will open the file, update links then save and close. Also tried the same but refreshall instead of update links, then again with a marco that does both, and yet everytime it runs, it goes through all the workbooks one by one, opens them, calculates, saves and closes, and yet when i go back to check, nothing is updated.

I have checked the access data and it is updated, and if i go into each workbook and manually click refresh it does indeed refresh. I am at a loss and dont know what i am doing wrong.

here is the code.


Sub GetFile()
Workbooks.Open "blahblah.xlsx", UpdateLinks:=1
ActiveWorkbook.RefreshAll
ActiveWorkbook.Close SaveChanges:=True

Any help would be greatly appreciated.

Thank You,

Sean

 

[dalbright]

"Unknown" and the only options available are: Open Source and Check Status, the others are grayed out.

The other message I'm getting in that field is "Error:Source not found"

 

[BiocideJ]

"Unknown" and the only options available are: Open Source and Check Status, the others are grayed out.

The other message I'm getting in that field is "Error:Source not found"

Unknown probably just means the source file is already open.
The line that has Error:Source not found is definitely your culprit. I would check to verify that whatever is on that line is definitely correct AND that you have access to open it. If you select that line and hit Open Source it may give you an indication as to what exactly is going on.

 

[dalbright]

So far I have gotten this to work except when it gets to ActiveWorkbook.Update.....a window pops up (which is the correct folder) and wants me to physically select the "child.xlsx" file. Once I do that the links are upated.

Any ideas?


Sub Macro1()
'
' Macro1 Macro
'
'
Sheets("Rollup Exempt").Activate
ActiveSheet.Unprotect ("password")
ChDir "\\srint2\HRSALARY\REVIEWS\ALL\Master\Child"
Workbooks.Open Filename:= _
"\\srint2\hrsalary\reviews\all\Master\Child\Child Spring 2012.xlsx"
Sheets("Recommendations Exempt").Select
ActiveSheet.Unprotect ("password")
Sheets("Recommendations Non-exempt").Select
ActiveSheet.Unprotect ("password")

Windows("Master Spring 2012.xlsm").Activate

ActiveWorkbook.UpdateLink Name:= _
"\\srint2\REVIEWS\ALL\Master\Child\Child Spring 2012.xlsx", Type:= _
xlExcelLinks




Range("V10").Select
End Sub

 

[BiocideJ]

If it is opening a window prompting you to select the file that (in every instance I have seen) means it cannot find the file 'exactly' how you are directing it.
You mentioned you recently upgraded to Excel 2007. Are the child files that you are linking to actually .xlsx or are they perhaps still .xls?
Or maybe .xlsm or some other variation that Excel 2007 and later likes to throw at you?

EDIT: You know... this COULD also be trying to reference a tab on the spreadsheet that no longer exists.

 

[dalbright]

They are all *.xlsx files. I've got my boss sitting with me to see now if we can figure this out.

 

[dalbright]

Do you know if it makes a difference if there are spaces in the file name?

 

[BiocideJ]

It shouldn't as long as the filename is enclosed in apostrophes such as...
'\\srint2\REVIEWS\ALL\Master\Child\[Child Spring 2012.xlsx]SheetName1'!$A$1

EDIT: Ignore the underline I couldn't get rid of it.

 

[dalbright]

It appears that Excel 2007 does not like the UNC \\driveName\Folder format. When I mapped my X drive to the network folder and in the link changed the link from \\driveName\Folder\Filename.xlsx to X\Folder\Filename.xlsx the links work and i no longer get the error.

 

[ailuffee]

what if i need to refresh values off all files in one folder; not only one file, but all files such as ''blahblah.xlsx", ''blahblah2.xlsx", ''blahblah3.xlsx",and so on; all files stored in the same folder;

many thanks..

 

[BiocideJ]

In the future, please create a new post as opposed to posting on a 3 year old thread. That way anyone on can see and answer this question as opposed to hoping that someone who is subscribed to this thread will notice it.

That being said, I now use this code to update all links in a workbook.

ActiveWorkbook.UpdateLink ActiveWorkbook.LinkSources