Solved My macro wont save file properly and is coming up read-only

December 17, 2012 at 09:43:02
Specs: Windows 7

My macro wont save properly and is coming up read-only. There is line in my macro thats saves the file. it is below:

Application.ThisWorkbook.Save

but the file is on a local drive and it wont save to different computers when i try to pull it up. Should i save as the file name and rewrite? Can a macro do this by else self? Because the least the people using this will have to do the better.. Im not ure need advice.

It is also coming up read-only i dont know if these two things are connected or not.


Thanks in advance


See More: My macro wont save file properly and is coming up read-only

Report •


✔ Best Answer
December 19, 2012 at 14:31:26

It's not the only option - it is a possible option in light of the fact that we don;y know how your network is set up.

If everyone can access X then create a directory in X for all the user files as described above, Each user will access their specific file (e.g. Joe would use Calculator_Joe) from X and save to X. Your consolidation spreadsheet would also reside on that x:Drectory. When you want to consolidate you will need to make sure no-one is using their individual file.

I suggest that you try the consolidation manually initially - go into each user file, copy the archive data and paste it into your consolidation spreadsheet. Once you are satisfied that all the users can run their files successfully over a period of time and you have done a few manual consolidations then we can talk about automating the consolidation.



#1
December 17, 2012 at 11:27:33

Try Application.ActiveWorkbook.Save.

Report •

#2
December 17, 2012 at 11:47:03

re "but the file is on a local drive and it wont save to different computers when i try to pull it up."

I don't understand this.

"it won't save to different computers when I pull it up"

Please explain.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
December 17, 2012 at 12:21:51

I agree with DerbyDad - we'll need to know more detail about the workflow involved.

Where does the file reside
How is it accessed
who accesses it
where do you want to save it

If, as I suspect, you are expecting multiple users to use the file at the same time then we're in a more complex arena. If this is the case click on Share Workbook in the Review ribbon and see how this works for you.


Report •

Related Solutions

#4
December 18, 2012 at 08:37:59

Yes multiple user will be using this macro. What i mean is this program is on a local drive where everybody can pull it up nd work with it. When i go to another computer to pull up the file the same way i pull it up by clicking the drive clicking the folder the program is in the clicking the program the updated version wont save throughout the drive. Does this help?????please let me know.

Report •

#5
December 18, 2012 at 08:44:19

Also I am unable to share the workbook because it will only come up Read-only and say its locked for editing by me.....

Report •

#6
December 18, 2012 at 12:04:51

Have you tried the Share Workbook option?

Even with that option I'm not sure you'll get what you want or need (apologies to the Rolling Stones).

If you think about it; what is Excel supposed to do when you have 2 or more users trying to change the same cell? Whose change should Excel accept? Even with the "Share Workbook" option this conflict would need to be resolved by a user.

You can see how complicated it could get. One way to get around this is to give each user their own copy of the spreadsheet and then have a consolidating spreadsheet that reads the Archive from all the user files.

This is just one alternative. Depending on your circumstances e.g. what the purpose of the spreadsheet is, how many users there are, what each user needs to see, who else would use the information etc. there may be other alternatives.


Report •

#7
December 18, 2012 at 12:16:50

Here's what's confusing to me...

You are using the terms macro, program, file, workbook, etc. in different sentences and I'm confused as to what they refer to.

Let's start with some definitions:

- A macro is a script written in VBA language and is usually stored within a workbook.
- A program is another name for an application, e.g. Excel is an application with which workbooks can be created.
- A file (according to some web definitions) is a block of arbitrary information which is available to a computer program and is usually stored on some kind of durable storage.
- A workbook is a specific type of computer file.

So when you say things like "multiple user will be using this macro. What I mean is this program is on a local drive..." it gets confusing because you are mixing up terms.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
December 18, 2012 at 13:04:42

Well you cant used the program without using the macro so those terms im using are the same. you can put "program everywhere i say macro" if that helps. Thanks, but like AlteK said above.
"You can see how complicated it could get. One way to get around this is to give each user their own copy of the spreadsheet and then have a consolidating spreadsheet that reads the Archive from all the user files. "

That would be what i needed. There will be 9 to 10 users in total possibly 3 or 4 using at the same time. So this makes sense. So I can give everybody there own copy through a flash drive then how would i consolidate all the archive data without checking every PC every month. Is that possible. If so, how would i go about that because that would do the trick


Report •

#9
December 18, 2012 at 13:39:53

This is where DerbyDad's request for clarification becomes more important. We need to understand your setup and I made certain assumptions where DerbyDad (quite rightly) wanted more specifics.

For example, I assumed where you said "this program is on a local drive where everybody can pull it up and work with it" you actually meant a "Shared Drive" on the company network (LAN). For example, everyone would have a C: drive or even a dedicated area on the LAN that is used for personal files and are not "share-able".

If my assumption is correct then I would have the different user files all in the same shared drive so that the consolidating spreadsheet can access all those files.

We can talk about how you would automate that consolidation after we understand precisely how and where files are stored and the frequency with which you want to consolidate.


