title: Import Excel in Access with custom dynamic ranges
body: <p>I have some trouble getting my import to work:</p>
  
  <pre><code>Function importMetrics() 
  Dim strFile As String 
  Dim strPath As String 
  Dim strWorksheet As String 
  Dim strTable As String 
  
  'Excel variables 
  Dim xlApp As Excel.Application 
  Dim xlFile As Excel.Workbook 
  Dim xlSheet As Excel.Worksheet 
  Dim xlRange1 As Excel.Range 
  Dim xlRange2 As Excel.Range 
  
  Dim r1, r2 As String 
  Dim r#, c# 
  Dim clVal As String
  
  'Set File Path 
  strPath = CurrentProject.Path &amp; "\Data\2015\01 - January" 
  
  'check if directory exists 
  If Len(Dir(strPath, vbDirectory)) = 0 Then 
      MsgBox ("File Doesn't exist") 
      End 
  End If 
  
  'Import all Files 
  strFile = Dir(strPath &amp; "\*.xl*") 
  
  'ID which Sheet to Import 
  strWorksheet = "EP &amp; NBO DATA" 
  
  'ID Table to Import To 
  strTable = "tbl_MIPData" 
  
  'start loop 
  Do While strFile &lt;&gt; "" 
      'Open File 
      'Get the info from Excel: 
      Set xlApp = New Excel.Application 
  
      Set xlFile = xlApp.Workbooks.Open(strPath &amp; "\" &amp; strFile, False, True) 
  
      Set xlSheet = xlFile.Sheets(strWorksheet) 
  
      Set xlRange1 = xlSheet.Range("A1" &amp; xlSheet.Range("A1").End(xlDown).End(xlToRight)).Select 
      Set xlRange2 = xlSheet.Range("N1" &amp; xlSheet.Range("N1").End(xlDown).End(xlToRight)).Select 
  
      'Import File Range A 
  
       DoCmd.TransferSpreadsheet _ 
          transfertype:=acImport, _ 
          tablename:=strTable, _ 
          FileName:=strPath &amp; "\" &amp; strFile, _ 
          hasFieldNames:=False, _ 
          Range:=xlRange1 
  
      'Import File Range B 
      DoCmd.TransferSpreadsheet _ 
          transfertype:=acImport, _ 
          tablename:=strTable, _ 
          FileName:=strPath &amp; strFile, _ 
          hasFieldNames:=False, _ 
          Range:=strWorksheet &amp; "!" &amp; xlRange2 
  
          'Close Excel File 
          xlApp.Quit 
          Set xlApp = Nothing 
  Loop 
  
  End Function 
  </code></pre>
  
  <p>This is the code I've written so far. I think it has to do with how I am opening the Excel files. The Range fails on <code>xlRange1</code> and <code>xlRange2</code>. If I put an <code>xlSheet.Select</code> it doesn't seem to fix it.</p>
  
  <p>I am stumped at this point. In addition I couldn't find much on how to recursively walk the sub-directories (e.g. Walk all the Excel files in data)</p>
  
  <p>Access 2010, with Excel 2010 XLSX files.</p>