title: importing data from excel to access using ms excel vba
body: <p>I'm having an issue that occurs randomly (run it on my machine - it works about 90% of the time, couple of attemps on client's machine it didn't work 100% of time)
  here's the code:</p>
  
  <pre><code>sub importdata()
  Dim dbpath As String
  Dim acc As New Access.Application
  'bunch of stuff
  acc.OpenCurrentDatabase dbpath &amp; "\Database.accdb"        
  acc.DoCmd.TransferSpreadsheet acImport, 10, "tbl_SalesData", dbpath &amp; "\Dashboard 2015.04.17.xlsm", True, "DataForImport!"
  acc.CloseCurrentDatabase
  acc.Quit
  'bunch of more stuff
  end sub
  </code></pre>
  
  <p>DataForImport is the sheet where the data resides. It's in a table; however, access failed to recognize the table name "Sales" as a range to import, so i went with importing the entire sheet.</p>
  
  <p>on the client's machine the code gives an error on the transferspreadsheet line (tries to open another instance of excel with the dashboard file and says that the file is already open, error three thousand something); however, on my machine it runs just fine.</p>
  
  <p>Did anyone run into a similar issue? is there a better way to push data from excel to access?If not, does transferspreadsheet prefer closed files, so i need to close the file i'm importing data from (Dashboard) before running that piece of code and re-opening it after? </p>
  
  <p>Thank you!</p>
  
  <p>Answer:</p>
  
  <p>saving a temp file and pushing from it works well</p>
  
  <pre><code>Workbooks.Add.SaveAs dbpath &amp; "\tempwb.xlsx"
          Set tempwb = Application.Workbooks("tempwb.xlsx")
          Application.Workbooks(dashboard).Activate
          acc.OpenCurrentDatabase dbpath &amp; "\Database.accdb"
          Application.Workbooks(dashboard).Sheets("DataForImport").Copy Before:=tempwb.Sheets(1)
          tempwb.Save
          tempwb.Close
          acc.DoCmd.TransferSpreadsheet acImport, 10, "tbl_SalesData", dbpath &amp; "\tempwb.xlsx", True, "DataForImport!"
          Kill dbpath &amp; "\tempwb.xlsx"
          acc.CloseCurrentDatabase
          acc.Quit
          Set acc = Nothing
  </code></pre>