G
Guest
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
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