Save file to Network Drive

[santeria]

I'm just trying to Figure out what to change for a network file save.
Probably something amazingly basic...

However, I have...

Sub SAVECMSDAILYNETWORK()
'
' SAVECMSDAILYNETWORK Macro
' Macro recorded 2/24/2004 
'

'
    Windows("M:\REPORTS\02_FEBRUARY SUMMARY\CMS Daily.xls").Activate
    ChDir "E:\HR Outsourcing\Benefits\Reports"
    ActiveWorkbook.SaveAs Filename:= _
        "E:\HR Outsourcing\Benefits\Reports\CMS Daily2.xls", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
    ActiveWorkbook.Close
End Sub

And I need to know what to change to make the thing work.

Ta

 

[-sam]

Dies at same place.

Ta

Why not make the sheet you want to save "active" and use Activeworkbook.saveas instead of trying to do the saveas with the variable. And like Guitarde said, definately use the UNC name for the server ("\\server\volume\directory\"). That way if somebody else tries to use your macro with their drives mapped in a different configuration things will remain copacetic. It won't fix this problem, but it will make the macro more robust.

-sam

 

[santeria]

It is indeed most perspicacious that you should be concerned with the copacetic behaviour of my Macros and related work.
I appreciate your very prolix analysis of my indeed unfortunate macro with it's tort based logic.

I shall endeavour to extricate myself from a maladroit macro by your suggested edit.

Ta

 

[santeria]

Unfortunately your suggestion has met with a negative outcome.

Ta

 

[santeria]

It can't be the active workbook, really.
That is what leads to the file I am using as my control panel, to save the Raw Data file over the End result file.

The File save Macro I need has to refer to a separate file, then save that separate file as something with a slightly different name elsewhere.

That is indeed the point of the problem.

Ta

 

[-sam]

It can't be the active workbook, really.
That is what leads to the file I am using as my control panel, to save the Raw Data file over the End result file.

The File save Macro I need has to refer to a separate file, then save that separate file as something with a slightly different name elsewhere.

That is indeed the point of the problem.

Well you certainly could make it temporarily the activesheet to do the saveas. With screenupdating off you'd never even know the sheet was opened. Admittedly it's a bit of a hack, but it works.

In any event wouldn't the problem with the code snippet you posted yesterday be that you declared fname as a string? Strings don't support the saveas method. If you declare fname as a workbook then you can perform the saveas. The only thing I can't figure out is how to assign your first file to fname without opening up the first file.

-sam

 

[santeria]

And there-in lies the rub.

I am expecting to have to save a bunch of reports to network, and I really don't like the idea of having this Hunt and search thing every time I have to save the file to network.

My only alternative to a Excel Macro, is to map out the process in Macro Angel ( the Macro device for Windows as a whole).

But at this point in time , that is essentially the least of my worries.

But, that is also the key to why I am trying automate these processes... I am trying to focus on trend Analysis, and anomaly assessment, so these rinky dink things, will hopefully be fully automated as time progresses.

Not that I am looking forward to Trend Analysis... I dread that... the fun will be in setting it up... then the repetitive stuff will take care of itself.


Ta

The only thing I can't figure out is how to assign your first file to fname without opening up the first file.

-sam

 

[-sam]

I am expecting to have to save a bunch of reports to network, and I really don't like the idea of having this Hunt and search thing every time I have to save the file to network.

Well, this does work

Dim tsheet As Workbook
oldfile = "\\server\volume\directory\test.xls"
Set tsheet = Workbooks.Open(Filename:=oldfile)
finame = "\\server\volume\directory\test2.xls"
tsheet.SaveAs Filename:=finame
tsheet.close

but I don't really see the difference between that and this

oldfile = "\\server\volume\directory\test.xls"
Workbooks.Open(Filename:=oldfile)
finame = "\\server\volume\directory\test2.xls"
activeworkbook.SaveAs Filename:=finame
activeworkbook.close

-sam

 

[santeria]

Ta

I'll try that.

I'm reformatting some data right now.
Made easier by a simple discovery ... Thanks to Fairwinds.

My main bug-a-boo at the moment is my email macro.

That looks like taking some time, but has been radically transformed thanks to Nate, the Notes Genius.

If I could figure out an anomaly sort method for some of my reports, I'd be happier.

However, my quest at the moment is for a source of Trend Analysis Method.
Ranking numbers and such like over half hour periods, and day periods, weekly etc... then applying based on some observations of my own that will hopefully be clarified by some Anomaly research, and a Briefing from some CMS programmers.

What a set of tasks ahead

Time to get a PDA..

Personally I'd prefer an album by Iron Maiden at the moment.


Ta

 

[Smitty]

Personally I'd prefer an album by Iron Maiden at the moment

Got one in my truck - I'll have to Run to the Hills and get it!

Smitty

 

[santeria]

Okay, Your Code Works.

I inserted some comments to get rid of the messages I get.

Oddly Enough this fails on the final

End Sub.

Sub SAVECMSDAILYNETWORK()
  Dim tsheet As Workbook
With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
oldfile = "M:\REPORTS\02_FEBRUARY SUMMARY\CMS Daily.xls"
Set tsheet = Workbooks.Open(Filename:=oldfile)
finame = "E:\HR Outsourcing\Benefits\Reports\CMS Daily2.xls"
tsheet.SaveAs Filename:=finame
tsheet.Close
With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With

End Sub

Ta