+ Reply to Thread
Results 1 to 11 of 11

create folder and save as

  1. #1
    runandrun
    Guest

    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.

  2. #2
    Bob Phillips
    Guest

    Re: create folder and save as

    The code is attached below.

    First.

    start to record a macro.
    Tools>Macro>record 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
    Tools>Macro>Macros
    Select the SaveFile macro in the list
    Click OK
    Replace everything with the code given here
    Close the VBIDE window

    now add a button
    Tools>Customize
    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" <runandrun@discussions.microsoft.com> wrote in message
    news:EE7E7D65-C6B1-480F-8F34-C35853B681B9@microsoft.com...
    > 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.




  3. #3
    runandrun
    Guest

    Re: create folder and save as

    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.
    > Tools>Macro>record 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
    > Tools>Macro>Macros
    > Select the SaveFile macro in the list
    > Click OK
    > Replace everything with the code given here
    > Close the VBIDE window
    >
    > now add a button
    > Tools>Customize
    > 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" <runandrun@discussions.microsoft.com> wrote in message
    > news:EE7E7D65-C6B1-480F-8F34-C35853B681B9@microsoft.com...
    > > 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.

    >
    >
    >


  4. #4
    runandrun
    Guest

    Re: create folder and save as

    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.
    > > Tools>Macro>record 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
    > > Tools>Macro>Macros
    > > Select the SaveFile macro in the list
    > > Click OK
    > > Replace everything with the code given here
    > > Close the VBIDE window
    > >
    > > now add a button
    > > Tools>Customize
    > > 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" <runandrun@discussions.microsoft.com> wrote in message
    > > news:EE7E7D65-C6B1-480F-8F34-C35853B681B9@microsoft.com...
    > > > 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.

    > >
    > >
    > >


  5. #5
    Bob Phillips
    Guest

    Re: create folder and save as

    Typo

    change it to

    sFolder = SpecailFolders(wsh_SPECIAL_FOLDER_MY_DOCS & "\Beacon")

    --
    HTH

    Bob Phillips

    "runandrun" <runandrun@discussions.microsoft.com> wrote in message
    news:B0406E7C-91FD-4C31-854A-4B4C083851D9@microsoft.com...
    > 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.
    > > > Tools>Macro>record 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
    > > > Tools>Macro>Macros
    > > > Select the SaveFile macro in the list
    > > > Click OK
    > > > Replace everything with the code given here
    > > > Close the VBIDE window
    > > >
    > > > now add a button
    > > > Tools>Customize
    > > > 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" <runandrun@discussions.microsoft.com> wrote in message
    > > > news:EE7E7D65-C6B1-480F-8F34-C35853B681B9@microsoft.com...
    > > > > 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.
    > > >
    > > >
    > > >




  6. #6
    Bob Phillips
    Guest

    Re: create folder and save as

    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" <runandrun@discussions.microsoft.com> wrote in message
    news:B0406E7C-91FD-4C31-854A-4B4C083851D9@microsoft.com...
    > 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.
    > > > Tools>Macro>record 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
    > > > Tools>Macro>Macros
    > > > Select the SaveFile macro in the list
    > > > Click OK
    > > > Replace everything with the code given here
    > > > Close the VBIDE window
    > > >
    > > > now add a button
    > > > Tools>Customize
    > > > 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" <runandrun@discussions.microsoft.com> wrote in message
    > > > news:EE7E7D65-C6B1-480F-8F34-C35853B681B9@microsoft.com...
    > > > > 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.
    > > >
    > > >
    > > >




  7. #7
    Dave Peterson
    Guest

    Re: create folder and save as

    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.
    > > > Tools>Macro>record 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
    > > > Tools>Macro>Macros
    > > > Select the SaveFile macro in the list
    > > > Click OK
    > > > Replace everything with the code given here
    > > > Close the VBIDE window
    > > >
    > > > now add a button
    > > > Tools>Customize
    > > > 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" <runandrun@discussions.microsoft.com> wrote in message
    > > > news:EE7E7D65-C6B1-480F-8F34-C35853B681B9@microsoft.com...
    > > > > 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

  8. #8
    runandrun
    Guest

    Re: create folder and save as

    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.
    > > > > Tools>Macro>record 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
    > > > > Tools>Macro>Macros
    > > > > Select the SaveFile macro in the list
    > > > > Click OK
    > > > > Replace everything with the code given here
    > > > > Close the VBIDE window
    > > > >
    > > > > now add a button
    > > > > Tools>Customize
    > > > > 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" <runandrun@discussions.microsoft.com> wrote in message
    > > > > news:EE7E7D65-C6B1-480F-8F34-C35853B681B9@microsoft.com...
    > > > > > 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
    >


  9. #9
    Dave Peterson
    Guest

    Re: create folder and save as

    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.
    > > > > > Tools>Macro>record 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
    > > > > > Tools>Macro>Macros
    > > > > > Select the SaveFile macro in the list
    > > > > > Click OK
    > > > > > Replace everything with the code given here
    > > > > > Close the VBIDE window
    > > > > >
    > > > > > now add a button
    > > > > > Tools>Customize
    > > > > > 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" <runandrun@discussions.microsoft.com> wrote in message
    > > > > > news:EE7E7D65-C6B1-480F-8F34-C35853B681B9@microsoft.com...
    > > > > > > 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

  10. #10
    runandrun
    Guest

    Re: create folder and save as

    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.
    > > > > > > Tools>Macro>record 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
    > > > > > > Tools>Macro>Macros
    > > > > > > Select the SaveFile macro in the list
    > > > > > > Click OK
    > > > > > > Replace everything with the code given here
    > > > > > > Close the VBIDE window
    > > > > > >
    > > > > > > now add a button
    > > > > > > Tools>Customize
    > > > > > > 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" <runandrun@discussions.microsoft.com> wrote in message
    > > > > > > news:EE7E7D65-C6B1-480F-8F34-C35853B681B9@microsoft.com...
    > > > > > > > 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
    >


  11. #11
    Dave Peterson
    Guest

    Re: create folder and save as

    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.
    > > > > > > > Tools>Macro>record 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
    > > > > > > > Tools>Macro>Macros
    > > > > > > > Select the SaveFile macro in the list
    > > > > > > > Click OK
    > > > > > > > Replace everything with the code given here
    > > > > > > > Close the VBIDE window
    > > > > > > >
    > > > > > > > now add a button
    > > > > > > > Tools>Customize
    > > > > > > > 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" <runandrun@discussions.microsoft.com> wrote in message
    > > > > > > > news:EE7E7D65-C6B1-480F-8F34-C35853B681B9@microsoft.com...
    > > > > > > > > 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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1