Excel Network Problems

[ging18]

Hello All!

I have developed a large excel workbook in Excel 2000 on Windows 2000
that is heavily automated with VBA.

The workbook is used like an interface to view and modify a host of CSV
files. No actual data is stored within the workbook, just code to load
the files, manipulate them and the save the CSVs back to the network.
The excel file is stored on a network drive that is accessed by
different users on their machines

Part of the functionality of the workbook allows users to work remotely
from the network. Unfortunately the security implemented on our network
prevents wireless access so a piece of code copies all the required CSV
files from the network to the users local machine where they can then
unplug the network cable and work away from the network on the
machines. When the user has finished they reconnect their machine to
the network, click a button and the workbook copies the updated CSV
files back to the network drive.

As users needs have changed I have been adding extra features to the
workbook so that more functionality is available but I have not changed
the code used to control the disconnecting and reconnecting to the
network.

Now I find that when a user has reconnected to the network (I check
that a folder on the network exists to ensure the connection is active
before starting the operation) the errors below occur at different
points in the execution.

“Error Accessing File. Network Connection May have been lost”
“Could not find specified Object”

These cause the toolset to crash and the user either gets trapped in
the workbook or Excel freezes.

My initial thoughts are that the workbook may have grown too big for
excel to store in memory and the components are not loaded, but with
the network connection being present why is it not just loading the
components from the source?

I’m also finding that the original version is also starting to have
occurrences of the error.

I have found that different machines have different success levels with
the function, in some cases this workbook works fine and the error
passes to other workbooks that were open when disconnected to the
network

Does anybody have any ideas as to the likely cause and possible
solution to this problem?

Cheers
Christian.

 

[NickHK]

Not that I have that error but there are various causes which seem more tied
to VBA than Excel:
http://www.google.co.uk/search?hl=en&q=+"Error+Accessing+File.+Network+Connection"++Excel

Check the references of the WB both when on the network and when copied to
the laptops.

NickHK

 

[ging18]

Hello again,

I've checked the references and the reference set is the same when
connected to the network and when running from the laptop.

I have also found that if I complete an operation within the workbook
while connected and then disconnect from the network, the workbook
doesn't fall over if an operation is carried out while disconnected
from the network. I'm assuming that this is because all the necessary
modules have been properly loaded into memory? However, i find that
when I try and close the workbook after reconnecting to the network it
just hangs and has to be stopped with task manager to get Excel working
again.

I've now tried reorganising and further optimising the code but this
seems to just shift the problem to another part of the workbook. On
occasional attempts where it has worked, the problem gets passed to
other network based excel files that are open and they start throwing
the errors.

I'm getting to think it may be more a server/network problem, but how
do I go about proving this or solving the problem.

Cheers
Christian