1

If possible, I would like to add code that would check to see if there is a file in "Return notes" that has the same text value as B1 (which contains the filename to be)

Private Sub CommandButton1_Click()

Dim NameofWorkbook As String

NameofWorkbook = "RN" & Range("B1")

MyMsg = NameofWorkbook + " " & "saved to return note folder"

'Create and assign variables
Dim saveLocation As String
saveLocation = "S:\Office information\Returns\Return Notes\" + NameofWorkbook

'Save Active Sheet(s) as PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLocation

MsgBox MyMsg

End Sub

Working code (Thank you Romulax):

Private Sub CommandButton1_Click()

Dim NameofWorkbook As String
Dim levSave As String

NameofWorkbook = "RN" & Trim(Range("B1"))

MyMsg = NameofWorkbook + " " & "saved to return note folder"

'Create and assign variables
Dim saveLocation As String

saveLocation = "S:\Office information\Returns\Return Notes\" & NameofWorkbook
lenSave = saveLocation & ".pdf"

If Len(Dir(lenSave)) = 0 Then
    MsgBox "File does not exist"
    'Do stuff

'Save Active Sheet(s) as PDF
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
     Filename:=saveLocation

MsgBox MyMsg

Else

MsgBox "Filename taken."

End If
End Sub
itz_reecey
  • 17
  • 4

1 Answers1

1

See here.
In you case :

If Len(Dir(saveLocation)) = 0 Then
  MsgBox "File does not exist"
  'Do stuff
Else
    MsgBox "File does exist"
    'Do stuff
End If

Also replace this :

saveLocation = "S:\Office information\Returns\Return Notes\" + NameofWorkbook

With this :

saveLocation = "S:\Office information\Returns\Return Notes\" & NameofWorkbook

To concatenate, you must use & not +.

Edit: If the file you are looking for is a excel file ".xlsm", and if the ".xlsm" is NOT in Range("B1"), you have to include it within the code. That way, your savelocation is :

saveLocation = "S:\Office information\Returns\Return Notes\" & NameofWorkbook & ".xlsm"

Replace ".xlsm" with the actual file type

romulax14
  • 555
  • 2
  • 12
  • Maybe try `NameofWorkbook = "RN" & Trim(Range("B1")) `. `Trim` gets rid of unwanted spaces, except single space between words – romulax14 Aug 24 '20 at 11:32
  • Thanks again Romulax, still no luck unfortunately. I have gave the current version of my code in my original post – itz_reecey Aug 24 '20 at 11:32
  • Also are you sure that the file directory begins with "S"? Usually it begins with "C", if it isn't a secondary drive. Can you also explain what does the code to right now, does it show a msgbox or just crash somewhere? Or nothing? – romulax14 Aug 24 '20 at 11:33
  • Yeah it's definitely "S". I'm working on a project on a shared drive. I have still had no success. The code shows "File does not exist" and runs the remainder of the code (i.e. completes the save) – itz_reecey Aug 24 '20 at 11:35
  • Remember that you have to include ".xlsm" or ".xlsx" (or other file format, depending on what file you are looking for) at the end of your directory, if it isn't in `Range("B1")`. See edited answer, keep me updated – romulax14 Aug 24 '20 at 11:42
  • Thanks Romulax! All sorted now. I'll post my final code +1 – itz_reecey Aug 24 '20 at 11:47
  • I upvoted your post. However, as I have under 15 rep, it will not show the count – itz_reecey Aug 24 '20 at 11:50
  • I didn't mean upvoting, below upvote button there is a mark that you can check to accept the answer – romulax14 Aug 24 '20 at 11:51
  • My apologies, just realised. Done! – itz_reecey Aug 24 '20 at 12:47