Save a workbook in a specific folder under a given name

Question

I have created an Excel Template workbook called LP LOG.xlt. When the user opens the template, the first thing they are to do is to click on a shape which then asks them for the Job Number for the log.  The job number is called "jobnumber" in the code.

Once they hit enter, the code draws from the job database and fills in all the details stopping a couple of times to request for info from the user.  When that is complete, the user can then carry on with logging their entries as needed.

I would like the code to include a function that saves the Log in the given job's document folder under the name "[Job#] LP Log.xls".  The address of the document folder would be...

E:\Documents[Job#][Job#] LP Log.xls

I have made several workbooks with buttons that save and close that workbook but this one is new for me.  I want to open a template, enter the job number, have it autofil and save itself in its own folder before I can actually start working.  I'm essentially trying to protect the template as well as idiot-proof the saving of the file in the correct location.

Thanks in advance for your time and attention.

James

Answer 1

Sure thing.  I actually took it a step further and made it search for an existing file first.  If there is one, use it, if not make one.

Cheers!!

Sub OpenItOrMakeIt()

Dim jobnumber As String

Dim thisfilePath As String

Dim existingfile As String

Dim thissheetname As String

Dim wbDEST As Workbook

    thissheetname = ActiveSheet.Name

    thisfilePath = "E:\Documents"

'---------------------------------------------------

''So you open the blank LP LOG saved on the network.

''Click the button on the sheet and the

''input box pops up and asks you for the job number.

''Type it in and click ENTER and it looks to see if

''there is a file named "[job#] LP LOG.xls" in the

''main Documents directory.  If there is, it opens

''the existing one and closes this one.

''if there's not, it goes through the process of

''building the workbook just like normal, but then

''it saves this new workbook in the [job#] folder in

''the Documents directory under the name

''of "[job#] LP LOG.xls", closes the blank LP LOG

''and keeps the newly created open and active

''ready for you to start modifying.

'--------------------------------------------------

jobnumber = InputBox("Enter job number: " & jobnumber)

 If jobnumber = "" Then Exit Sub

existingfile = thisfilePath & "" & jobnumber & "" & jobnumber & " LP LOG" & ".xls"

Application.ScreenUpdating = False         

    If Len(Dir(existingfile)) > 0 Then    

        Set wbDEST = Workbooks.Open(existingfile)

        Application.DisplayAlerts = False

            ThisWorkbook.Saved = True

                If Workbooks.Count > 1 Then

                    ThisWorkbook.Close               

                Else

                    Application.Quit

                End If

        Application.DisplayAlerts = True

    Else                                   

'-----------------------------------------------------

''this is where all your standard code would be found.

''If you just had a standalone workbook, you could copy

''and paste the code here.

'-----------------------------------------------------

 ActiveWorkbook.SaveAs thisfilePath & "" & jobnumber & "" & jobnumber & " LP LOG" & ".xls"

    End If

End Sub

Answer 2

Hello James,

We're glad to know that your issue is resolved. If you could share the steps you followed to resolve this issue, it might help other users facing the same issue.

Thank you.

Answer 3

Never mind...figured it out myself.

Thanks anyway.