Updating workbooks.....

Randal G · Mar 25, 2003

I’m looking for a simple but “automatic” way to update all my workbooks by way of a macro assigned to a button in the source workbook. The action I’m thinking of is when the macro is activated it opens each closed linked workbook ( minimized if possible so they aren’t flashing the screen ) , and then automatically saves the file and closes them and also completes the action by saving and closing any workbooks already open. My source workbook has 9 workbooks. Can anyone help me with this?
Thanks
Randal G

Legacy 98055 · Mar 26, 2003

Just curious as to why you need to do this??? The workbooks default to UpdateRemoteReferences = True which means the linked books will update upon opening. Why update like this??? You have a reason, I'm sure, but I would like to know before I write some example code. Want to make sure it is neccesary!

Tom

Randal G · Mar 26, 2003

TsTom said:
Just curious as to why you need to do this??? The workbooks default to UpdateRemoteReferences = True which means the linked books will update upon opening. Why update like this??? You have a reason, I'm sure, but I would like to know before I write some example code. Want to make sure it is neccesary!

Tom

Tom,
Your exactly right, they update upon opening. With 9 dependant workbooks (and I'm getting ready to add another) I'm just looking for a simple way with one click to open and close all books to update all of them at the end of the day before I run my tape backup (something else I've added). This is redundancy but as I've learned from a past experience (system crash

.. redundancy is good. My source book got corupted but no the dependant books, of course this resulted in lost of info from source book and an entire days work of labor and other entries. I was actually considering setting this up as a workbook code that would run say every hour automatically but I've still got to be able to do it manually. Long story short, being able to do this is a matter of contentment and an insurance that ALL workbooks have been updated daily and all have been saved and closed. And due to the size of the files and limited resources of my shop computer they really need to open and close individually. I know I'm sure I'm asking for allot but being able to do this would be nice and beats what I'm doing now which is opening, closing and saving each one individually. I hope this explains this well enough. Thank you for your time. It is much appreciated.
Randal G.

Guitarde · Mar 26, 2003

I'm not sure you need to open all the Workbooks, to be sure you can force the refresh of the links.

ActiveWorkbook.UpdateLink Name:="Workbook_no_1.xls", Type:= _
xlExcelLinks

Regards
Eric

Randal G · Mar 26, 2003

Guitarde said:
I'm not sure you need to open all the Workbooks, to be sure you can force the refresh of the links.

ActiveWorkbook.UpdateLink Name:="Workbook_no_1.xls", Type:= _
xlExcelLinks
-----------------------------------------------------------------------------
Either I'm not smart enough to know how to set this up or it just isn't achieving the task as implied???
Regards'
Randal G.

Guitarde · Mar 26, 2003

I thought about :

ActiveWorkbook.UpdateLink Name:="Workbook_no_1.xls", Type:= xlExcelLinks
ActiveWorkbook.UpdateLink Name:="Workbook_no_2.xls", Type:= xlExcelLinks
ActiveWorkbook.UpdateLink Name:="Workbook_no_3.xls", Type:= xlExcelLinks

upto spreadsheet # 9

Salutations !
Eric

Randal G · Mar 26, 2003

Guitarde,
Alrighty,
Could you please assist me on how to set this up.
1.I assume that ( :="Workbook_no_1.xls", ) I am supposed to enter the actual name of each file (workbook) and do I include the source workbook in this list?
2. And where am I to place this code? I tried the sheet and workbook and get errors. Sorry, I'm just a little lost here...

3. Is this code supposed to update data to workbooks that are closed?

Thanks again,
Randal G.

Legacy 98055 · Mar 26, 2003

Guitarde,
I do hope I am wrong, but I don't think you can update dependent links in a closed workbook. If you can then I have never seen a way to do this??? I have tried to figure out a way to do this in the past and have not been able. As far as I can tell, the UpdateLink method updates precedents only...

Anyway, Randal, if you have not yet found a solution, here is a procedure and an accompanying function which will update your workbooks without much interference. As a practice, I avoid placing constants used in code in a range and, therefore, this procedure will use workbook names to find your files which need to be updated. Without editing the code it'self, the only criteria is that each name will need to begin with "Dependent". For example, you have 9 files, as of now, which you would like to update. From the Worksheet Menu Bar, in your Source workbook, choose Insert, Name, Define. Type in the name of your first definition, "Dependent01". In the "refers to" textbox at the bottom, type in the full pathname of this file. Hit enter or click on Add. Do this for each of your 9 workbooks. You should have now defined 9 workbook names with each name referring to a correct path. Save you Source file! Now you're ready to go. Place this code in a standard module and run the public procedure "OpenUpdateSave" as you wish. Assign it to a button or call it from the Click_Event of some control you have drawn out on your worksheet. If all goes well, the procedure will simply run and end. If not, you will get a messagebox telling you what the problem is. There is one additional procedure which you may or may not want to utilize. ShowPrecendentNames. I don't know your users, but you may want to hide your names to protect them from being changed. I think you can only do this in code. Simply call the procedure and pass an argument of true to show or false to hide. This will keep the names invisible in the "Names" dialog and inaccessible to editing. To test rather the procedure is working or not. With the source workbook being the only open workbook, change some values which are linked to one or more of your nine workbooks. Run the procedure. Now close your source workbook. Open one or more or your dependent books and choose NOT to update links at the prompt. The linked values should reflect the changes you have made in your source workbook. The download example link at the bottom will allow you to see where the code is placed and what the names and paths look like. Obviously, this will not work without you changing the pathnames. To copy the code to your clipboard, click the link at the bottom of this page.

<pre>Option Explicit

<hr size=1 style="verticalAlign=top;">

Public Sub OpenUpdateSave()

On Error GoTo err_OpenUpdateSave

Dim objName As Name, strMsg As String

Application.ScreenUpdating = False

For Each objName In ActiveWorkbook.Names

If Left(objName.Name, 9) = "Dependent" Then

strMsg = strMsg & UpdateDependent(Evaluate(objName.RefersTo))

End If

Next

If Len(strMsg) <> 0 Then

MsgBox strMsg

End If

Application.ScreenUpdating = True

Set objName = Nothing

Exit Sub

err_OpenUpdateSave:

MsgBox "OpenUpdateSave" & Chr(13) & Err.Number & " - " & Err.Description

Set objName = Nothing

End Sub

<hr size=1 style="verticalAlign=top;">

Private Function UpdateDependent(strDependent As String) As String

On Error GoTo err_UpdateDependent

Dim objFSO As New FileSystemObject, objWB As Excel.Workbook

Dim strOpenBookName As String

If Not objFSO.FileExists(strDependent) Then

UpdateDependent = "Path does not exist: " & strDependent & Chr(13)

Exit Function

End If

Set objFSO = Nothing

strOpenBookName = Mid(strDependent, InStrRev(strDependent, "\") + 1)

On Error Resume Next

Set objWB = Application.Workbooks(strOpenBookName)

If Err.Number <> 0 Then

Set objWB = Application.Workbooks.Open(strDependent, True)

End If

On Error GoTo err_UpdateDependent

objWB.Close True

Set objWB = Nothing

Exit Function

err_UpdateDependent:

UpdateDependent = Err.Number & " - " & Err.desription & _

" pertaining to" & Chr(13) & " -" & strDependent

End Function

<hr size=1 style="verticalAlign=top;">

'Usage from some procedure

'Call ShowPrecendentNames(True)

'Call ShowPrecendentNames(False)

Private Sub ShowPrecendentNames(ShowName As Boolean)

Dim objName As Name

For Each objName In ActiveWorkbook.Names

If Left(objName.Name, 9) = "Dependent" Then

objName.Visible = ShowName

End If

Next

End Sub

<hr size=1 style="verticalAlign=top;">

</pre>

Google Search<text> - </text>Email TsTom<text> - </text>Where do I put this code?<text> - </text>Copy Code to your Clipboard<text> - </text>Download Example

<pre> If you need further information pertaining to the above code, click on the term in question.

Many, but not all, are links to MSDN. If the downloaded file does not behave correctly in your

browser, then right click the link, choose "Save target as" and download to your hard-drive.

Tom</pre>

Randal G · Mar 27, 2003

Tom,
O.K., I’ll try to keep this short…. I am obviously missing something ….

I created a source book with 3 dependent books (for testing purposes). Set everything up to look just like your file that I downloaded. (That was nice!) Created everything as prescribed. Copy and pasted your code into the module. Put a button on the source sheet (file saved) clicked the button and I got this error: Compile Error: “User-Defined Not Defined” and it points to:
------------------------------------
Private Function UpdateDependent(strDependent As String) As String

On Error GoTo err_UpdateDependent
Dim objFSO As New FileSystemObject, objWB As Excel.Workbook
------------------------------------
So after looking over everything quite meticulously, I cannot find one thing that looks different. Every .( ) , “” ….ect., All in place. Went through my Visual Basic book with no prevail.
I even exported the module from your file and imported it to mine. Still won’t work. So I trashed it and started over from scratch and again the results were the same.

So I took your file ( put it in its own directory and dependent workbooks) and change everything to point to the workbooks (each linked exactly as the others I did), placed a button on the source book and assigned it. It works beautifully! Absolutely fantastic.!…. :D So I proved it will work and I’m not completely ignorant... but I’ve still got to apply it to the real thing and I can’t seem to recreate it.

So I started over from scratch again. Recreated everything as prescribed. I still get the same error.

She don’t tick. What possibly could I be missing? Maybe a key entry at some point that impliments it? Obviously I'm just picking in the dark now. The operation seems simple enough! Have you any suggestions? Please....
Thank you very much,
Randal G

Legacy 98055 · Mar 27, 2003

Oops.

I forgot to tell you to set a reference to Microsoft Scripting Runtime. From your VBAIDE select Tools, References, Microsoft Scripting Runtime. Place a checkbox. Save your Source book. I do apologize...

Tom :D

Updating workbooks.....

Randal G

Board Regular

Excel Facts

Legacy 98055

Guest

Randal G

Board Regular

Guitarde

Board Regular

Randal G

Board Regular

Guitarde

Board Regular

Randal G

Board Regular

Legacy 98055

Guest

Randal G

Board Regular

Legacy 98055

Guest

Similar threads

Forum statistics

Share this page

Updating workbooks.....

Randal G

Board Regular

Excel Facts

Legacy 98055

Guest

Randal G

Board Regular

Guitarde

Board Regular

Randal G

Board Regular

Guitarde

Board Regular

Randal G

Board Regular

Legacy 98055

Guest

Randal G

Board Regular

Legacy 98055

Guest

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock