PDA

View Full Version : Saving File Gives Error Excel VBA



Anne Troy
05-25-2015, 10:13 AM
If Len(Dir("Report " & Format(Date, "yyyymmdd"))) = 0 Then
MkDir "Report " & Format(Date, "yyyymmdd")
Else
MsgBox "You already ran today's reports and need to delete the folder before running them again."
Exit Sub


I just get a Path/File access error on the MkDir line of code instead of getting the message box.
I have tried moving the lines around, too. Anybody know what I'm doing wrong?

Kenneth Hobs
05-25-2015, 11:01 AM
I guess you could try checking for the folder rather than the file.

If Len(Dir("Report " & Format(Date, "yyyymmdd"),vbDirectory)) = 0 Then
MkDir "Report " & Format(Date, "yyyymmdd")
Else
MsgBox "You already ran today's reports and need to delete the folder before running them again."
Exit Sub

Yongle
05-25-2015, 11:16 AM
MKDir (which allows you to create a new folder), looks for something ressembling:
c:\Test\Excel

But your vba is generates
Report 20150525

So I think it is asking for a drive letter and a path

Something like

MkDir "C:\MyDocuments\Report " & Format(Date, "yyyymmdd")

or

Dim MyPath as string
MyPath = "C:\MyDocuments\"
MkDir MyPath & "Report " & Format(Date, "yyyymmdd")


or even

Dim MyPath as string
MyPath = "C:\MyDocuments\" & "Report " & Format(Date, "yyyymmdd")
MkDir MyPath

Anne Troy
05-25-2015, 11:50 AM
Kenneth: I just get errors an error on ELSE when I run yours.

Yongle: The drive and path should be the current drive and path.

snb
05-25-2015, 12:43 PM
What with:


Sub M_snb()
MsgBox CurDir & " Report " & Format(Date, "yyyymmdd")

MsgBox Dir("Report " & Format(Date, "yyyymmdd"), 16)

If Dir("Report " & Format(Date, "yyyymmdd"), 16) = "" Then MkDir "Report " & Format(Date, "yyyymmdd")


MsgBox Dir("Report " & Format(Date, "yyyymmdd"), 16)
End Sub

Yongle
05-25-2015, 12:56 PM
See what this throws up:

MsgBox Application.ActiveWorkbook.Path & "Report " & Format(Date, "yyyymmdd")

Yongle
05-25-2015, 01:28 PM
Have just tested and:

This line results in your error

MkDir "Report " & Format(Date, "yyyymmdd")

This line does not

MkDir "\Report " & Format(Date, "yyyymmdd")