Saving workbook on shared drive that may have different letter assignments

Damian37

Active Member
Joined
Jun 9, 2014
Messages
301
Office Version
  1. 365

Hello,
I'm receiving a method 'SaveAs" of object' _workbook failed error when I attempt to save a newly created file on to
a shared drive. I have the correct Directory path, and I am able to access another file within that directory, but
when I attempt to save the file I just created I get that error, and the line that is bolded red is the line that is
highlighted when I click on Debug. Here's the code I'm working with.

Rich (BB code):
Sub CopyNeg()
    'Dim OldWb As String
    Dim strFullFileName As String
    Dim NewWb As Workbook
    Dim NewWs As Worksheet
    Dim CurWs As Worksheet
    
    'OldWb = objDrv.DriveLetter & ":\SC Support Desk\Negative Replenishment Reports\_CHARTER_REPLEN"
    strFullFileName = "\\cable.comcast.com\corp-DFS\CHQ-Shared\SC Support Desk\Negative Replenishment Reports\Negative Replenishment file_" _
    & Format(Date, "mm.dd.yyyy") & ".xlsx"
    'OldWb.Close
    
    Set CurWs = ActiveWorkbook.Worksheets("Replen Report")
    Set NewWb = Workbooks.Add
    Set NewWs = NewWb.Sheets(1)
    CurWs.Range("A:T").AutoFilter Field:=20, Criteria1:="<0"
    CurWs.AutoFilter.Range.EntireRow.Copy
    NewWs.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    NewWb.SaveAs Filename:=strFullFileName, FileFormat:=xlsx
    
    'OldWb.Close
        
    
End Sub
All help is greatly appreciated!
D
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Try changing this
Code:
NewWb.SaveAs Filename:=strFullFileName, FileFormat:=[COLOR=#ff0000]51[/COLOR]
 
Upvote 0
Try changing this
Code:
NewWb.SaveAs Filename:=strFullFileName, FileFormat:=[COLOR=#ff0000]51[/COLOR]

Thank you Fluff. I thought I tried that before, and was receiving a different error. Do you think you might be able to assist me on another post I've placed regarding too many attachments being placed into an email I've created using VBA?

D.
 
Upvote 0
You're welcome & thanks for the feedback.

As for emails, I won't be able to help as I don't use Outlook.
 
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top