Saving a WorkBook

[Aeneren]

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thank you in advance. I have very limited knowledge on the use and functions of using the VBA so please be patient and if you respond be detailed in those responses I would appreciate it, I do have experienced with Excel but on the Formula side, this is my first endeavor into Macro’s and using the VBA.<o></o>
<o></o><o></o>
First I have a Workbook that over the past 3 weeks have been building and it will be stored on a Server as a read only and opened by 3-4 users every morning, The Full Work book is over 30+ sheets and has some sensitive material on it and im not able to post the entire book but have created a very small version of 10 sheets that I would attach if able but will Email it to anyone who would like to take a stab at it and it should give you a good idea on what im attempting to do.<o></o>
<o></o>
On the log in page, there are three fields <o></o>
Auto Date with the (=TODAY() function), and a <o></o>
Truck Number (MEDIC: (user selects from ActiveX combo Box2)), and then <o></o>
Person doing the Inventory (CREW: (user selects from ActiveX combo Box1)<o></o>
<o></o>
I Am trying to make a ActiveX Click button on another page to save this workbook using the three field’s listed above, I’ve found several post on this site and on others but as new to programing that I am, I will admit I am over whelmed.<o></o>
<o></o>
The location that will be used to save the file is on our network and its path will be:<o></o>
W:\Scan Snap Documents\Vehicle Checklist\(were the above info needs to be inserted so it saves to the correct folder in following order “MEDIC#””Date””Crew”) <o></o>
<o></o>
If anyone could help me out with this code I would be grateful thank you<o></o>
<o></o>
<o></o>
<o></o>
Aeneren<o></o>

 

[pbornemeier]

The following code will used elements from the "Login" worksheet to create a filename. These elements are as follows:
- ComboBox1
- ComboBox2
- A date in cell C13

And will produce a filename similar to:
W:\Scan Snap Documents\Vehicle Checklist\MEDIC#3_20120413_CrewB.xls

where 3 is the crew number from ComboBox1, CrewB is the crew identifier from ComboBox2, AND 20120413 is generated from the date in C13.

You can change many of the above defaults by editing the Const statements

The workbook will be saved in Excel 2003 format.

Option Explicit

Sub SaveWorkbook()
    
    'This is the name of the worksheet the contains the 3 save parameters
    Const sLoginWorksheetName As String = "Login"
    
    'Information about the sources of the save parrameters
    Const sCrewComboBoxName As String = "ComboBox1"
    Const sTruckNumberComboBoxName As String = "ComboBox2"
    Const sDateFieldAddress As String = "C13" 'Location of date string on Login worksheet
    Const sDateFormat As String = "yyyymmdd" 'Format of date in filename
    
    Dim sFileName As String
    Dim sSaveDirectory As String
    Dim lX As Long
    
    'sSaveDirectory = "W:\Scan Snap Documents\Vehicle Checklist\"
    sSaveDirectory = "j:\"
    
    'Make sure the directory location
    If Right(sSaveDirectory, 1) <> "\" Then sSaveDirectory = sSaveDirectory & "\"
    
    With Worksheets(sLoginWorksheetName)
        sFileName = "MEDIC#" & CStr(.ComboBox2.Value) & "_" & _
            Format(.Range(sDateFieldAddress).Value, sDateFormat) & "_" & _
            CStr(.ComboBox1.Value) & ".xls"
    End With
    
    For lX = 1 To Len(sFileName)
        Select Case Mid(sFileName, lX, 1)
        Case "\", "/", ":", "*", "", "<", ">", "|", Chr(34)
            MsgBox "Invalid character(s) in filename.  \ / : * ? " & Chr(34) & " < > | are not allowed.", vbCritical, "Invalid Filename"
            GoTo End_Sub
        End Select
    Next
    
    If Len(sSaveDirectory & sFileName) > 255 Then
        MsgBox "File path + filename is too long.", vbCritical, "File Path/Name too long"
        GoTo End_Sub
    End If
        
    ActiveWorkbook.SaveAs Filename:=sSaveDirectory & sFileName, _
        FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

End_Sub:

End Sub