title: Export Access database to existing excel doc using VBA
body: <p>So after searching through numerous other pages im still having difficulty and admittedly its due to self teaching myself VBA, so forgive me :(</p>
  
  <p>I have created a simple Phone call counter Access form with a table. My goal is to press a button and it exports the table to a specified directory to an existing XLSM (the data exported would open a new worksheet with the current date as the worksheet name and then save it.  </p>
  
  <p>database location is here:
  L:\Reports\TestCalltoolmetric.accdb</p>
  
  <p>existing Excel file is here:
  L:\Reports\Callcounterreports\MonthlyCallCounter.xlsm</p>
  
  <p>What i have tried so far...</p>
  
  <p>1.</p>
  
  <pre><code>Dim strTable As String 
  Dim strWorksheetPath As String
  strWorksheetPath = "L:\Reports\Callcounterreports"
  strWorksheetPath = strWorksheetPath &amp; Format(Date, "ddmmmyy") &amp; "Callreport.xlsx"
  </code></pre>
  
  <p>This exports my database to a new file with the name of the file as the date+callreport. It works but i dont know if i can use this command to accomplish my goal above (filename isnt really important but at the end of the month i need to provide call metrics so i need every days export all in one workbook so i can create totals).</p>
  
  <p>2.
  I tried using this:</p>
  
  <pre><code>DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Table1", "L:\Reports\Callcounterreports\MonthlyCallCounter.xlsm"
  </code></pre>
  
  <p>But it errors out saying that Access database engine could not find the object 'Table1'</p>
  
  <p>3.
  I also found through some furious googling a similar request but it was based off exporting a query...in an less than educated mannor i tried adapting it but never really was able to get it to work..</p>
  
  <pre><code>Dim appXL As Object
  Dim wb As Object
  Dim wks As Object
  Dim xlf As String
  Dim rs As DAO.Recordset
  
  xlf = "L:\Reports\Callcounterreports\MonthlyCallCounter.xlsm" 'Full path to Excel file
  
  Set rs = CurrentDb.OpenRecordset("Query1") 'Replace Query1 with real query name
  Set appXL = CreateObject("Excel.Application")
  Set wb = appXL.Workbooks.Open(xlf)
  Set wks = wb.Sheets &amp; Format (Date, "ddmmmyy") ' Sheet name
  
  
  wb.Save
  wb.Close
  appXL.Quit
  Set wb = Nothing
  rs.Close
  Set rs = Nothing
  </code></pre>
  
  <p>I know i am by far not the first person to probably ask about how to do this, but any help would be fantastic!</p>