title: Import mulitple excelfiles with multiple sheets - issue with range
body: <p>I'm trying to import multiple Excelfiles with multiple sheets.</p>
  <pre><code>Set objExcel = CreateObject(&quot;Excel.Application&quot;)
  objExcel.Visible = True
  
  strFileName = &quot;C:\SomeFile\File.xlsx&quot;
  
  Set objWorkbook = objExcel.Workbooks.Open(strFileName)
  Set colWorksheets = objWorkbook.Worksheets
  
  For Each objWorksheet in colWorksheets 
      Set objRange = objWorksheet.UsedRange 
      strWorksheetName = objWorksheet.Name &amp; &quot;!&quot; &amp; objRange.Address(False, False) 
      objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
          &quot;Vulnerability&quot;, strFileName, True, strWorksheetName
  Next
  </code></pre>
  <p>I have a problem with the range.  The variable strWorksheetName = &quot;BE900!A1:L1634&quot;.</p>
  <p>I get a runtime-error '3011'.  The &quot;!&quot; is replaced by &quot;$&quot; so the sheet isn't found.</p>
  <p>Any ideas?</p>
  <p><a href="https://i.stack.imgur.com/i1i8y.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/i1i8y.png" alt="enter image description here" /></a></p>
  <h1>ALL of my code</h1>
  <p>Public Function ImportFiles()
  Dim strFolder As String
  Dim db As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim strFile As String
  Dim strTable As String
  Dim strExtension As String
  Dim lngFileType As Long
  Dim strSQL As String
  Dim strFullFileName As String
  Dim varPieces As Variant</p>
  <pre><code>With Application.FileDialog(3) ' msoFileDialogFolderPicker
  .AllowMultiSelect = True
  .Title = &quot;Please select one or more files&quot;
  .Initialfilename = &quot;*.xls*&quot;
  
  If .Show Then
      strFullFileName = .SelectedItems(1)
  Else
      MsgBox &quot;No folder specified!&quot;, vbCritical
      Exit Function
  End If
  End With
  
  strFile = Dir(strFolder)
  
  Set db = CurrentDb()
  
  strFile = Dir(strFolder &amp; &quot;*.xls*&quot;)
  Do While Len(strFile) &gt; 0
  strTable = DetermineTable(strFile)
  
  strSQL = &quot;UPDATE [&quot; &amp; strTable &amp; &quot;] SET FileName=[pFileName]&quot; &amp; vbCrLf &amp; _
  &quot;WHERE FileName Is Null OR FileName='';&quot;
  Set qdf = db.CreateQueryDef(vbNullString, strSQL)
  
  varPieces = Split(strFile, &quot;.&quot;)
  strExtension = varPieces(UBound(varPieces))
  Select Case strExtension
      Case &quot;xls&quot;
          lngFileType = acSpreadsheetTypeExcel9
      Case &quot;xlsx&quot;, &quot;xlsm&quot;
          lngFileType = acSpreadsheetTypeExcel12Xml
      Case &quot;xlsb&quot;
          lngFileType = acSpreadsheetTypeExcel12
  End Select
  
  Set objexcel = CreateObject(&quot;Excel.Application&quot;)
  Set objworkbook = objexcel.Workbooks.Open(strFullFileName)
  Set colworksheets = objworkbook.Worksheets
  
  For Each objWorksheet In colworksheets
      Set objRange = objWorksheet.UsedRange
      **strWorksheetName = objWorksheet.Name &amp; &quot;!&quot; &amp; objRange.Address(False, False)**
      'strWorksheetName = objRange.Address(0, 0, external:=True)
          DoCmd.TransferSpreadsheet _
                  TransferType:=acImport, _
                  SpreadsheetType:=lngFileType, _
                  tableName:=strTable, _
                  FileName:=strFile, _
                  HasFieldNames:=False, _
                  **Range:=CStr(strWorksheetName)**
  Next
  
  colworksheets.Close
  colworksheets = Nothing
  objworkbook.Close
  objworkbook = Nothing
  objexcel.Close
  objexcel = Nothing
  
  Set db = CurrentDb()
  Set tdf = db.TableDefs(strTable)
  
  'Add the field to the table.
  If FieldExistsInTable(strTable, &quot;FileName&quot;) = True Then
      'Do nothing
  Else
      tdf.Fields.Append tdf.CreateField(&quot;FileName&quot;, dbText, 255)
      'tdf.fields.append tdf.createField(&quot;SheetName&quot;, dbText, 255)
  End If
  
  'Supply the parameter value for the UPDATE and execute it ...
  qdf.Parameters(&quot;pFileName&quot;).Value = strFile
  qdf.Execute 'dbFailOnError
  
  'Move to the next file
  strFile = Dir
   Loop
  
  Set fld = Nothing
  Set tdf = Nothing
  Set db = Nothing
  'rstTable.Close
  Set rstTable = Nothing
  
  End Function
  </code></pre>