title: In Excel, using VBA, how do I take the "path+filename+extension" and change the extension?
body: <p>Have a program that's dynamically generating an Excel file and a csv. The excel file has VBA code that loads the csv data "on load" and I want to dynamically call that csv by having the csv file have the same filename, but just the different extension. So, based on my understanding this if the xls file was here:</p>
  
  <pre><code>C:\directory\filename.xls
  </code></pre>
  
  <p>This VBA code:</p>
  
  <pre><code>Function GetFullName() As String
  
   GetFullName = ThisWorkbook.FullName 
  
  End Function
  </code></pre>
  
  <p>Would result in </p>
  
  <pre><code>GetFullName() = "C:\directory\filename.xls"
  </code></pre>
  
  <p>So, if that's correct (<a href="http://www.ozgrid.com/VBA/WorkbookPath.htm" rel="nofollow">source of code</a>), how do I replace ".xls" with ".csv" and then insert that value into a file call. Or for example, to keep it simple, the file has VBA that prints a PDF using this code; which would require me to use the GetFullName, and change ".xls" to ".pdf":</p>
  
  <pre><code>Sub PrintPDF()
  
      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
          "C:\directory\filename.pdf", Quality:=xlQualityStandard, _
          IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False _
  
  End Sub
  </code></pre>
  
  <p>Hope the problem is clear, if there's a better solution concept, I'm open to it. If you've got questions, just let me know. </p>