Good morning all,

I have a rather odd problem.

I have a workbook with a cell that contains a string referring to a
foldername.
It also has cells containg a team name, the year number and the week number.
I then have some code that stores the contents of these cells to variables
and finally concatenates the whole thing and saves the file thus:

FolderName = Sheets("Database").Range("FolderName").Formula
SaveString = Year.Value & "-" & Format(WeekNumber.Value, "00") & " " &
TeamName.Value & ".xls"
ActiveWorkbook.SaveAs Filename:=FolderName & SaveString, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

The problem is, that if the foldername cell is set to something local like
C:\ or D:\, the no problem. If it contains a network folder, then sometimes
it works and sometimes it doesn't - I get a "cannot access file..." message.

HOWEVER, if I save the workbook locally by changing the foldername field to
C:\ or D:\ first, then running the code, THEN change to the network folder,
it saves every time.

It's almost as though saving it successfully locally first allows it to save
to the network folder OK too.

Can anyone think of any reason as to why this might be happening?

Yours in bewilderment

Pete

When saving to the network I usually save to the network name instead of the
drive letter on each individual computer. i.e. istead of Z:\\folder
name\file name I use \\networkname\foldername\filename

That way there is a direct link at all times.

I don't know if this is what you were looking for. Do you have multiple
users that have to open the file or is this just on your computer?


"Peter Rooney" wrote:

Good morning all,

I have a rather odd problem.

I have a workbook with a cell that contains a string referring to a
foldername.
It also has cells containg a team name, the year number and the week number.
I then have some code that stores the contents of these cells to variables
and finally concatenates the whole thing and saves the file thus:

FolderName = Sheets("Database").Range("FolderName").Formula
SaveString = Year.Value & "-" & Format(WeekNumber.Value, "00") & " " &
TeamName.Value & ".xls"
ActiveWorkbook.SaveAs Filename:=FolderName & SaveString, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

The problem is, that if the foldername cell is set to something local like
C:\ or D:\, the no problem. If it contains a network folder, then sometimes
it works and sometimes it doesn't - I get a "cannot access file..." message.

HOWEVER, if I save the workbook locally by changing the foldername field to
C:\ or D:\ first, then running the code, THEN change to the network folder,
it saves every time.

It's almost as though saving it successfully locally first allows it to save
to the network folder OK too.

Can anyone think of any reason as to why this might be happening?

Yours in bewilderment

Pete

Hi, JNW,

Normally I wouldn't use drive letters either, but it seems to be the only
way in which I can avoid the problem the first time I run the "NetworkSave"
macro when I open the workbook!
The template is available for multiple users to create their own workbooks
(using the code that's causing the problem), but no two users should have any
one workbook open at the same time.

Regards

Pete



"JNW" wrote:

When saving to the network I usually save to the network name instead of the
drive letter on each individual computer. i.e. istead of Z:\\folder
name\file name I use \\networkname\foldername\filename

That way there is a direct link at all times.

I don't know if this is what you were looking for. Do you have multiple
users that have to open the file or is this just on your computer?


"Peter Rooney" wrote:

Good morning all,

I have a rather odd problem.

I have a workbook with a cell that contains a string referring to a
foldername.
It also has cells containg a team name, the year number and the week number.
I then have some code that stores the contents of these cells to variables
and finally concatenates the whole thing and saves the file thus:

FolderName = Sheets("Database").Range("FolderName").Formula
SaveString = Year.Value & "-" & Format(WeekNumber.Value, "00") & " " &
TeamName.Value & ".xls"
ActiveWorkbook.SaveAs Filename:=FolderName & SaveString, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

The problem is, that if the foldername cell is set to something local like
C:\ or D:\, the no problem. If it contains a network folder, then sometimes
it works and sometimes it doesn't - I get a "cannot access file..." message.

HOWEVER, if I save the workbook locally by changing the foldername field to
C:\ or D:\ first, then running the code, THEN change to the network folder,
it saves every time.

It's almost as though saving it successfully locally first allows it to save
to the network folder OK too.

Can anyone think of any reason as to why this might be happening?

Yours in bewilderment

Pete