Running Code from Shared Workbook

[SpaaamCatcher]

Hi, there. I need to find a way to run code from a shared workbook whereby one of the spreadsheets is saved off in text format. We will be doing parallel integrity testing for a new system for several months and I'm trying to make it easier by programmatically generating a text file from an excel spreadsheet which is shared. My understanding is that you may run code from a shared spreadsheet, but some options are disallowed. I did not believe 'SaveAs' to be one of them, but...

I realize that I can grant exclusive access at the time in order to do this, but then the workbook gets saved as unshared, which is a problem.

Any ideas why this code won't work while the workbook is shared, or a way around it? Help!

RUN-TIME ERROR '1004'; Method 'SaveAs' of object '_Workbook' failed

I do not have the problem when the workbook is not shared, and since I can't step through the code (in shared mode) to determine exactly where it's failing, I am assuming it is here when I try to output the file:

OutputFileName = "253DLAB" & DateFormat & ".pay"
CompleteFile = Directory & "\" & OutputFileName
Worksheets("PAY_OutputFile&quot.Activate
ActiveWorkbook.SaveAs FileName:=CompleteFile, _
FileFormat:=xlTextPrinter

Any ideas? Want to see more?

 

[tbl]

You certainly can run SaveAS from a shared book. I have several shared Excel files that do just that.The things that don't work are security,(anything to do with protection), and formatting. File saving should work, it's just a problem finding out where your code crashes.
Are you just saving one sheet of a book as text. Can you give me more details of what you are doing ?

Richard

 

[SpaaamCatcher]

Hi, Richard. Thanks for your response; sorry for the delay in mine.

It is difficult to identify exactly where the code is crashing, since I can't step through it when it is in shared mode and I don't have the problem when it's not. There are five sheets in the workbook, three of which are protected and the other two are hidden.

I am essentially programmatically populating the hidden sheets then saving them off as text files. The reason I thought the saveas function was causing the crash is because of the error - do you think unprotecting those protected sheets will eradicate the problem or do you think it's because they are hidden?

Let me know if there is more info I can provide to help solve the mystery...

Thanks for your time!

Jamie

 

[SpaaamCatcher]

Hi, there. Okay, I did a little testing. I made visible the two that were hidden and removed the protection from all of the worksheets so there shouldn't be any problem with security, but I still get the message: RUN-TIME ERROR '1004'; Method 'SaveAs' of object '_Workbook' failed.

This works perfectly when it's not shared and although I have found a workaround to the problem, it is not pretty or elegant and I would like to find a better way.

Let me know if you'd like to see more of the code...

jem

 

[VBAjedi]

Two observations:

You can just put a "Stop" line in your code before the line you suspect is the problem. Your code should break at the stop and take you into the VBA editor, where you can inspect variable contents, etc by mousing over them in the code. Which brings me to my second observation.

"_Workbook" doesn't look like a valid Workbook object name. . . you should find out what your CompleteFile variable contains just before the SaveAs (either by looking at in in break mode, or by using Debug.Print to send it to the Immediate window). Might not be the problem, but worth checking.

VBAjedi

 

[SpaaamCatcher]

Thanks for your response (and your sig-brought me a smile). Although I can't use the 'stop' method you were talking about (since in shared mode the VBA editor will not open under any circumstances), I will step through (unshared) to take a look at the value of the CompleteFile variable. However, since it works perfectly when it's not shared, I suspect the file name may not be the problem. Anyone??

 

[tbl]

Hi there,

sorry for the delay but its holiday time in Belgium !
I suggest that you verify that it is the save function that is causing you problems, (which doesn't surprise me), by just commenting it out and seeing if your code runs. If so we can take this further.

Richard

 

[SpaaamCatcher]

Hi, Richard. Holiday time in Belgium? I'll come on over and join you...

I remarked out:

ActiveWorkbook.SaveAs FileName:=CompleteFile, _
FileFormat:=xlTextPrinter

and, although it doesn't generate the text files I need, everything runs through fine. Help!

Jamie

 

[VBAjedi]

Jamie,

Got your post in your other thread requesting help on this one. I avoid Shared Workbooks like the plague. . . they cause all kinds of grief for VBA code (as you are discovering!).

Since nothing immediately comes to mind to try, let me take a different approach. Can you briefly describe why you NEED this workbook to be shared, and why you need to do a "Save As"? Perhaps there's an alternate approach we can suggest (like creating a new workbook and copying the relevant information to it, or?) . . .

VBAjedi

 

[SpaaamCatcher]

Sure! Thanks for your help. We're implementing a data warehouse to better handle this data, but in the interim (and during testing)...

We need to allow multiple departments' managers enter their statistics once a day. For this reason it needs to be shared.

I then programmatically manipulate this data, populate two hidden spreadsheets and save them off as text files. It is lovely and works perfectly until I share it. Then it fails at the saveas, which seems SO STRANGE! Thanks, Mr. Gates ;-)

Do you think there is a way to get around this problem, or will I need to come up with a completely different process?

There are some requirements with the output file, like field lengths, etc., that I had taken care of with this process. My fear is that starting something new at this point will take me back a step...

Your help is oh-so-greatly appreciated.

Jamie

 

[VBAjedi]

Ok, just brainstorming here. . .

If your file is doing what you want, with the exception of the "Save As" issue, what about this: add a "Submit" button or menu item to your workbook. Set this to trigger code that checks to see if that days two text files already exist in their designated network folder (this will obviously require that you have a standard text file naming convention that incorporates the date, but you probably already have that in your code). If one of the files does not exist, your code creates it and inserts that days values. If it does exist, your code opens the file for output, and writes that days' values (no headers or other garbage, just data) to the end of the file.

Now, you distribute your workbook to your managers, not as a shared network file, but as a copy they place on their desktop. Whoever gets around to doing their daily stats first will click the "Submit" button, which will trigger the process that creates the two files on the network. The rest of the managers data just gets added to the end of the text files when they click "Submit".

If it's a problem to have your text files' data be in the order it was submitted, you could modify this approach to have all the managers files write to a central workbook instead (again, not shared - what are the odds two managers hit the "Submit" button within 5 seconds of each other?). Then you can process that central workbook as desired before you create the text files from there.

Think either of those approaches could work for you?


VBAjedi

 

[SpaaamCatcher]

Thanks for your response. There is only one person who generates the file, once all the managers have entered their data (by noon), so I think your original idea of copying the data into a new workbook and saving it off may be the cleanest solution.

Thanks for sticking with me on this!

Jamie

 

[VBAjedi]

Right-o! I know the way you had it set up there was only one person who generated the file. I was just saying that unless the generation process requires manual guidance, it wouldn't be that hard to make the file be generated in "chunks" by the managers collectively. Anyway, sounds like you have an action plan.

Give a holler if you get stuck with that. . .

VBAjedi