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

 

[santeria]

I found some code that seems to partially work:

Sub SAVECMSDAILYNETWORK()
Dim TBName$
    Application.ScreenUpdating = False
    TBName = ("M:\REPORTS\02_FEBRUARY SUMMARY\CMS Daily")
    ActiveWorkbook.SaveAs "E:\HR Outsourcing\Benefits\Reports\CMS Daily2.xls"
    Application.ScreenUpdating = True
    ActiveWorkbook.Close
End Sub

However, the End Result is not clear.
It actually closes my Control file, and not the file I am trying to deal with.

Is it actually saving the file I am driving the macro from and not the defined TB Workbook ?


Ta

 

[Smitty]

I think I see a potential problem:

Sub SAVECMSDAILYNETWORK() 
Dim TBName$ 
    Application.ScreenUpdating = False 
    TBName = ("M:\REPORTS\02_FEBRUARY SUMMARY\CMS Daily") 
    ActiveWorkbook.SaveAs "E:\HR Outsourcing\Benefits\Reports\CMS Daily2.xls" 
    Application.ScreenUpdating = True 
    ActiveWorkbook.Close 
End Sub

You Dim'd TBName$, but used TBName. What's the name of the workbook that you're macros are in? Is it CMSDaily? If so, this code should close CMSDaily2.

HTH,

Smitty

 

[santeria]

Okay, I'm a tad confused.

I need to reference the file another way ?

Not clear in the Dim bit, I was just copying some code I saw, them rem'd out the parts that errored.

All I am trying to do is, from my Raw Data file, perform an action of saving CMS Daily, as CMS Daily 2, in another Drive.

I have tried a few different ways.

The headache is, My whole focus is saving my CMS Daily file on my network turf, then copying the current data to a different location with a different ( slightly) name.


Ta

 

[santeria]

I don't know if this is a step closer, or a step further away... it still errors, I just don't know what to correct.

Sub SAVECMSDAILYNETWORK()
With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
Application.ScreenUpdating = False
    fName = ("M:\REPORTS\02_FEBRUARY SUMMARY\CMS Daily")
    fName.SaveAs "E:\HR Outsourcing\Benefits\Reports\CMS Daily2.xls"
    Application.ScreenUpdating = True
    ActiveWorkbook.Close
With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
End Sub

Ta

 

[Smitty]

Looks like you left out the End With's:

Sub SAVECMSDAILYNETWORK() 
  Dim fname As String
    With Application 
        .DisplayAlerts = False 
        .ScreenUpdating = False 
    End With
      fName = ("M:\REPORTS\02_FEBRUARY SUMMARY\CMS Daily") 
        fName.SaveAs "E:\HR Outsourcing\Benefits\Reports\CMS Daily2.xls" 
      ActiveWorkbook.Close 
    With Application 
        .DisplayAlerts = True 
        .ScreenUpdating = True 
    End With
End Sub

Hope that helps,

Smitty

 

[santeria]

It dies on the second fname reference.

It seems to be getting part of the way there.

Any suggestions, please ???


Ta

 

[Guitarde]

If you don't need the file to be opened, why not use copy ???

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> test()
    sName = "M:\REPORTS\02_FEBRUARY SUMMARY\CMS Daily"
    dName = "E:\HR Outsourcing\Benefits\Reports\CMS Daily2.xls"

    FileCopy Source:=sName, Destination:=dName
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

 

[santeria]

Good idea, but it fails on line five for some reason.


Ta

 

[-sam]

Good idea, but it fails on line five for some reason.


Ta

Don't know if this will help, but I have approximately a hogillion reports that save a copy of themselves out onto the network. goes a little something like this

filepath = "\\server\volume\data_warehouse\reports\" & "<filename>.xls" ' I use the UNC in case my drive mappings change
sheet1.copy ' this is so the users don't see the macros they don't need to see
activeworkbook.saveas filename:=filepath, fileformat:=xlnormal
activeworkbook.close

Easy as pie. Stupid question, the path you're trying to save to exists right?

-sam

 

[santeria]

Yup, the path exists.

Not sure if I interpreted you incorrectly, or what happened.

But the code you gave saves the file I am running the macro from, and not the file I want to manipulate.

Something is not entirely right.
But I do need a way to save this file as another file across networks, and also to not have the "such and such a file already exists, do you want to over-write".

Ta Anyway.