title: Opening and Excel file in Access using VBA and saving it to a different name and closing it properly
body: <p>I have been searching for some time on how exactly to go about this, but I keep coming up with a large number of possible ways that come close, but never really give me exactly the sort of thing I'm looking for.  The concept is pretty simple I need to open a certian .xls file using some VBA code in Access 2010.  Once the file is opened I need to insert data and do some things to the file then save the file as a different filename and close the file. I also need it to close excel if it was not already open and if it was open I need it to leave excel alone and not save/close anything other than the template.xls file I am working with. I currently have code that will do part of this provided Excel is not already open at the time the script runs.  When excel is already opened I get the following error;</p>
  
  <p><em>"Run-time'91':  Object variable or With block variable not set."</em>  </p>
  
  <p>When I click debug I get the following line highlighted</p>
  
  <p><em>x.ActiveWorkbook.SaveAs fileName:=savedfilename</em></p>
  
  <p>Here is the code without all the junk that doesn't relate to the issue. I have cobbled together using examples from various sites.</p>
  
  <pre><code>    Dim DateSampled As String
      Dim strPath As String
      Dim TemplatePath As String
      Dim x As Excel.Application
      Dim xBook As Excel.Workbook
      Dim xSheet As Excel.Worksheet
  
      DateAsString = Format(DateSampled, "MMDDYYYY")
      savedfilename = strPath &amp; "\" &amp; TrainNum &amp; "-" &amp; DateAsString &amp; ".xls"
      TemplatePath = "B:\template.xls"
  
      Set x = CreateObject("Excel.Application")
      x.Visible = False
      Set xBook = GetObject(TemplatePath)
      xBook.Windows(1).Visible = True
      Set xSheet = xBook.Worksheets(1)
  
      '---------------CODE DOES STUFF WITH THE FILE -----------------------
  
      x.DisplayAlerts = False
      x.ActiveWorkbook.SaveAs fileName:=savedfilename
      x.DisplayAlerts = True
      x.ActiveWorkbook.Close
  
      Set x = Nothing
      Set xBook = Nothing
      Set xSheet = Nothing
  </code></pre>