Updating workbooks.....

[Randal G]

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

 

[Randal G]

THANK YOU!!!!! (I am yelling... with joy!)

Tom,
This is absolutely perfect. Exactly what I wanted. I'm just elated! Much,much graditute and appreciation!. This is just slicker than puppy poop!
Thankyou so much, :D
Randal G

 

[Legacy 98055]

puppy poop? I'll take that as a compliment.

 

[Randal G]

TsTom,
Finally got my workbook complete and placed it back on my shop server. I’m using your code in the source workbook which works absollutely perfect on a single machine (updates all files opened or closed)but once on the server it won’t udate if another machine has one of the dependent files open in the workbook. It thinks that open dependent file is “Read Only”. I’ve removed read only from the files attribtres and sharing is set to full access but it still won’t update any dependent workbooks that are open. It opens the dependent file on the machine with the opened source book as a "Read Only" file. Do you know of anyway around this?
Thanks again,
Randal g

 

[ecowar]

TsTom, ray:
I need to update a value contained in a second closed workbook.

Here's what I'm doing so far. I have a workbook that when opened, gets a value from a second workbook using an auto_open macro executed when the worksheet is opened. It opens the second file(harikari.xls), compares a value, and if the 2 values are equal it closes the second file and continues opening. If the two values aren't equal it calls an expire macro and causes the main worksheet to commit harikari. This part works great and keeps my users from using older versions

I am also getting a second value (REQNUM) from harikari to use as an autonumbering field in my main workbook. I know in the dictionary under redundant it says "See Redundant", and it would probably be easier to just use a formula or something in the main file, but with 30 to 40 users each having their own copy I want to be able to have a central reference file I can easily change.

When the main workbook is opened, after it checks its expiration status I want to pass it the REQNUM value from the harikari workbook. This much works. I want to increment the value and save it in harikari before it is closed so that the next time I open a new copy of the main workbook it starts with the next REQNUM. This is where my problem is. If my last REQNUM value was 144, when I open the main workbook it adds 1 to REQNUM and passes 145 to my autonumbering field. I then want it to save 145 as the new REQNUM value in harikari so that the next time I open my main workbook it passes 146 (145+1) and so on. Here is the macro I am using:

Sub Autpen()
'
' DEAD_SWITCH Macro If proper response not received, KABLAAMO!
' Macro recorded 12/26/2002 by BHOLMAN
' If proper comparison value is not received
' from HARIKARI file, This file will trigger
' code "11A 11A2B 1B2B3 000 DESTRUCT 0" sequence(EXPIRE macro).

Application.ScreenUpdating = False

A = ActiveWorkbook.Name

Workbooks.Open ("\\dsintserver4\v01\Printing Updates\HARIKARI.xls")
'Workbooks.Open ("a:\Printing Updates\HARIKARI.xls")
'Workbooks.Open ("c:\My Documents\Daticon Stuff\Printing Updates\HARIKARI.xls")
'Workbooks.Open ("D:\My Documents\Daticon Stuff\Printing Updates\HARIKARI.xls")
VAL1 = Sheets("Sheet1").Range("C3")
REQNUM = Sheets("Sheet1").Range("C6") + 1
Sheets("Sheet1").Cells(6,3) = REQNUM
ActiveWorkbook.Close savechanges = True, FileName:="\\dsintserver4\v01\harikari.xls"

Sheet1.Cells(1, 7) = REQNUM
If Sheets("Print Request").Range("A2") <> VAL1 Then
EXPIRE
End If

DisableCopyCutAndPaste

MsgBox "Please make sure you enter the server as: SERVER NAME \ VOLUME NAME" & vbCrLf _
& vbCrLf & "EXAMPLE: DSINTSERVER4\V01" & vbCrLf & vbCrLf _
& "EXAMPLE: SNOREDD02\FORMAT02" _
& vbCrLf & vbCrLf & "EXAMPLE: SNORFILE08\V04", vbOKOnly


End Sub

I have tried several different ways of setting the new value so that it is saved when it closes the harikari workbook before continuing, but the value never increments other than adding 1 to the same old value. I've tried it with the filename after the close command and without it, but it never saves the new value. Any ideas???