Excel VBA Can't save file name with variable holding date value

Question

I have some code that copys and pastes the current worksheet to a blank new workbook, and then saves it depending on the values of some cells (stored in variables).

Specifically, these are Site, Client, and Date visited.

It all works fine with site and client, however when I include the date variable in the filename to save, it throws an error: Runtime error 76 - Path not found.

I'd appreciate any help/advise.

Sub Pastefile()

Dim client As String
Dim site As String
Dim visitdate As String
client = Range("B3").Value
site = Range("B23").Value
screeningdate = Range("B7").Value

Dim SrceFile
Dim DestFile

SrceFile = "C:\2013 Recieved Schedules\schedule template.xlsx"
DestFile = "C:\2013 Recieved Schedules" & "\" & client & " " & site & " " & visitdate  & ".xlsx"

FileCopy SrceFile, DestFile

ActiveWindow.SmallScroll Down:=-12
Range("A1:I37").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-30
Workbooks.Open Filename:= _
    "C:\Schedules\2013 Recieved Schedules" & "\" & client & " " & site & " " &     visitdate & ".xlsx", UpdateLinks:= _
0
Range("A1:I37").PasteSpecial Paste:=xlPasteValues
Range("C6").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close

End Sub

If the date contains / they will be interpreted as part of a path. What does msgbox "C:\Schedules\2013 Recieved Schedules" & "\" & client & " " & site & " " & visitdate & ".xlsx" look like — Alex K.
– Alex K., Commented Mar 18, 2013 at 12:00
Ah you're right, thanks for the heads up. I've made sure these were formatted as eg "01 january 2013" though, assuming it wouldn't trip me up. How can I avoid this issue? Thanks. — user1571463
– user1571463, Commented Mar 18, 2013 at 12:07
Hi. It seems that, in fact, your code is defective. Do you see that you've mentioned screeningdate instead of he declared variable visitdate above? It would be good running your code using the Debug Step-By-Step (F8) instead of using F5 to check it thoroughly. Hope this tiny bit of idea helps you do better coding :-) — Octopus
– Octopus, Commented Mar 18, 2013 at 12:11
as your file count raises you will realize that its a bad approach to save your files with such extended names. i recommend saving it into folders with names i.e. clients, site, visit date. In a long run - this will be much easier for you to do any type of searching or/and analysis — user2140173
– user2140173, Commented Mar 18, 2013 at 12:16

GSerg · Accepted Answer · 2013-03-18 12:08:51Z

2

When using dates in file names, you never want to rely on the default textual representation of the date, because that depends on the current locale.

You should store the date as date in the first place, and explicitly format it in a safe way for the file name:

Dim visitdate As Date
visitdate = Range("b7").Value

dim visitdate_text as string
visitdate_text = Format$(visitdate, "yyyy\-mm\-dd")

You might also consider removing any special characters like \ from your other values, such as client and site. Otherwise the problem might arise again.

answered Mar 18, 2013 at 12:08

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1571463 Over a year ago

Thanks for your help guys, both issues have been rectified and now all works well.

Octopus · Accepted Answer · 2013-03-18 12:20:11Z

Here is my suggestion of code rewrite:

Sub Pastefile()

Dim client As String
Dim site As String
Dim visitdate As String
client = Range("B3").Value
site = Range("B23").Value
visitdate = Range("B7").Value

Dim SrceFile
Dim DestFile

If IsDate(visitdate) Then

SrceFile = "C:\2013 Received Schedules\schedule template.xlsx"
DestFile = "C:\2013 Received Schedules" & "\" & Trim(client) & " " & Trim(site) & " " & Str(Format(Now(), "yyyymmdd")) & ".xlsx"

If Trim(Dir("C:\2013 Received Schedules\schedule template.xlsx")) <> "" Then
FileCopy SrceFile, DestFile
Else
MsgBox (SrceFile & " is not available in the folder")
GoTo EndCode
End If

Range("A1:I37").Select
Selection.Copy

Workbooks.Open Filename:= _
"C:\Schedules\2013 Received Schedules" & "\" & client & " " & site & " " & visitdate & ".xlsx", UpdateLinks:= 0
Range("A1:I37").PasteSpecial Paste:=xlPasteValues
Range("C6").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close

Else
MsgBox ("Please input the correct date in cell B7")
ActiveSheet.Range("B7").Activate
End If
EndCode:
End Sub

Collectives™ on Stack Overflow

Excel VBA Can't save file name with variable holding date value

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related