Original Excel File gets deleted and temp file gets created.
 

Guys,

I have a major problem with an Excel workbook that i created and have
been try to resolve this issue for a couple of weeks and my Bosses are
breathing down my neck.

Here is how it works:
I have multiple users using the excel workbook at the same time (they
will open as read only). Once they are done with the data entry they
run a macro. Which will copy data from this excel workbook opens
another file, which is saved in the Shared Drive (Network Drive) and
pastes it as the last entry made in that workbook. If another user is
using this file then it throws an error boxing saying that, "The file
is locked by another user for editing and try after 10 seconds". Then
saves the workbook that it opened from the shared drive and closes it.

The problem is that the macro is running ok till the point of opening
the file making the entry but then when it tried to save the file it
creates a temp file in the same location instead and deletes the
original file. So when other users run the macro again the file does
not get located and they get Run Time error 1004.

This problem is only happening 5 to 10% of the times and usually occurs
when multiple users are trying to save the file at the same time.

I did read the previous threads, the suggestions i got from there we
It could be an Anti Virus issue. Norton is not scanning this folder.
The users have Read, Write as well as Delete access to this particular
folder.

All help will be greatly appreciated.

Thanks and Regards,
Sandy

Original Excel File gets deleted and temp file gets created.
 

You might try putting a check in the code to see if the file is open (in
use) before the save takes place. If it is open, you could prompt the user
to try again later (but prevent anything else from happening if it is opened
by someone else). Here is a link to get you started.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=625

"Sandy" wrote in message
oups.com...
Guys,

I have a major problem with an Excel workbook that i created and have
been try to resolve this issue for a couple of weeks and my Bosses are
breathing down my neck.

Here is how it works:
I have multiple users using the excel workbook at the same time (they
will open as read only). Once they are done with the data entry they
run a macro. Which will copy data from this excel workbook opens
another file, which is saved in the Shared Drive (Network Drive) and
pastes it as the last entry made in that workbook. If another user is
using this file then it throws an error boxing saying that, "The file
is locked by another user for editing and try after 10 seconds". Then
saves the workbook that it opened from the shared drive and closes it.

The problem is that the macro is running ok till the point of opening
the file making the entry but then when it tried to save the file it
creates a temp file in the same location instead and deletes the
original file. So when other users run the macro again the file does
not get located and they get Run Time error 1004.

This problem is only happening 5 to 10% of the times and usually occurs
when multiple users are trying to save the file at the same time.

I did read the previous threads, the suggestions i got from there we
It could be an Anti Virus issue. Norton is not scanning this folder.
The users have Read, Write as well as Delete access to this particular
folder.

All help will be greatly appreciated.

Thanks and Regards,
Sandy

Original Excel File gets deleted and temp file gets created.
 

Thanks Mark will try this today and get back to u.....

Regards,
Sandy

Mark Ivey wrote:
You might try putting a check in the code to see if the file is open (in
use) before the save takes place. If it is open, you could prompt the user
to try again later (but prevent anything else from happening if it is opened
by someone else). Here is a link to get you started.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=625

"Sandy" wrote in message
oups.com...
Guys,

I have a major problem with an Excel workbook that i created and have
been try to resolve this issue for a couple of weeks and my Bosses are
breathing down my neck.

Here is how it works:
I have multiple users using the excel workbook at the same time (they
will open as read only). Once they are done with the data entry they
run a macro. Which will copy data from this excel workbook opens
another file, which is saved in the Shared Drive (Network Drive) and
pastes it as the last entry made in that workbook. If another user is
using this file then it throws an error boxing saying that, "The file
is locked by another user for editing and try after 10 seconds". Then
saves the workbook that it opened from the shared drive and closes it.

The problem is that the macro is running ok till the point of opening
the file making the entry but then when it tried to save the file it
creates a temp file in the same location instead and deletes the
original file. So when other users run the macro again the file does
not get located and they get Run Time error 1004.

This problem is only happening 5 to 10% of the times and usually occurs
when multiple users are trying to save the file at the same time.

I did read the previous threads, the suggestions i got from there we
It could be an Anti Virus issue. Norton is not scanning this folder.
The users have Read, Write as well as Delete access to this particular
folder.

All help will be greatly appreciated.

Thanks and Regards,
Sandy

Original Excel File gets deleted and temp file gets created.
 

Think Mark didn't understand the question. The code he sent you to is
worthless for the situation you describe.


http://support.microsoft.com?kbid=138621
XL: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=291295
XL2002: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=213383
XL2000: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=184982
WD97: VBA Function to Check If File or Document Is Open

Will be more along what you want to do as I understand the situation.

--
Regards,
Tom Ogilvy


"Sandy" wrote in message
ups.com...
Thanks Mark will try this today and get back to u.....

Regards,
Sandy

Mark Ivey wrote:
You might try putting a check in the code to see if the file is open (in
use) before the save takes place. If it is open, you could prompt the
user
to try again later (but prevent anything else from happening if it is
opened
by someone else). Here is a link to get you started.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=625

"Sandy" wrote in message
oups.com...
Guys,

I have a major problem with an Excel workbook that i created and have
been try to resolve this issue for a couple of weeks and my Bosses are
breathing down my neck.

Here is how it works:
I have multiple users using the excel workbook at the same time (they
will open as read only). Once they are done with the data entry they
run a macro. Which will copy data from this excel workbook opens
another file, which is saved in the Shared Drive (Network Drive) and
pastes it as the last entry made in that workbook. If another user is
using this file then it throws an error boxing saying that, "The file
is locked by another user for editing and try after 10 seconds". Then
saves the workbook that it opened from the shared drive and closes it.

The problem is that the macro is running ok till the point of opening
the file making the entry but then when it tried to save the file it
creates a temp file in the same location instead and deletes the
original file. So when other users run the macro again the file does
not get located and they get Run Time error 1004.

