title: Setting range properly in DoCmd.TransferSpreadSheet (VBA Access in Excel)?
body: <pre><code>For Each Page In Worksheets
      PageName = Split(Page.Name, " ")
      If UBound(PageName) &gt; 0 Then
          Worksheets(Page.Name).Activate
          lRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
          LCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
          Fullrange = Worksheets(Page.Name).Range(Worksheets(Page.Name).Cells(1, 1), _
              Worksheets(Page.Name).Cells(lRow, LCol))
          accappl.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, _
              Page.Name, strpathxls, True, Fullrange
      End If
  Next
  </code></pre>
  
  <p>I have written this code in VBA Excel to backup data into access from excel. The code doesn't like the way that I wrote the range in my for each loops. I also tried the 2nd for each loop, but that just backed up the main page repeatedly( with the correct table names though). </p>
  
  <p>I think the 1st way is close, but I don't understand what is wrong with FullRange line which is type Range.</p>
  
  <p>EDIT: The error is object variable or with block variable not set on the FullRange line</p>
  
  <p>Update 6-18, It seems that the fullrange should be in the form string. I have edited a little but the error I am getting now on the transferspreadsheet line is "The Microsoft database engine could not find the object'1301 Array$A$1:J$12'. Make sure that the object exists and you spell its name correctly. </p>
  
  <p>I took out fullrange and put in page.name and it gave me the same error. </p>
  
  <pre><code>For Each Page In Worksheets
      PageName = Split(Page.Name, " ")
      If UBound(PageName) &gt; 0 Then
          ' Worksheets(Page.Name).Activate - this line is most likely not needed
          lRow = Page.Range("A" &amp; Rows.Count).End(xlUp).Row
          LCol = Page.Cells(2, Columns.Count).End(xlToLeft).Column
          fullRange = Page.Name &amp; Page.Range(Page.Cells(1, 1), _
              Page.Cells(lRow, LCol)).Address
          accappl.DoCmd.TransferSpreadsheet acImport, _
              acSpreadsheetTypeExcel12Xml, Page.Name, strpathxls, True, Page.Name
      End If
  Next  
  </code></pre>