Saving Questions

[seahawk56]

I have come across a few problems and was hopping I could save my self a great deal of time trying to figure these out by throwing them up on the board. I appreciate any and all help. If you only know one that's better then I'm at now.
1. I have created a production report workbook that has a macro that goes into staffs production workbooks and copies and pastes their production for the day. A friend of mine suggested making their sheets a shared file allowing me to run the report even if they are still in their workbooks. The only problem we have experience is that they can not save their workbook if I still have the production report workbook open. Does anyone know of a code that I can attach at the end of the macro to sever the connection to their production workbooks? Or any other suggestions on how I might go around this issue.
2. I am not positive about this but I believe when the workbook auto saves it saves it to a different folder. Is there a way to have a workbook auto save to it's original file and folder? As well as how do you set it to different times?
3. Kind of a saving question but more of a bonus for me. I believe I have read some where that you can program a workbook to run a macro/report at a set time. Is that something that is hard to set up? Is it something that is reliable and do you have to have the workbook open to have it run?
Thanks,
Seahawk56

 

[GlennUK]

1. I don't see how having workbooks shared would prevent anyone from saving their workbooks. Please explain the setup exactly, and what actions are taken in what order.

2. Please explain what you mean by autosave? What version of Excel are you using? The AutoSave dialog in Excel 97 and 2000 allows you to set how often saving is to take place. This step-by-step article explains how to use the AutoSave feature in Microsoft Excel 97, in Microsoft Excel 2000, and in Microsoft Excel 98 Macintosh Edition.
http://support.microsoft.com/kb/213943

3. Yes, you can set a macro to run at a set time ( see Application.OnTime Method ). And yes, you have to have the workbook open to have it run.

 

[seahawk56]

Thank you very much for replying I do greatly appreciate it. Please let me know if the below information helps.

1) My staff will fill out their production on their own production workbook. I then have a production report workbook that when I run a macro it will go out (G:drive) and copy and paste their production in my production report workbook. If after I run the macro I do not close the workbook and my staff tries to save and update their production workbook they receive this edit message (The file is Locked. Try the command again later.) As soon as I close out of the workbook they can then save and update. So I'm wondering if there is some coding I can add to the end of my macro that will sever any connection that is locking their workbook.

2) I'm currently using the 2003 version. I have not had a chance to see if this will work for the 2003 version. Do you know if it is the same?

Thanks again,
Seahawk56

 

[GlennUK]

1) Are you opening their individual workbooks as Read-Only or Read-Write? Are these workbooks existing in "Shared" mode? After your macro has run, which workbook do you not close ... your own or the staff's individual workbook?

2) You asked about where autosave saves and how to set the times of autosave. Autosave does not exist for Excel 2003. In versions of Excel of 2003 and higher there is a feature named Autorecovery ... see the Excel Help here:

Change the save interval for automatic file recovery

On the Tools menu, click Options, and then click the Save tab.
Select the Save AutoRecover info every check box.
In the minutes box, specify how often you want your Microsoft Office program to save files.
Note Do not use AutoRecover as a substitute for regularly saving your work by clicking Save on the Standard toolbar

 

[seahawk56]

Hi GlennUK,
I first want to say thank you for responding and that I'm sorry I have not responded back in a timely manner. I really appreciate your help I have just been very busy.
1) Their workbooks are existing in "Shared" mode so I believe the macro is opening their workbooks in Read -Write. I believe they receive an edit message that says "The File is Locked. Try the command again later."
The Macro will close out of every person's Production Tracking Sheet. If I do not close out of the Production Report workbook that it locks it up. Which is the workbook that I run the Macro from. I have pasted below a section of the macro that does the copy and paste for one person.
2) On the AutoRecover piece, there is a pathway for location. If I set that up to the pathway where the original folder is located will that save the fill on top of it's self? I would like to set it up so they can update their production tracking sheets and not have to worry about clicking save.

Thanks again for all your help!
Seahawk56

Proc 1
    With ActiveSheet.QueryTables.Add(Connection:=Array( _
        "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=""G:\ROC-CLAIMS\Clms Proc-Model Line\" & MyDept & " Production Tracki" _
        , _
        "ng\" & MyProc1 & "\" & Proddate & """;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet" _
        , _
        " OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Glob" _
        , _
        "al Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=F" _
        , _
        "alse;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=Fals" _
        , "e;Jet OLEDB:SFP=False"), Destination:=Range("A1:A75"))
        .CommandType = xlCmdTable
        .CommandText = Array("" & MyProc1 & "$")
        .Name = "" & Proddate & ""
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = _
        "G:\ROC-CLAIMS\Clms Proc-Model Line\" & MyDept & " Production Tracking\" & MyProc1 & "\" & Proddate & ""
        .Refresh BackgroundQuery:=False