Export excel file as PDF into a targeted pc folder based on cell value

Ramadan2512

Board Regular
Joined
Sep 7, 2024
Messages
68
Office Version
  1. 2021
Platform
  1. Windows
I have a code in excel sheet to save the currant page as pdf into pc folder based on cell value + current month text and it's working perfectly with just one problem - when I have an old file already saved in the folder and I need to save a new one with the same month name, the new file override the old one and I lose it - for example a file saved as (AV 24A - P3) it will be saved in my folder as (AV 24A - P3 October) but if i have a new file with the same name (AV 24A - P3) and i need to save it now in the same month I will lose the old one because it will get the same name like (AV 24A - P3 October)
So, I need please to edit my code that if the file name is already found in the folder to add for example: AV 24A - P3 October (1), (2) etc.. to the new file name and not to override the old existing file - here is my code

VBA Code:
Sub ExportToPDF()
   
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
   
    Dim sName As String
   
    Select Case ws.Range("M7").Value
        Case 1: sName = "NH1 (District One)\Guards"
        Case 2: sName = "NH2 (Carnell Park)\Guards"
        Case 3: sName = "NH3 (Ivory Hills)\Guards"
        Case 4: sName = "NH4 (Westridge)\Guards"
        Case 5: sName = "NH5 (Gold Cliff)\Guards"
        Case 6: sName = "NH6 (Kingsrange)\Guards"
        Case 7: sName = "NH7 (Amberville)\Guards"
        Case 8: sName = "NH8 (Kingsrange PH2)\Guards"
        Case Else:
            MsgBox "Enter a number from 1 to 8 in cell 'M7'!", vbExclamation
            Exit Sub
    End Select
   
    Dim FilePath As String:
    FilePath = "D:\NEWGIZA\" & sName & "\" & ws.Range("M5").Value & " " _
        & Application.Text(Date, "[$-401]mmmm") & ".pdf"
 
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FilePath, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub
 
Last edited by a moderator:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Try this:

VBA Code:
Sub ExportToPDF()
  Dim ws As Worksheet
  Dim sName As String, FilePath As String, NewName As String, sExt As String
  Dim n As Long
  
  Set ws = ActiveSheet ' improve!
  Select Case ws.Range("M7").Value
    Case 1: sName = "NH1 (District One)\Guards"
    Case 2: sName = "NH2 (Carnell Park)\Guards"
    Case 3: sName = "NH3 (Ivory Hills)\Guards"
    Case 4: sName = "NH4 (Westridge)\Guards"
    Case 5: sName = "NH5 (Gold Cliff)\Guards"
    Case 6: sName = "NH6 (Kingsrange)\Guards"
    Case 7: sName = "NH7 (Amberville)\Guards"
    Case 8: sName = "NH8 (Kingsrange PH2)\Guards"
    Case Else:
      MsgBox "Enter a number from 1 to 8 in cell 'M7'!", vbExclamation
      Exit Sub
  End Select
  
  FilePath = "D:\NEWGIZA\" & sName & "\" & ws.Range("M5").Value & " " _
    & Application.Text(Date, "[$-401]mmmm")
  sExt = ".pdf"
  NewName = FilePath & sExt
  
  Do While True
    If Dir(NewName) = "" Then Exit Do
    n = n + 1
    NewName = FilePath & " (" & n & ")" & sExt
  Loop
  
  ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewName, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

🤗
 
Upvote 0
Solution
Try this:

VBA Code:
Sub ExportToPDF()
  Dim ws As Worksheet
  Dim sName As String, FilePath As String, NewName As String, sExt As String
  Dim n As Long
 
  Set ws = ActiveSheet ' improve!
  Select Case ws.Range("M7").Value
    Case 1: sName = "NH1 (District One)\Guards"
    Case 2: sName = "NH2 (Carnell Park)\Guards"
    Case 3: sName = "NH3 (Ivory Hills)\Guards"
    Case 4: sName = "NH4 (Westridge)\Guards"
    Case 5: sName = "NH5 (Gold Cliff)\Guards"
    Case 6: sName = "NH6 (Kingsrange)\Guards"
    Case 7: sName = "NH7 (Amberville)\Guards"
    Case 8: sName = "NH8 (Kingsrange PH2)\Guards"
    Case Else:
      MsgBox "Enter a number from 1 to 8 in cell 'M7'!", vbExclamation
      Exit Sub
  End Select
 
  FilePath = "D:\NEWGIZA\" & sName & "\" & ws.Range("M5").Value & " " _
    & Application.Text(Date, "[$-401]mmmm")
  sExt = ".pdf"
  NewName = FilePath & sExt
 
  Do While True
    If Dir(NewName) = "" Then Exit Do
    n = n + 1
    NewName = FilePath & " (" & n & ")" & sExt
  Loop
 
  ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewName, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

🤗
Woww that's great, you are much professional my friend - it works perfectly - I do appreciate your help - thanks a million
 
Upvote 1

Forum statistics

Threads
1,224,462
Messages
6,178,814
Members
452,880
Latest member
kares

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