Excel Macro to create new folder and save file

[Clanos4]

I want to create a macro that will create a new folder called "Fungicide Quotes" under my documents and will save the workbook using the cell reference d4:f4 for the file name, which are merged cells. I have tried the following but can't get it to work. Any help would be appreciated, Thanks

Sub Save_wrkbk()

Dim strFilename, strDirname, strPathname, strDefpath As String
On Error Resume Next
strDirname = "Fungicide Quotes"

strFilename = Range("d4:f4").Value
strDefpath = "C:\My Documents\"
If IsEmpty(Filename) Then Exit Sub

MkDir strDefpath & strDirname
strPathname = strDefpath & strDirname & "\" & strFilename

ActiveWorkbook.SaveAs Filename:=strPathname, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End sub

 

[Clanos4]

I have it working, thanks for all your help

 

[tom_nolan]

I have gone through this thread and it appears to be what I need. However, I am not able to get the macro to work.
Like Clanos4 said in the beginning, I am not getting an error.

Here is the Code I have, where did I go wrong?
<CODE>

Sub Save_wrkbk()
Dim strFilename, strDirname, strPathname, strDefpath As String
strDirname = "Test Plans"

strFilename = Range("A1").Value
strDefpath = CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & "\"
If IsEmpty(Filename) Then Exit Sub

MkDir strDefpath & strDirname
strPathname = strDefpath & strDirname & "\" & strFilename

ActiveWorkbook.SaveAs Filename:=strPathname, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

</CODE>

 

[Andrew Poulsom]

What's this?

If IsEmpty(Filename) Then Exit Sub

You haven't declared or assigned a value to Filename.

 

[tom_nolan]

I thought I did a few lines above with this line: strFilename = Range("A1").Value

I want the value of cell A1 to be the File Name.

 

[Andrew Poulsom]

Filename and strFilename are different variables. Try:

If IsEmpty(strFilename) Then Exit Sub

 

[tom_nolan]

Thank you so much. That worked great. The dir is being created if needed and the file is getting the correct name.

However, I am getting the following Compatibility Checker error.

(This is an image of the error - Online Storage and Social Utility | FreeDrive.com)

"Some cells or styles in this workbook contain formatting that is not supported by the selected file format. These formats will be converted to the closest format available."

I am ok with that, I just don't want my users to see this message every time they try to use the macro to save the file.

All of my users and I are using Excel 2007.

Any thoughts on how I can find which cells it is referring to so I can fix them?
Or is there a better way stop or get around this message?

Thanks,
Tom

 

[Andrew Poulsom]

Why don't you save the workbook as xlsx (or xlsm)?

 

[tom_nolan]

Makes sence, I found I did not specify the extention in the file name. I have corrected that. The file is XLSM.

But I am still the Compatibility Checker window, and now there is a new problem.

If I continue past the the message, make a change to the spread sheet and run the Macro again, I get a VB Run-time error '75': Path/File access error.

 

[Andrew Poulsom]

For xlsm you need FileFormat:=xlOpenXMLWorkbookMacroEnabled (or 52).

 

[tom_nolan]

That took care of the Compatibility Checker window, Thanks!!

I am still getting the VB Run-time error '75': Path/File access error. When I run Debug I am taken to the following line:
<code>MkDir strDefpath & strDirname</code>

I think I need an error check to see if the dir exsists and if it does to skip this line.
How do I address this?