Create a new workbook from an Excel template located in a different folder

Question

I have an Excel 2016 spreadsheet which contains the details of quotes sent to Customers (1 row for each quote containing cells with the Customer Name, Quote Date, Quote Number etc). I am trying to design a macro to reside in this spreadsheet that will create a new workbook based on an existing Excel template (UK - Quote Sheet.xltm), populate certain fields in the new workbook and save it with a filename name constructed from elements of quote entry. For example, if the spreadsheet contains an entry of 19/11/18 in the Date Cell, ABC in Customer Name field and Q1234 in the Quote Number field, the new file will be given the name "Q1234 - ABC (19.1.18).xlsm" and the Date, Customer Name and Quote Number will be inserted into the appropriate Worksheet cells in the new workbook. I have been able to get the macro to generate the appropriate filename and populate the cells in the new workbook but only by using absolute file paths for both the location of the template and the location of where the new workbook is to be stored. This is fine so long as the folder paths are the same on every PC I need to run the macro but this isn't the case so the macro fails when it's run on a PC where the file paths are different. Whilst the folder structure on every PC will be:

..\Documents\Quotes\Quotes Database for the location of Quotes details spreadsheet
..\Documents\Templates\Quotes for the location of the template file ..\Documents\Quotes\Amendable Quotes for location of the new workbook

the location of this folder tree will vary by PC.

The following code works for when the above folder tree resides inside C:\VD Operations but I need to modify so that it works no matter where the folder tree resides e.g. it could be under D:\Work\VD Operations:

Sub CreateQuote()   'Generate a new quote using UK - Quote Sheet Template

Dim qContact, qNo, qDate, qCustomer As String
Dim qFilename As String
Dim qDay, qMonth, qYear As String
Dim qNewWorkbook As Workbook
Dim qDest As Worksheet

' Get quote details from appropriate entry in Quote Database

qContact = Cells(ActiveCell.Row, 13).Value
qNo = Cells(ActiveCell.Row, 1).Value
qCustomer = Cells(ActiveCell.Row, 12).Value
qDate = Cells(ActiveCell.Row, 3).Value
qDay = Day(qDate)
qMonth = Month(qDate)
qYear = Right(Year(qDate), 2)

Set qNewWorkbook = Workbooks.Open(Filename:="C:\VD Operations\Documents\Templates\Quotes\UK - QUOTE SHEET.xltm", _
Editable:=False)

' Construct quote file name

qFilename = "C:\VD Operations\Documents\Quotes\Amendable Quotes\" & qNo & " - " & qCustomer & " (" & qDay & "." & qMonth & "." & qYear & ").xlsm"

' Update Cover sheet in quote with values from Quote Database

Set qDest = qNewWorkbook.Sheets("Cover Sheet")
qDest.Range("QuoteNo") = qNo
qDest.Range("Customer") = qCustomer
qDest.Range("Contact") = qContact
qDest.Range("QuoteDate") = qDate

' Save the quote
qNewWorkbook.SaveAs Filename:=qFilename, FileFormat:=52
End Sub

I know I can find the path of the Quote details spreadsheet but not sure how to use this to calculate the path to the other files.

ThisWorkbook.Path will give you the location of the macro workbook, so you can use `\..\` to go "up" from there and then back down into the folder you need. — Tim Williams

Tim Williams Tim Williams · Accepted Answer · 2018-11-20T00:11:42

For this folder structure (for example):

C:\
    _Stuff\
         test\
             tmp1\   '<< macro file is stored here
             tmp2\   '<< need to load a file "test.xlsx" from here

You can do this:

  ThisWorkbook.Path                        '>> C:\_Stuff\test\tmp1

  ThisWorkbook.Path & "\..\tmp2\test.xlsx" '>> C:\_Stuff\test\tmp2\test.xlsx

so the .. takes you "up" to C:\_Stuff\test and from there you can go "down" to tmp2

Create a new workbook from an Excel template located in a different folder

1 Answers