create folder and save as
 

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.

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.

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.

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.

Typo

change it to

sFolder = SpecailFolders(wsh_SPECIAL_FOLDER_MY_DOCS & "\Beacon")

--
HTH

Bob Phillips

"runandrun" wrote in message
...
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.

Forget my last post, this is the proper code. Replace everything with this

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

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

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

Set oWSH = CreateObject("WScript.Shell")
SpecialFolders = oWSH.SpecialFolders(folder)
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
...
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.

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

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

You have a couple of choices.

I think I'd choose to format it the way I want in the code--then I don't have to
take any chances with what the user (you???) typed:

ActiveWorkbook.SaveAs sFolder & "\" & _
Worksheets("Grad Info").Range("account_name") & _
" - " & _
format(Worksheets("Grad Info").Range("date_taken").value,"yyyymmdd")

Or if you're positive that the format of the date in the cell is ok:

ActiveWorkbook.SaveAs sFolder & "\" & _
Worksheets("Grad Info").Range("account_name") & _
" - " & _
Worksheets("Grad Info").Range("date_taken").Text

(.text is what you see, .value (the default property if the property isn't
explicitly shown) is still a date (with the slashes.)



runandrun wrote:

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


--

Dave Peterson

Many thanks for your help, Dave. The spreadsheet isn't for me - I'll be
distributing it among inexperienced users. With that in mind, is there any
way to change the error message if the user tries to save without entering
account_name or date_taken? I'd like it to tell them why it hasn't worked.
(I'm not sure of the protocol here - should this be a new thread?)

"Dave Peterson" wrote:

You have a couple of choices.

I think I'd choose to format it the way I want in the code--then I don't have to
take any chances with what the user (you???) typed:

ActiveWorkbook.SaveAs sFolder & "\" & _
Worksheets("Grad Info").Range("account_name") & _
" - " & _
format(Worksheets("Grad Info").Range("date_taken").value,"yyyymmdd")

Or if you're positive that the format of the date in the cell is ok:

ActiveWorkbook.SaveAs sFolder & "\" & _
Worksheets("Grad Info").Range("account_name") & _
" - " & _
Worksheets("Grad Info").Range("date_taken").Text

(.text is what you see, .value (the default property if the property isn't
explicitly shown) is still a date (with the slashes.)



runandrun wrote:

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


--

Dave Peterson

You could check before you try to do the save:

if isempty(Worksheets("Grad Info").Range("account_name")) _
or isempty(Worksheets("Grad Info").Range("date_taken")) then
msgbox "please fill in both the account name and date"
else
'do the save
end if



runandrun wrote:

Many thanks for your help, Dave. The spreadsheet isn't for me - I'll be
distributing it among inexperienced users. With that in mind, is there any
way to change the error message if the user tries to save without entering
account_name or date_taken? I'd like it to tell them why it hasn't worked.
(I'm not sure of the protocol here - should this be a new thread?)

"Dave Peterson" wrote:

You have a couple of choices.

I think I'd choose to format it the way I want in the code--then I don't have to
take any chances with what the user (you???) typed:

ActiveWorkbook.SaveAs sFolder & "\" & _
Worksheets("Grad Info").Range("account_name") & _
" - " & _
format(Worksheets("Grad Info").Range("date_taken").value,"yyyymmdd")

Or if you're positive that the format of the date in the cell is ok:

ActiveWorkbook.SaveAs sFolder & "\" & _
Worksheets("Grad Info").Range("account_name") & _
" - " & _
Worksheets("Grad Info").Range("date_taken").Text

(.text is what you see, .value (the default property if the property isn't
explicitly shown) is still a date (with the slashes.)



runandrun wrote:

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


--

Dave Peterson


--

Dave Peterson