Good morning, all,

I posted a similar question a few days ago, but I think it might have got
lost, so I'll try again.
I have a number of workbooks that contain a macro to save to a network drive
and a macro to save to a local drive.

If I open a workbook and run its networksave macro, I get a runtime message
1004 "Cannot access file xxx.xls"

If however I open the workbook, first run its localsave macro (which runs
OK) and THEN run the networksave macro, then everything is OK.

I can then run the networksave macro as often as I want, but if I close the
file and open it again, the problem reoccurs.

I'm not going to post the code as, since it runs OK after I've done a local
save, I don't think there can be anything "wrong" with it. As the problem
occurs over a number of different workbooks, I don't think that it's a
workbook corruption issue, either. And nobody else has the workbooks open.

Can ANYONE help, please?

Yours in desperation

Pete



Can anyone sugge

I have a solution.

If I use a string to specify the folder like \\Servername\MyFolder\My
SubFolder etc i.e. one that begins with a double backslash, that's when the
problem occurs.
If the string begins with a drive letter, then no problem.
Not ideal, as not all my users will me mapped to the network drive in the
same way, but by picking up the foldername value from a cell on the worksheet
that they can alter, at least they can customize it and the value doesn't
need to be hardcoded.
I wonder why Excel doesn't like double backslashes?

Have a good weekend, all

Pete



"Peter Rooney" wrote:

Good morning, all,

I posted a similar question a few days ago, but I think it might have got
lost, so I'll try again.
I have a number of workbooks that contain a macro to save to a network drive
and a macro to save to a local drive.

If I open a workbook and run its networksave macro, I get a runtime message
1004 "Cannot access file xxx.xls"

If however I open the workbook, first run its localsave macro (which runs
OK) and THEN run the networksave macro, then everything is OK.

I can then run the networksave macro as often as I want, but if I close the
file and open it again, the problem reoccurs.

I'm not going to post the code as, since it runs OK after I've done a local
save, I don't think there can be anything "wrong" with it. As the problem
occurs over a number of different workbooks, I don't think that it's a
workbook corruption issue, either. And nobody else has the workbooks open.

Can ANYONE help, please?

Yours in desperation

Pete



Can anyone sugge

Do you think it could be a network problem?

Peter Rooney wrote:

Good morning, all,

I posted a similar question a few days ago, but I think it might have got
lost, so I'll try again.
I have a number of workbooks that contain a macro to save to a network drive
and a macro to save to a local drive.

If I open a workbook and run its networksave macro, I get a runtime message
1004 "Cannot access file xxx.xls"

If however I open the workbook, first run its localsave macro (which runs
OK) and THEN run the networksave macro, then everything is OK.

I can then run the networksave macro as often as I want, but if I close the
file and open it again, the problem reoccurs.

I'm not going to post the code as, since it runs OK after I've done a local
save, I don't think there can be anything "wrong" with it. As the problem
occurs over a number of different workbooks, I don't think that it's a
workbook corruption issue, either. And nobody else has the workbooks open.

Can ANYONE help, please?

Yours in desperation

Pete

Can anyone sugge

--

Dave Peterson

Why not post the code? There *may* be something "wrong" with it if it
does not work....
UNC paths have always worked fine for me in XL.

Tim


"Peter Rooney" wrote in
message ...
Good morning, all,

I posted a similar question a few days ago, but I think it might
have got
lost, so I'll try again.
I have a number of workbooks that contain a macro to save to a
network drive
and a macro to save to a local drive.

If I open a workbook and run its networksave macro, I get a runtime
message
1004 "Cannot access file xxx.xls"

If however I open the workbook, first run its localsave macro (which
runs
OK) and THEN run the networksave macro, then everything is OK.

I can then run the networksave macro as often as I want, but if I
close the
file and open it again, the problem reoccurs.

I'm not going to post the code as, since it runs OK after I've done
a local
save, I don't think there can be anything "wrong" with it. As the
problem
occurs over a number of different workbooks, I don't think that it's
a
workbook corruption issue, either. And nobody else has the workbooks
open.

Can ANYONE help, please?

Yours in desperation

Pete



Can anyone sugge

Dave,

Knowing our network, it's a distinct possibility..! :-)

