Excel VB MkDir and File Save

**sdrane** · 04-24-2009, 03:13 AM

Hi

I'm looking for code that will allow me to use the contents of a cell say (K1) on a spreadsheet to specify a directory\path where I want to save a file. I need a macro that will check if the directory\path exists, if not then create a directory based on the path detailed in the cell K1 eg G:\2009\Offers

Then I want to save the file to that directory, with the file name being a concatenation of a series of cells within the sheet. This part I've got sorted.

However, I can not work out the code to check if the directory exists and then make the directory.

Note it is imperative that the directory path resides in a cell in the sheet as different users will have different drives/paths in which they will choose to save the file.

Please help
Steve

**Mallycat** · 04-24-2009, 03:48 AM

Sounds like you could use a challenge and some coaching rather than a solution. Read up in the VBA help on "dir". Basically you call this function and it will return a code that you can trap to determine if the directory exists. You can then branch your code to create the directory if it is not there, then save the file. Be careful as you will need to check each level of the folder structure. Post your progress

**JONvdHeyden** · 04-24-2009, 03:56 AM

Hi Steve

Have a look at GetSaveAsFilename in the help file (or show us your code so we can adapt). You could use this to collect a file path instead of having it keyed into a cell and then testing the keyed in value.

You can still specify the file name (even as a concatenation of other cells). It basically used the same SaveAs dialog box which will allow a user to trawl their drives and create new folders. Then it will return the full path which you can then use when you save the file.

HTH

**sdrane** · 04-25-2009, 03:22 AM

Hi

I have tried a couple of things but to be frank I'm self taught and usually keep trying until I get something that works.

Here is the code I'm using, the rem'd lines were the last attempt I tried, I think I'm nowhere near a result!!

Steve

' SaveOffer Macro
' Save Offer with Filename = "Model Variant Trans Material Code.xls" to Default Drive
'
' Keyboard Shortcut: Ctrl+Shift+X
'
' MyDir - InputBox("Enter Directory to Save , or OK to save to Directory", "MkDir", ActiveSheet.Range("K1"))
'
SavedName = InputBox("Enter Filename to Save , or OK to save as Worksheet", "Save Order", ActiveSheet.Range("A46"))
If SavedName <> "" Then ActiveWorkbook.SaveAs Filename:=SavedName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

End Sub