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.
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")
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.