Run Time Error 5 - when saving to shared drive

Question

My code runs perfectly when I save to my local drive, but when I save to the shared drive I get Runtime Error 5? How is this occurring?

I have unmerged cells and put it as center across selection
Ensured that the whole document is within the print margins

Edit: I have tried saving into the folder directory above where I was saving and it works. I understand that there is a character limit (pathname and title), which might be the problem? Is there a way to solve this?

The error is in the following area:

'Creating Only the PDF based on Company Network - there is an existing folder
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=Fldr & "\" & Title & ".pdf", _
    Quality:=xlQualityStandard, _
    IgnorePrintAreas:=False

This is the whole code:

Option Explicit
Private Function selectfolder()

'Defining the Variables
Dim user_name As String 
user_name = Environ("username") 'to pick up the username from work environment

'Prompt for folder creation
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select the folder to save"
.InitialFileName = "C:\Users\" & user_name & "\Department\" 
.AllowMultiSelect = False
If .Show <> -1 Then Exit Function 'if user does not press OK, end the function'
selectfolder = .SelectedItems(1)

End With

End Function
Sub SaveActiveSheetAsPDF()

'Create a message box to ask user before proceeding
If MsgBox("This will print to PDFs. Continue?", vbYesNo + vbQuestion + vbDefaultButton2, "Printing to PDFs") = vbNo Then Exit Sub

'Defining the Type of Variables
Dim inputrange As Range 'Range represents a cell or multiple cells in Excel
Dim cell As Range
Dim network, Address, Fldr, Title As String

'If user does not choose a folder
Address = selectfolder
If Address = "" Then
    Exit Sub
End If

'Determine (set) where validation comes from - create a reference point
Set inputrange = Evaluate(Range("G2").Validation.Formula1)

For Each cell In inputrange

   Range("G2").Value = cell.Value

'Defining the Company Network Folder variables
network = Range("C6").Value
Fldr = Address & "\" & network
Title = "MonthlyReport (" & Format(Range("C8"), "mmmm") & ") - " & ActiveSheet.Range("B2").Value & " (" & ActiveSheet.Range("G2").Value & ")"

'Creating the folder based on Company Network - No existing folder
If Dir(Fldr, vbDirectory) = "" Then
'Create a folder
MkDir Fldr
'Save Active Sheet as PDF and to Company Network file
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=Fldr & "\" & Title & ".pdf", _
    Quality:=xlQualityStandard, _
    IgnorePrintAreas:=False

'Creating Only the PDF based on Company Network - there is an existing folder
Else
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=Fldr & "\" & Title & ".pdf", _
    Quality:=xlQualityStandard, _
    IgnorePrintAreas:=False

End If

Next cell
    
'Create a message box at end of task to inform user it is complete
MsgBox "Generation of PDF Reports and Folders Completed", vbInformation, "Complete"


End Sub

score 1 · Answer 1 · answered Aug 12 '20 at 04:41

1

It is difficult to diagnose problems with network drive without more information, but I could suggest a workaround instead.

You could save the file on your local drive and then move it using the File System Object in VBA. Here's how it would look like:

'Save Active Sheet as PDF in temporary folder
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=Environ("TEMP") & "\" & Title & ".pdf", _
    Quality:=xlQualityStandard, _
    IgnorePrintAreas:=False


'Move PDF to Company network drive
Dim fso As FileSystemObject
Set fso = New FileSystemObject
fso.MoveFile Environ("TEMP") & "\" & Title & ".pdf", Fldr & "\" & Title & ".pdf"

Note that for this code to work, you need a reference to the Microsoft Scripting Runtime Library.

answered Aug 12 '20 at 04:41

DecimalTurn

3,243
3
16
36

Yeah understood on the network drive issue. I am so confused, as when I did a test run of 20, it all worked fine, however, now expanding the list to the full 70, I get runtime error 5. It also does not affect everything, which means some can be saved and some cannot. I am doing a workaround by saving into local drive and then uploading – Onigiri Aug 12 '20 at 05:13
@Onigiri – Are you uploading the files manually? Because with my answer, you could at least automate this process. – DecimalTurn Aug 12 '20 at 05:34
I am currently trying different ways to troubleshoot. Learnt a new thing today from you though. Sorry, I am very new to VBA so everyday is a learning experience for me. – Onigiri Aug 12 '20 at 06:56

Run Time Error 5 - when saving to shared drive

1 Answers1