title: Excel VBA - Save As suggested filename and filepath from a cell value
body: <p>I have a macro in an Excel Workbook, that is connected to a button that says <em>Export</em></p>
  <p>When I click the button, it triggers the <em>Export XML</em> dialog and I have to manually search for a folder to export it into and enter the filename.</p>
  <p>Since the folders in my Documents are named exactly the same as the value of the Cell A24, i would like it to direct itself into the correct folder and suggest me a filename based on the value of the Cell A24 with some extra text behind it.</p>
  <p>So far i have this in the VBA:</p>
  <pre><code>Public Sub ExportToXML()
  
      Dim strFilePath As String
      Dim POFilePath As String
      Dim FOFilePath As String
      Dim POFileName As String
      Dim FOFileName As String
      Dim XMLDoc As MSXML2.DOMDocument
      Dim xNode As MSXML2.IXMLDOMNode
      Dim xAttribute As MSXML2.IXMLDOMAttribute
      Dim xElement As MSXML2.IXMLDOMElement
      Dim xElementRoot As MSXML2.IXMLDOMElement
      
      Application.ScreenUpdating = False
      
      MainSheetName = ActiveSheet.Name
      
      POFilePath = &quot;C:\Users\admin\Desktop\&quot; &amp; Range(&quot;A24&quot;)
      
      FOFilePath = &quot;C:\Users\admin\Desktop\&quot; &amp; Range(&quot;D22&quot;) &amp; &quot; &quot; &amp; Range(&quot;A22&quot;)
      
      POFileName = &quot;_report &quot; &amp; Range(&quot;D13&quot;).value &amp; &quot; &quot; &amp; Range(&quot;F13&quot;).value &amp; &quot;.xml&quot;
      
      FOFileName = &quot;_report &quot; &amp; Range(&quot;D13&quot;).value &amp; &quot; &quot; &amp; Range(&quot;F13&quot;).value &amp; &quot;.xml&quot;
      
      If Range(&quot;A24&quot;).value = &quot;0&quot; Then
          
          strFilePath = Application.GetSaveAsFilename(FOFilePath &amp; FOFileName, fileFilter:=&quot;XML files (*.xml),*.xml&quot;, Title:=&quot;Save FileAs...&quot;)
          
      Else
      
          strFilePath = Application.GetSaveAsFilename(POFilePath &amp; POFileName, fileFilter:=&quot;XML files (*.xml),*.xml&quot;, Title:=&quot;Save FileAs...&quot;)
          
      End If
  
      If strFilePath = &quot;False&quot; Then Exit Sub
  </code></pre>
  <p>This gives me the right filename suggestion, but it doesn't direct me to the folder and goes to Desktop.</p>
  <p>Any help would be appriciated!</p>
  <p>EDIT:</p>
  <p>I tried merging the Strings together a bit more and came up with this:</p>
  <pre><code>Public Sub ExportToXML()
  
      Dim strFilePath As String
      Dim POFilePath As String
      Dim FOFilePath As String
      Dim XMLDoc As MSXML2.DOMDocument
      Dim xNode As MSXML2.IXMLDOMNode
      Dim xAttribute As MSXML2.IXMLDOMAttribute
      Dim xElement As MSXML2.IXMLDOMElement
      Dim xElementRoot As MSXML2.IXMLDOMElement
      
      Application.ScreenUpdating = False
      
      MainSheetName = ActiveSheet.Name
      
      POFilePath = &quot;C:\Users\admin\Desktop\&quot; &amp; Range(&quot;A24&quot;) &amp; Range(&quot;A24&quot;).value &amp; &quot;_report &quot; &amp; Range(&quot;D13&quot;).value &amp; &quot; &quot; &amp; Range(&quot;F13&quot;).value &amp; &quot;.xml&quot;
      
      FOFilePath = &quot;C:\Users\admin\Desktop\&quot; &amp; Range(&quot;D22&quot;) &amp; &quot; &quot; &amp; Range(&quot;A22&quot;) &amp; &quot;_report &quot; &amp; Range(&quot;D13&quot;).value &amp; &quot; &quot; &amp; Range(&quot;F13&quot;).value &amp; &quot;.xml&quot;
      
      
      If Range(&quot;A24&quot;).value = &quot;0&quot; Then
          
          strFilePath = Application.GetSaveAsFilename(FOFilePath, fileFilter:=&quot;XML files (*.xml),*.xml&quot;, Title:=&quot;Save FileAs...&quot;)
          
      Else
      
          strFilePath = Application.GetSaveAsFilename(POFilePath, fileFilter:=&quot;XML files (*.xml),*.xml&quot;, Title:=&quot;Save FileAs...&quot;)
          
      End If
  
      If strFilePath = &quot;False&quot; Then Exit Sub
  </code></pre>
  <p>The problem is, that VBA thinks that in:</p>
  <pre><code>POFilePath = &quot;C:\Users\admin\Desktop\&quot; &amp; Range(&quot;A24&quot;) &amp; Range(&quot;A24&quot;).value &amp; &quot;_report &quot; &amp; Range(&quot;D13&quot;).value &amp; &quot; &quot; &amp; Range(&quot;F13&quot;).value &amp; &quot;.xml&quot;
  </code></pre>
  <p>the first Range(&quot;A24&quot;) belongs to the filename part and doesn't continue on with the filepath. So if the value in A24 was &quot;test&quot;, then this suggests saving the xml to Desktop with the filename testttest_report 11 2020</p>