creating a folder and saving in it with a filename taken from cell

D

ditto

New Member
Joined
Mar 21, 2009
Messages
20
I would like excel to create the folder C:/Bill if it doesn't already exist when I click save/save as option and save the workbook in it with a filename that is a combination of text/values in cells A1 and A2 on Sheet1. Better if the save as dialog box appears with this option selected so that I have to just click Save in the save as dialog box to save it in "C:/Bill". Please help. <!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->
<!-- controls -->
progress.gif
 
Sort by date Sort by votes
Hello and welcome to MrExcel.

Try

Code: 
Function dirExists(dirAndPath) As Boolean
Dim tempVar
On Error Resume Next
tempVar = Dir(dirAndPath & "\*.*", vbDirectory)
If Err = 0 And tempVar <> "" Then
    dirExists = True
End If
On Error GoTo 0
End Function

Sub SaveMe()
Dim fname As String
If Not dirExists("C:\Bill") Then MkDir "C:\Bill"
fname = Sheets("Sheet1").Range("A1").Value & Sheets("Sheet1").Range("A2").Value & ".xls"
ThisWorkbook.SaveAs Filename:="C:\Bill\" & fname
End Sub
 
Upvote 0
I am using excel2007.
How do I make this code run when I click save.
Where should I put the code?
I am new to vba that's why.
 
Upvote 0
Click the Office button (top left), click Excel Options, tick Show Developer tab in the Ribbon and click OK.

Press ALT + F11 to open the Visual Basic Editor, Insert > Module then paste the following modified code into the white space on the right:

Rich (BB code): 
Function dirExists(dirAndPath) As Boolean
Dim tempVar
On Error Resume Next
tempVar = Dir(dirAndPath & "\*.*", vbDirectory)
If Err = 0 And tempVar <> "" Then
    dirExists = True
End If
On Error GoTo 0
End Function

Sub SaveMe()
Dim fname As String
If Not dirExists("C:\Bill") Then MkDir "C:\Bill"
fname = Sheets("Sheet1").Range("A1").Value & Sheets("Sheet1").Range("A2").Value & ".xlsm"
ThisWorkbook.SaveAs Filename:="C:\Bill\" & fname
End Sub

Press ALT + F11 to return to your sheet, on the Developer tab click Macros, highlight SaveMe and click the Run button.
 
Upvote 0
In the VBE, View > Project Explorer. Double click ThisWorkbook and paste in the following code

Code: 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
Application.EnableEvents = False
Call SaveMe
Application.EnableEvents = True
End Sub
 
Upvote 0
If a file by the same filename exists in the folder and when asked whether to replace the file if I click "No" a Runtime error message 1004 appears which says method saveas of object workbook failed. After this error the automatic naming of file works only after closing and reopening the file. Please help me out of this.
 
Upvote 0
Try this

Code: 
Sub SaveMe()
Dim fname As String
If Not dirExists("C:\Bill") Then MkDir "C:\Bill"
fname = Sheets("Sheet1").Range("A1").Value & Sheets("Sheet1").Range("A2").Value & ".xls"
fname = Replace(Replace(fname, "/", "-"), ":", "-")
On Error Resume Next
ThisWorkbook.SaveAs Filename:="C:\Bill\" & fname
On Error GoTo 0
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Getting Userform Command Buttons to Work with a BeforeSave VBA Macro
2
Replies
19
Views
476
Logit
Logit
J
  • Question Question
VBA Create folder and sub folder
Replies
8
Views
478
jdluke6545
J
M
  • Question Question
Data Transfer from Excel to PowerPoint and getting PDF file of PPT
Replies
1
Views
401
Sequoyah
Sequoyah
P
  • Solved
Problem saving a sheet named as a cell value
2
Replies
12
Views
534
saboh12617
S
D
  • Question Question
Modify a worksheet without registering it as a change
Replies
1
Views
353
Kaper
Kaper

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top