However, I can't see why the code works once I've saved the file locally. I
get the problem the first time I open the workbook, whenever I open it. Even
WE can't have network problems that often!

I'm going to post the code under Tim Williams' response

Thanks for your interest.

Pete

"Dave Peterson" wrote:

Do you think it could be a network problem?

Peter Rooney wrote:

Good morning, all,

I posted a similar question a few days ago, but I think it might have got
lost, so I'll try again.
I have a number of workbooks that contain a macro to save to a network drive
and a macro to save to a local drive.

If I open a workbook and run its networksave macro, I get a runtime message
1004 "Cannot access file xxx.xls"

If however I open the workbook, first run its localsave macro (which runs
OK) and THEN run the networksave macro, then everything is OK.

I can then run the networksave macro as often as I want, but if I close the
file and open it again, the problem reoccurs.

I'm not going to post the code as, since it runs OK after I've done a local
save, I don't think there can be anything "wrong" with it. As the problem
occurs over a number of different workbooks, I don't think that it's a
workbook corruption issue, either. And nobody else has the workbooks open.

Can ANYONE help, please?

Yours in desperation

Pete

Can anyone sugge

--

Dave Peterson

Tim,

Here it is:

Sub SaveMe()
Application.DisplayAlerts = False
YearWeekTeamName
SetFolderName

If TeamName.Value = "All Teams" Then
SetFolderName
SaveString = Year.Value & "-" & Format(WeekNumber.Value, "00") & " "
& "Consolidation.xls"
ActiveWorkbook.SaveAs Filename:=FolderName & SaveString, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Else
SaveString = Year.Value & "-" & Format(WeekNumber.Value, "00") & " "
& TeamName.Value & ".xls"
ActiveWorkbook.SaveAs Filename:=FolderName & SaveString, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End If
End Sub

Sub YearWeekTeamName() 'defines cells containing entries to make up filename
Set WeekNumber = Sheets("Database").Range("WeekNumber")
Set Year = Sheets("Database").Range("Year")
Set TeamName = Sheets("Database").Range("TeamName")
Set TeamName = DBSheet.Range("TeamName")
end sub

Sub SetFolderName() 'cell containing the offending \\ prefixed foldername
FolderName = Sheets("Database").Range("FolderName").Value
End Sub

Hope this helps. At the moment, we're having to make sure that the cell
"FolderName" contains something prefixed with a drive letter, which is a bit
awkward, as not everyone maps o the folder in the same way.

Cheers

Pete



"Tim Williams" wrote:

Why not post the code? There *may* be something "wrong" with it if it
does not work....
UNC paths have always worked fine for me in XL.

Tim


"Peter Rooney" wrote in
message ...
Good morning, all,

I posted a similar question a few days ago, but I think it might
have got
lost, so I'll try again.
I have a number of workbooks that contain a macro to save to a
network drive
and a macro to save to a local drive.

If I open a workbook and run its networksave macro, I get a runtime
message
1004 "Cannot access file xxx.xls"

If however I open the workbook, first run its localsave macro (which
runs
OK) and THEN run the networksave macro, then everything is OK.

I can then run the networksave macro as often as I want, but if I
close the
file and open it again, the problem reoccurs.

I'm not going to post the code as, since it runs OK after I've done
a local
save, I don't think there can be anything "wrong" with it. As the
problem
occurs over a number of different workbooks, I don't think that it's
a
workbook corruption issue, either. And nobody else has the workbooks
open.

Can ANYONE help, please?

Yours in desperation

Pete



Can anyone sugge

Tim,

For your added information, within the If Else Statement, the first bit,
where the workbook name is a fixed string, works, but the second bit, where
the workbook name is built up from the contents of the cell "Teamname"
doesn't.

Hope this is of some use - sorry if this message has repeated, site crashed
whilst I was posting :-)

Pete

"Tim Williams" wrote:

Why not post the code? There *may* be something "wrong" with it if it
does not work....
UNC paths have always worked fine for me in XL.

Tim


"Peter Rooney" wrote in
message ...
Good morning, all,

I posted a similar question a few days ago, but I think it might
have got
lost, so I'll try again.
I have a number of workbooks that contain a macro to save to a
network drive
and a macro to save to a local drive.

