Ytukyg

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:

..DocumentsQuotesQuotes Database for the location of Quotes details spreadsheet
..DocumentsTemplatesQuotes for the location of the template file
..DocumentsQuotesAmendable 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:WorkVD 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 OperationsDocumentsTemplatesQuotesUK - QUOTE SHEET.xltm", _

Editable:=False)



' Construct quote file name



qFilename = "C:VD OperationsDocumentsQuotesAmendable 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.

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:_Stufftesttmp1



  ThisWorkbook.Path & "..tmp2test.xlsx" '>> C:_Stufftesttmp2test.xlsx

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