Setting Default SaveAs Name Before Saving Workbook

[Shades047]

Hello,

I am trying to set the default Save As name for an Excel workbook that was created through LabVIEW. The LabVIEW program opens a new Excel workbook from a template, populates it with data, and does some analysis. When doing the same thing in Word, this was the macro that could make that work:

Sub ChangeProps()


'change properties
    If Documents.Count > 0 Then
         Set dlgProp = Dialogs(wdDialogFileSummaryInfo)
         ' Establish title, subject, author, and keywords values
         dlgProp.Title = MakeADocTitle(LVTitle)
         dlgProp.Subject = LVSubject
         dlgProp.Author = LVAuthor
         dlgProp.Comments = LVComments


         ' Set the values
         dlgProp.Execute


         ' Show the dialog for testing purposes
         'dlgProp.Show
    End If


End Sub


Function MakeADocTitle(ByVal strTitle As String) As String


arrSplit = Split(strTitle, " ")


MakeADocTitle = arrSplit(0)
For I = 1 To UBound(arrSplit)
    MakeADocTitle = MakeADocTitle & Chr(95) & arrSplit(I)
Next I


MakeADocTitle = MakeADocTitle
End Function

Because for some reason just setting the "Title" property in a Word document does not set the default SaveAs name. This is also true of Excel, but I couldn't adapt that macro to work in Excel. The reason why I want to do this is because this is being used by the guys out in the machine shop, and we want to save the Excel files as "serial number.xls", like "SN55698755.xls". Because they will be doing this over and over and the serial numbers are all a bit samey, I want the computer to fill in the title for them so that they don't have to worry about making a mistake.

Is there a similar macro that does the same job in Excel? I am sorry if this is a super basic question. I tried Googling this about a dozen different ways, but they all seem to involve opening a dialog box, which doesn't work from LabVIEW (maybe doesn't work from 3rd party programs at all?). Even just telling me the right word to search for would be a big help.

[SamT]

In ThisWorkbook Code module

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim SAName As String 'SA - Save As
Dim SAPath As String

On Error GoTo EndSub

'To be Completed
'SAName = ??? Probably a function to return Serialized Name
SAPath = ThisWorkbook.Path 'or Maybe a hard coded path
SAPath = SAPath & "\" 'Can't forget the last \slash\

ThisWorkbook.SaveCopyAs SAPath & SAName
ThisWorkbook.Saved = True

Cancel = True
Exit Sub

EndSub:
MsgBox "Oopsies! Sumpin bad happen"
End Sub

I have a workbook I want a Copy saved every time there is a change. Instead of a serial number, I use the DateTime converted to a decimal number string.

SAName = CStr(CDbl(NOW)) & " - " & ThisWorkbook.Name & ".xls"
'OR
SAName = ThisWorkbook.Name & " - " & CStr(CDbl(NOW))& ".xls"

[Shades047]

I tried what you had and changed it to this:

Option Explicit 
 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
     
    Dim SAName As String 'SA - Save As
    Dim SAPath As String 
     
    On Error GoTo EndSub 
     
     'To be Completed
    SAName = "steve"
    SAPath = ThisWorkbook.Path 'or Maybe a hard coded path
    SAPath = SAPath & "\" 'Can't forget the last \slash\
     
    ThisWorkbook.SaveCopyAs SAPath & SAName 
    ThisWorkbook.Saved = True 
     
    Cancel = True 
    Exit Sub 


End Sub

I took out the MsgBox part because opening any kind of window or dialog box doesn't seem to work (tried it once with it anyway). It didn't work. I ran this in an Excel workbook on it's own. It saves the file like you would expect form a SaveCopyAs command. But I don't want to save a copy at macro execution, I just want to set the name the file will have when the user hits the "Save" button themselves for the file's first save.

[SamT]

Did you try changing "SaveCopyAs" to "SaveAs"?

And put "EndSub:" back in so that "Cancel" is bypassed when an error occurs.

You can't change the Excel "Title" property because Excel doesn't have one. If you want to change the Name of an exiting workbook, you have to SaveAs.

I just want to set the name the file will have when the user hits the "Save" button themselves for the file's first save.

I knew that the first time.

The end results on the hard drive are the same if you SaveCopyAs the working template every time, if you SaveAs the book every time, or if you SaveAs the book the first time and thereafter merely Save it.

This does not fulfill every requirement, because I don't fully understand every requirement. I was thinking to get this part working, then worry about...
Does the Serialized name change only once a day or every time the book is saved?

Is there a particular format of the serial number required?
OR...
If only once a day can it start tomorrow with 42883 and increment by one each calendar day?
ElseIf every time the book is saved, can it start tomorrow with 42883.123456 and increase the decimal portion at each save and the integer portion by one each day?

As a programmer I strongly prefer the latter two options since those are built in and don't require any custom functions.

Once a day: SAName = CStr(CLng(Date)) & ".xls" = "42883.xls"
Every time saved: SAName = CStr(CDbl(Now)) etc = "42883.123456.xls"
Or you can use a custom human readable version: SAName = Format(Now, "yyyymmdd") etc = "20170528.xls"

[Shades047]

The serial number changes basically randomly, as there is no guarantee that the serialized product (valves in this case) will be tested in order. That part is easy to handle in LabVIEW however- you just split the macro into two strings around the serial number, then concatenate the three strings together, sort of like:

concatenate("Sub SetTitle()\nSome Code","Serial Number","Rest of Code")

and then you send that completed macro into the "Excel Run Macro" part of your LabVIEW program.

I will try to be a lot clearer, and I'm sorry I didn't describe my problem correctly.

In Excel, if you open a new document, the first time you hit "Save", it wants to call it "Book1". The operator may or may not want to actually save the workbook depending on the results, which he's supposed to review on the screen. If he does want to save them, then he hits the "Save" button, and that's when I want the name to be populated. At least that's what I thought I wanted. It's funny, but after talking to you, I now think this is retarded on my part. I wanted to do it that way because that's how we do it with Word, but with the Word reports, we save the data in a separate text file automatically. That way, if the Word report is accidentally closed without saving, the Word report can be regenerated. With the Excel reports, however, we plan to store the data in the Excel file itself (naturally), so there's no reason not to automatically save. In which case I don't really have a problem.

I'm sorry to have wasted your time, but this was really valuable to me. Thank you for your help!

[SamT]

Your welcome, and, there was no waste of time for me.