This problem is only happening 5 to 10% of the times and usually occurs
when multiple users are trying to save the file at the same time.

I did read the previous threads, the suggestions i got from there we
It could be an Anti Virus issue. Norton is not scanning this folder.
The users have Read, Write as well as Delete access to this particular
folder.

All help will be greatly appreciated.

Thanks and Regards,
Sandy

Original Excel File gets deleted and temp file gets created.
 

Your absolutely right Tom...

I noticed the same thing after I had sent it. I actually sent the wrong
weblink. I had been referencing several before sending that post and
inserted the wrong link.

Thank you for supplying this information.

"Tom Ogilvy" wrote in message
...
Think Mark didn't understand the question. The code he sent you to is
worthless for the situation you describe.


http://support.microsoft.com?kbid=138621
XL: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=291295
XL2002: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=213383
XL2000: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=184982
WD97: VBA Function to Check If File or Document Is Open

Will be more along what you want to do as I understand the situation.

--
Regards,
Tom Ogilvy


"Sandy" wrote in message
ups.com...
Thanks Mark will try this today and get back to u.....

Regards,
Sandy

Mark Ivey wrote:
You might try putting a check in the code to see if the file is open (in
use) before the save takes place. If it is open, you could prompt the
user
to try again later (but prevent anything else from happening if it is
opened
by someone else). Here is a link to get you started.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=625

"Sandy" wrote in message
oups.com...
Guys,

I have a major problem with an Excel workbook that i created and have
been try to resolve this issue for a couple of weeks and my Bosses are
breathing down my neck.

Here is how it works:
I have multiple users using the excel workbook at the same time (they
will open as read only). Once they are done with the data entry they
run a macro. Which will copy data from this excel workbook opens
another file, which is saved in the Shared Drive (Network Drive) and
pastes it as the last entry made in that workbook. If another user is
using this file then it throws an error boxing saying that, "The file
is locked by another user for editing and try after 10 seconds". Then
saves the workbook that it opened from the shared drive and closes it.

The problem is that the macro is running ok till the point of opening
the file making the entry but then when it tried to save the file it
creates a temp file in the same location instead and deletes the
original file. So when other users run the macro again the file does
not get located and they get Run Time error 1004.

This problem is only happening 5 to 10% of the times and usually
occurs
when multiple users are trying to save the file at the same time.

I did read the previous threads, the suggestions i got from there
we
It could be an Anti Virus issue. Norton is not scanning this folder.
The users have Read, Write as well as Delete access to this particular
folder.

All help will be greatly appreciated.

Thanks and Regards,
Sandy

Original Excel File gets deleted and temp file gets created.
 

Hey,

Thank you so much Mark and Tom for your help me thus far.

But I am still facing the same problem

This is the code that I am using:

Sub Test_DB2()
If
IsFileOpen("\\6.193.246.215\Border\Database\CEWAcc essDB2MervynsMexico.xls")
Then
MsgBox "The Database File is currently locked for Editing by
another user."
Range("A1").Select
End
Else
Application.Run ("Export_Data_Completed")
End If
End Sub

Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer
On Error Resume Next
filenum = FreeFile()
Open filename For Input Lock Read As #filenum
Close filenum
errnum = Err
On Error GoTo 0
Select Case errnum
Case 0
IsFileOpen = False
Case 70
IsFileOpen = True
Case Else
Error errnum
End Select
End Function


Sub Export_Data_Completed()
Sheets("Decision").Select
Sheets("Database").Select
Range("Export_Database").Select
Selection.Copy
Workbooks.Open
filename:="\\6.193.246.215\Border\Database\CEWAcce ssDB2MervynsMexico.xls"
Range("A1").Select
Selection.End(xlDown).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Application.CutCopyMode = True
ActiveWorkbook.Save
ActiveWorkbook.Close
Sheets("Welcome").Select
End Sub

Original Excel File gets deleted and temp file gets created.
 

Guys,

Any thoughts here.Need help bad....

thxs.
Sandy

Original Excel File gets deleted and temp file gets created.
 

Sandy,

I have been looking over your macro...

A few questions...
1. What is the name of the workbook you want to tranfer data from?
2. What is the worksheet(s) name(s) you wish to transfer data from?
3. What is the path and worksheet name you wish to transfer data to?
4. Are there named ranges associated with any of this data?

If you can answer these questions, I will give it a go to see if I can help
you out.

Mark Ivey
--
Mark Ivey


"Sandy" wrote:

Guys,

Any thoughts here.Need help bad....

thxs.
Sandy

Original Excel File gets deleted and temp file gets created.
 

Sandy...

It might be even better if you can email me a copy of the files you are
working with.


Mark Ivey


"Sandy" wrote in message
oups.com...
Guys,

Any thoughts here.Need help bad....

thxs.
Sandy

Original Excel File gets deleted and temp file gets created.
 

hey Mark,

Sent you an email. Please do let me know what you think.

Thank you for helping out.

Regards,
Sandy

Original Excel File gets deleted and temp file gets created.
 

Sandy,

I think the problem is more of your network address convention. I tried it
out using my local drives and one network drive that I have mapped in with a
letter and had absolutely no problems. You might do well to re-ask your
question (with code) about the proper method to address the network drive
you are using. I have limited experience in that area, and it might take me
much longer to get you fixed up than the others that are always part of this
newsgroup.

Mark Ivey


"Sandy" wrote in message
ups.com...
hey Mark,

Sent you an email. Please do let me know what you think.

Thank you for helping out.

Regards,
Sandy

Original Excel File gets deleted and temp file gets created.
 

hey Guys,

Can anyone help me put here.. I am stuck big time.

Regards,
Sandy