Hi Bob, Hi Dave.
Again, thanks. Everything now works fine but for one problem - the code
seems to fall over unless I enter 'date_taken' as text instead of as a date -
is there a way to fix this? I've tried formatting the date as 'xx-xx-xxxx'
rather than 'xx/xx/xxxx' (on the basis that '/' is not allowed in a filename)
but it makes no difference.


"Dave Peterson" wrote:

I think Bob had a typo and a change of heart on how to approach the problem.

Minor modifications of Bob's code seemed to make it work ok:

Option Explicit

Sub SaveFile()
Const wsh_SPECIAL_FOLDER_MY_DOCS As Long = 16
Dim sFolder As String

sFolder = SpecialFolders(wsh_SPECIAL_FOLDER_MY_DOCS) & "\Beacon"
If Not FolderExists(sFolder) Then
MkDir sFolder
End If
ActiveWorkbook.SaveAs sFolder & "\" & _
Worksheets("Grad Info").Range("account_name") & _
" - " & _
Worksheets("Grad Info").Range("date_taken")
End Sub

'----------------------------------------------------------------
Function SpecialFolders(mySpecFolderType As Long) As String
'----------------------------------------------------------------
Dim oWSH As Object

Set oWSH = CreateObject("WScript.Shell")
SpecialFolders = oWSH.SpecialFolders(mySpecFolderType)
Set oWSH = Nothing

End Function


'-----------------------------------------------------------------
Function FolderExists(Folder) As Boolean
'-----------------------------------------------------------------
Dim sFolder As String
On Error Resume Next
sFolder = Dir(Folder, vbDirectory)
If sFolder < "" Then
If (GetAttr(sFolder) And vbDirectory) = vbDirectory Then
FolderExists = True
End If
End If
End Function

runandrun wrote:

Sorry - should have said, the compile error is 'Sub or Function not defined'

"runandrun" wrote:

Hi Bob.

Thanks for this - there's a (hopefully) slight problem. The code is throwing
up a 'Compile Error' on the line:
sFolder = specialfoldser(wsh_SPECIAL_FOLDER_MY_DOCS & "\Beacon")

The word 'specialfoldser' is highlighted. I've tried deleting the second 's'
- on the assumption that maybe it should say 'specialfolder' but that makes
no difference.

Thanks for your help.

"Bob Phillips" wrote:

The code is attached below.

First.

start to record a macro.
ToolsMacrorecord A New Macro ...
Change the name to SaveFile
Make sure that the loaction is Store macro in ... is ThisWorkbook
OK
Don't do anything, just click the Stop Recording button.

edit this macro
ToolsMacroMacros
Select the SaveFile macro in the list
Click OK
Replace everything with the code given here
Close the VBIDE window

now add a button
ToolsCustomize
make sure Forms is checked
from the forms toolbar, drag a commandbutton onto the worksheet
assign SaveFile to that button
OK

should all be okay



Sub SaveFile()
Const wsh_SPECIAL_FOLDER_MY_DOCS As Long = 16
Dim sFolder As String

sFolder = specialfoldser(wsh_SPECIAL_FOLDER_MY_DOCS & "\Beacon")
If Not FolderExists(sFolder) Then
MkDir sFolder
End If
ActiveWorkbook.SaveAs sFolder & "\" & _
Worksheets("Grad Info").Range("account_name") & _
" - " & _
Worksheets("Grad Info").Range("date_taken")
End Sub

'----------------------------------------------------------------
Function SpecialFolders() As String
'----------------------------------------------------------------
Dim oWSH As Object

Set oWSH = CreateObject("WScript.Shell")
SpecialFolders = oWSH.SpecialFolders(wsh_SPECIAL_FOLDER_MY_DOCS)
Set oWSH = Nothing

End Function


'-----------------------------------------------------------------
Function FolderExists(Folder) As Boolean
'-----------------------------------------------------------------
Dim sFolder As String
On Error Resume Next
sFolder = Dir(Folder, vbDirectory)
If sFolder < "" Then
If (GetAttr(sFolder) And vbDirectory) = vbDirectory Then
FolderExists = True
End If
End If
End Function

--
HTH

Bob Phillips

"runandrun" wrote in message
...
I'm distributing a spreadsheet among some inexperienced computer users. I
want them to enter a place (eg Birmingham) into a cell named
'account_name',
and a date into a cell named 'date_taken' on a sheet named 'Grad Info'. I
then want them to click on a button which will automatically save the
workbook as filename: 'account_name date_taken', to a folder named
'Beacon' -
checking if the folder exists in My Documents and creating it if it
doesn't.
I'm an inexperienced user of macros and VBA and know that many respondents
on
this forum are genii, so would appreciate simple instructions - preferably
aimed at about 'bright nine year old' level. Many thanks in anticipation.




--

Dave Peterson