Saving File To Shared Drive Goofy Problem

[balla506]

Hi,

I for the life of me cannot figure out why the 'path will not save the file but if I use the M: drive it works just fine. I have triple checked that M:/ = \\office\group11\. I have screwed with it for hours with no luck. I continue to get a Method 'SaveAs' of object'_Workbook' failed error on the asterisked path. Any advice would be helpful.

Sub Run()
Dim path As String
iyear = Year(Date)
d = DateAdd("m", -1, Now)
b = Month(d)
f = MonthName(b)
a = "\" & Format(d, "mm") & ". " & f & " " & iyear
'path = "\\office\group11\T & A\DS\RTF Report\Source Reports\" & iyear & a
Filename = "RTF" & " " & f & " " & iyear & ".xlsm"


path = "M:\T & A\DS\RTF Report\Source Reports\" & iyear & a


MsgBox path & "\" & Filename
ActiveWorkbook.SaveAs path & "\" & Filename, xlOpenXMLWorkbookMacroEnabled
MsgBox "File has been saved, prep email using CTL+R"

End Sub

 

[Andrew Poulsom]

If you save a workbook to that location does ActiveWorkbook.Path match your UNC path?

 

[balla506]

Activework.path is displaying as M:\T & A\DS\RTF Report\Source Reports\2016\02.February 2016. My M: Drive is mapped to \\office\group11.

 

[Andrew Poulsom]

What have you used to establish that M: is mapped to that UNC path?

 

[balla506]

I used this code directly from Microsoft's site.

' 32-bit Function version.   ' Enter this declaration on a single line.
   Declare Function WNetGetConnection32 Lib "MPR.DLL" Alias _
      "WNetGetConnectionA" (ByVal lpszLocalName As String, ByVal _
      lpszRemoteName As String, lSize As Long) As Long


   ' 32-bit declarations:
   Dim lpszRemoteName As String
   Dim lSize As Long


   ' Use for the return value of WNetGetConnection() API.
   Const NO_ERROR As Long = 0


   ' The size used for the string buffer. Adjust this if you
   ' need a larger buffer.
   Const lBUFFER_SIZE As Long = 255


   Sub GetNetPath()


      ' Prompt the user to type the mapped drive letter.
      DriveLetter = UCase(InputBox("Enter Drive Letter of Your Network" & _
         "Connection." & Chr(10) & "i.e. F (do not enter a colon)"))


      ' Add a colon to the drive letter entered.
      DriveLetter = DriveLetter & ":"


      ' Specifies the size in characters of the buffer.
      cbRemoteName = lBUFFER_SIZE


      ' Prepare a string variable by padding spaces.
      lpszRemoteName = lpszRemoteName & Space(lBUFFER_SIZE)


      ' Return the UNC path (\\Server\Share).
      lStatus& = WNetGetConnection32(DriveLetter, lpszRemoteName, _
         cbRemoteName)


      ' Verify that the WNetGetConnection() succeeded. WNetGetConnection()
      ' returns 0 (NO_ERROR) if it successfully retrieves the UNC path.
      If lStatus& = NO_ERROR Then


         ' Display the UNC path.
          MsgBox lpszRemoteName, vbInformation


      Else
         ' Unable to obtain the UNC path.
         MsgBox "Unable to obtain the UNC path.", vbInformation
      End If


   End Sub

 

[Andrew Poulsom]

Then I'm out of suggestions, sorry.

 

[balla506]

So I tried troubleshooting some more and found that the below code works when I use .xls but when I try saving as a .xlsx or .xlsm I trip the same error. I need to have as one of these two file types. Any idea as to why this error is popping only for these types?

Sub save()
Dim path As String
Dim filename As String
Dim d As String
Dim b As String
Dim f As String
Dim a As String
Dim iyear As String




iyear = Year(Date)
d = DateAdd("m", -1, Now)
b = Month(d)
f = MonthName(b)
a = Format(d, "mm") & ". " & f & " " & iyear
filename = "Retail Foreign Exchange Report test" & f & " " & iyear & ".xls"


   ActiveWorkbook.SaveAs filename:="somepath" & iyear & "\" & a & "\" & filename, _
   FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", _
   ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

 

[Andrew Poulsom]

The FileFormat should match the file extension. Why are you using xlOpenXMLWorkbookMacroEnabled for an xls extension? It should be xlExcel8. That said I don't know why you would get an error if you change the file extension to xlsm.