If I open a workbook and run its networksave macro, I get a runtime
message
1004 "Cannot access file xxx.xls"

If however I open the workbook, first run its localsave macro (which
runs
OK) and THEN run the networksave macro, then everything is OK.

I can then run the networksave macro as often as I want, but if I
close the
file and open it again, the problem reoccurs.

I'm not going to post the code as, since it runs OK after I've done
a local
save, I don't think there can be anything "wrong" with it. As the
problem
occurs over a number of different workbooks, I don't think that it's
a
workbook corruption issue, either. And nobody else has the workbooks
open.

Can ANYONE help, please?

Yours in desperation

Pete



Can anyone sugge

Since the code works the second (and more) time through, I would bet it isn't
excel's fault.



Peter Rooney wrote:

Dave,

Knowing our network, it's a distinct possibility..! :-)

However, I can't see why the code works once I've saved the file locally. I
get the problem the first time I open the workbook, whenever I open it. Even
WE can't have network problems that often!

I'm going to post the code under Tim Williams' response

Thanks for your interest.

Pete

"Dave Peterson" wrote:

Do you think it could be a network problem?

Peter Rooney wrote:

Good morning, all,

I posted a similar question a few days ago, but I think it might have got
lost, so I'll try again.
I have a number of workbooks that contain a macro to save to a network drive
and a macro to save to a local drive.

If I open a workbook and run its networksave macro, I get a runtime message
1004 "Cannot access file xxx.xls"

If however I open the workbook, first run its localsave macro (which runs
OK) and THEN run the networksave macro, then everything is OK.

I can then run the networksave macro as often as I want, but if I close the
file and open it again, the problem reoccurs.

I'm not going to post the code as, since it runs OK after I've done a local
save, I don't think there can be anything "wrong" with it. As the problem
occurs over a number of different workbooks, I don't think that it's a
workbook corruption issue, either. And nobody else has the workbooks open.

Can ANYONE help, please?

Yours in desperation

Pete

Can anyone sugge

--

Dave Peterson


--

Dave Peterson

Peter,

Have you tried a debug.print on the generated filename? I bet that
would show something up....
You have some duplicated code which could be simplified a bit.

Sub SaveMe()
dim SaveString as string
Application.DisplayAlerts = False
YearWeekTeamName
SetFolderName

SaveString=Year.Value & "-" & Format(WeekNumber.Value, "00") & ""

If TeamName.Value = "All Teams" Then
SaveString = SaveString & "Consolidation.xls"
Else
SaveString = SaveString & TeamName.Value & ".xls"
End If

debug.print FolderName & SaveString

ActiveWorkbook.SaveAs Filename:=FolderName & SaveString, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

Hope that helps. Also check your "YearWeekTeamName" routine below: an
extra line in there....
Also may be time to turn on Option Explicit.

Tim.



"Peter Rooney" wrote in
message ...
Tim,

Here it is:

Sub SaveMe()
Application.DisplayAlerts = False
YearWeekTeamName
SetFolderName

If TeamName.Value = "All Teams" Then
SetFolderName
SaveString = Year.Value & "-" & Format(WeekNumber.Value,
"00") & " "
& "Consolidation.xls"
ActiveWorkbook.SaveAs Filename:=FolderName & SaveString, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False
Else
SaveString = Year.Value & "-" & Format(WeekNumber.Value,
"00") & " "
& TeamName.Value & ".xls"
ActiveWorkbook.SaveAs Filename:=FolderName & SaveString, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False
End If
End Sub

Sub YearWeekTeamName() 'defines cells containing entries to make up
filename
Set WeekNumber = Sheets("Database").Range("WeekNumber")
Set Year = Sheets("Database").Range("Year")
Set TeamName = Sheets("Database").Range("TeamName")
Set TeamName = DBSheet.Range("TeamName")
end sub

### You're setting TeamName twice - is this the same range ??? I'm
guessing that wasn't intended.





Sub SetFolderName() 'cell containing the offending \\ prefixed
foldername
FolderName = Sheets("Database").Range("FolderName").Value
End Sub

Hope this helps. At the moment, we're having to make sure that the
cell
"FolderName" contains something prefixed with a drive letter, which
is a bit
awkward, as not everyone maps o the folder in the same way.