Report •

#10
December 18, 2012 at 17:16:17

A "program" does not use a macro.

Do you mean that users can't use the workbook without using the macro?

You still haven't provided enough details for us to offer any suggestions. All you have provided is one line from a macro.

In fact, the first line of your original post doesn't make sense.

My macro won't save properly and is coming up read only.

Macros can't come up read only. A workbook could come up read only, but, as I said earlier, a macro is a script that's is stored in an Excel workbook. "Read only" is not a attribute of a macro, it is an attribute of a file, and that file could be a workbook.

My concern with multiple, individual workbooks is that they can't be easily consolidated if multiple users are making changes to the same cells at (more or less) the same time. For example, if User A opens his copy, changes A1 and saves his copy, then User B opens her copy, changes A1 and saves her copy, how do you "consolidate" those workbooks? As AlteK, said, this are just assumptions. We don't even know if what I just described in an issue or not.

You really need to tell us how your users use the workbook, how changes are made, what needs to be consolidated, etc. We can't help you unless you give us some specifics.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
December 18, 2012 at 17:49:45

Hi DerbyDad

I agree with what you say but since this is a continuation of a previous post
http://www.computing.net/answers/of...
we do have a bit more information with which to interpret BJELLS' request in this one.

Perhaps I should have stipulated this at the beginning.

So when BJELLS says "the macro won't save properly" we can infer from this that when the macro tries to save the workbook it does not behave as expected.

I also have the same concerns you do with multiple workbooks and is why I asked for more information about the LAN setup. In the end though I would probably recommend a process whereby all users are out of their individual files before attempting a consolidation. A macro can test for this (I think).


Report •

#12
December 19, 2012 at 04:24:02

Sub Archive()

Application.ScreenUpdating = False


'Calculator   Archive Col
'C10            C
'E10            B
'J10            D
'D14            E
'D18            A
'H27            F
'D27            G


Sheets("Calculator").Activate

LastRow = Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Row + 1 'Sets the last row in the Archives

'Copies the Calculator data to the Archive
        Sheets("Archive").Range("C" & LastRow) = Range("C10")
        Sheets("Archive").Range("B" & LastRow) = Range("E10")
        Sheets("Archive").Range("D" & LastRow) = Range("J10")
        Sheets("Archive").Range("E" & LastRow) = Range("D14")
        Sheets("Archive").Range("A" & LastRow) = Range("D18")
        Sheets("Archive").Range("F" & LastRow) = Range("H27")
        Sheets("Archive").Range("G" & LastRow) = Range("D27")
 
'Date and time of the archiving
        Sheets("Archive").Range("H" & LastRow) = Now
        

'Clears out the input data in the calculator but does not touch the calculated cells in the calculator
        
 
        Range("C10,E10,J10,D14,D18").ClearContents
   
'Sort on Column A

    ActiveWorkbook.Worksheets("Archive").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Archive").Sort.SortFields.Add Key:=Range("A2:A" & LastRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Archive").Sort
        .SetRange Range("A1:H" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Sheets("Archive").Activate
Range("A1").Select

End Sub

These are the changed users will be making...


"Yes multiple user will be using this workbook. What i mean is this workbook is on a local X drive where everybody can pull it up and work with it in the company. When i go to another computer to pull up the workbook the same way i pull it up by clicking the X drive clicking the folder the workbook is in, then clicking the workbook that was saved by running the macro, but the updated version wont save throughout the drive. Does this help?????please let me know." Does that make satisfy your confusion on my language???? its all about the workbook



Report •

#13
December 19, 2012 at 04:25:26

I forgot the application.Thisworkbook.Save

Report •

#14
December 19, 2012 at 09:08:55

Maybe it would help if I could save a and overwrite. What do you think???

Report •

#15
December 19, 2012 at 13:20:19

Sorry - still confused. Is the "X" drive accessible by everyone?

What about this. Give each user a copy of the file with a unique file name e.g.

Calculator_Joe
Calculator_Mary

Let them run it throughout the month and at the end of the month they can email you their files. You can then take the archive sheet from each of the files and append it to a separate worksheet.


Report •

#16
December 19, 2012 at 13:37:13

Well if thats my only option. Yes the X drive can be accessed by everyone. Thank though this was a tough one to explain

Report •

#17
December 19, 2012 at 14:31:26
✔ Best Answer

It's not the only option - it is a possible option in light of the fact that we don;y know how your network is set up.

If everyone can access X then create a directory in X for all the user files as described above, Each user will access their specific file (e.g. Joe would use Calculator_Joe) from X and save to X. Your consolidation spreadsheet would also reside on that x:Drectory. When you want to consolidate you will need to make sure no-one is using their individual file.

I suggest that you try the consolidation manually initially - go into each user file, copy the archive data and paste it into your consolidation spreadsheet. Once you are satisfied that all the users can run their files successfully over a period of time and you have done a few manual consolidations then we can talk about automating the consolidation.


Report •

#18
December 20, 2012 at 05:04:15

Sounds good, thanks

Report •


Ask Question