Cheers

Pete



"Tim Williams" wrote:

Why not post the code? There *may* be something "wrong" with it if
it
does not work....
UNC paths have always worked fine for me in XL.

Tim

Tim,

Thanks for the tidyup advice.

The save still doesn't work though - the only way it will work is if I
replace the contents of the FolderName cell with something that begins with a
drive letter, OR if I first successfully save the file to a local drive (by
changing the contents of FolderName to D:\ for example), then chang
Foldername back to \\servername\ etc.

This is why I didn't post the code originally - once the workbook has saved
successfully, the code to save it to the network drive works OK.

Can you advise me as to how I should use debug.print as I haven't used it
before?

The problem seems to be a bit reminiscent of opening a file from a floppy,
changing the floppy and trying to save the file to the new floppy - you need
an intermittent successful save first before you can do it (showing my age
now!)

"Tim Williams" wrote:

Peter,

Have you tried a debug.print on the generated filename? I bet that
would show something up....
You have some duplicated code which could be simplified a bit.

Sub SaveMe()
dim SaveString as string
Application.DisplayAlerts = False
YearWeekTeamName
SetFolderName

SaveString=Year.Value & "-" & Format(WeekNumber.Value, "00") & ""

If TeamName.Value = "All Teams" Then
SaveString = SaveString & "Consolidation.xls"
Else
SaveString = SaveString & TeamName.Value & ".xls"
End If

debug.print FolderName & SaveString

ActiveWorkbook.SaveAs Filename:=FolderName & SaveString, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

Hope that helps. Also check your "YearWeekTeamName" routine below: an
extra line in there....
Also may be time to turn on Option Explicit.

Tim.



"Peter Rooney" wrote in
message ...
Tim,

Here it is:

Sub SaveMe()
Application.DisplayAlerts = False
YearWeekTeamName
SetFolderName

If TeamName.Value = "All Teams" Then
SetFolderName
SaveString = Year.Value & "-" & Format(WeekNumber.Value,
"00") & " "
& "Consolidation.xls"
ActiveWorkbook.SaveAs Filename:=FolderName & SaveString, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False
Else
SaveString = Year.Value & "-" & Format(WeekNumber.Value,
"00") & " "
& TeamName.Value & ".xls"
ActiveWorkbook.SaveAs Filename:=FolderName & SaveString, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False
End If
End Sub

Sub YearWeekTeamName() 'defines cells containing entries to make up
filename
Set WeekNumber = Sheets("Database").Range("WeekNumber")
Set Year = Sheets("Database").Range("Year")
Set TeamName = Sheets("Database").Range("TeamName")
Set TeamName = DBSheet.Range("TeamName")
end sub

### You're setting TeamName twice - is this the same range ??? I'm
guessing that wasn't intended.





Sub SetFolderName() 'cell containing the offending \\ prefixed
foldername
FolderName = Sheets("Database").Range("FolderName").Value
End Sub

Hope this helps. At the moment, we're having to make sure that the
cell
"FolderName" contains something prefixed with a drive letter, which
is a bit
awkward, as not everyone maps o the folder in the same way.

Cheers

Pete



"Tim Williams" wrote:

Why not post the code? There *may* be something "wrong" with it if
it
does not work....
UNC paths have always worked fine for me in XL.

Tim

Can you advise me as to how I should use debug.print as I haven't used it
before?

In the VB editor. open the "Immediate" window (Ctrl+G). Anything
printed using debug.print will appear there. You could try copying and
pasting the generated filepath and using it as a literal string in your
code.

Tim

Tim,

I gave it up as a bad job. I forced the code to save the workbook to the
local drive first, then save to it to the network drive (based on the
FolderName cell containing a value beginning with "\\") then delete the local
drive workbook.

Not ideal, as I don't like solving things unless I understand the answer,
and this solution hasn't solved the problem, just got around it, but there
you go.

Thanks for your help, and the note about debug.print.

Regards

Pete


"Tim Williams" wrote:

Can you advise me as to how I should use debug.print as I haven't used it
before?

In the VB editor. open the "Immediate" window (Ctrl+G). Anything
printed using debug.print will appear there. You could try copying and
pasting the generated filepath and using it as a literal string in your
code.

Tim