title: MS Access VBA acImport sheet name with spaces and range
body: <p>I'm building a form with a button called "Go" to import a specific range of a specific excel sheet from an excel file. Another sub/function takes care of having the file name on the form in the field called text_filepathdata.</p>
  
  <p>I've discovered that spaces in the sheet names are problematic, however it can be resolved by using special quotation. </p>
  
  <pre><code>Dim sheetname As String
  sheetname = "'Commit Volumes!'"
  DoCmd.Transferspreadsheet acImport, acSpreadsheetTypeExcel12, _
      "lcl_ImportedFile", text_filepathdata, True, sheetname
  </code></pre>
  
  <p>That works, but I can't specify a range in addition to the sheet name because of the special quotation... (ie. "'Commit Volumes!A6:Z5000'" doesn't work and neither does a concatenation of the two pieces like "'Commit Volumes'" &amp; "A6:Z5000")</p>
  
  <p>I've tried all manner of quotations and concatenations, but the only things that work are:</p>
  
  <pre><code>sheetname = "'Commit Volumes!'"
  sheetname = "'Commit Volumes$'"
  sheetname = "CommitVolumes!A6:Z5000"
  </code></pre>
  
  <p>The excel sheet cannot be modified because it comes from another system and the file (after being updated) must be uploaded back to that system. </p>
  
  <p>Here is my code:</p>
  
  <pre><code>Private Sub button_Go_Click()
  On Error GoTo Err_button_Go_Click
  
  Dim sheetname As String
  sheetname = "'Commit Volumes!'"
  
  If IsNull(text_filepathdata) Or text_filepathdata = "" Then
      MsgBox "Please browse and select a valid file to import.", _
          vbCritical, "Invalid File"
  Else
      DoCmd.OpenForm "f_PleaseWait_Processing", acNormal, , , acFormReadOnly, acWindowNormal
      If ifTableExists("lcl_ImportedFile") = True Then
          DoCmd.DeleteObject acTable, "lcl_ImportedFile"
      End If
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,_
          "lcl_ImportedFile", text_filepathdata, True, sheetname
  
  'Call module to process the imported data
          Call MainProcess
      End If
  
  Exit_button_Go_Click:
      Exit Sub
  
  Err_button_Go_Click:
      MsgBox Err.Number &amp; " - " &amp; Err.Description
      Resume Exit_button_Go_Click
  
  End Sub
  </code></pre>
  
  <p>I get two different error messages depending on the issue. </p>
  
  <p>If there's a space in the name I get: Run-time error 3129: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.</p>
  
  <p>If there's a problem with the name otherwise I get: Run-time error 3011: The Microsoft Access database engine could not find the object "Commit Volumes$A1:Z5000". Make sure the object exists and that you spell its name and the path correctly. If "Commit Volumes$A1:Z5000" is not a local object, check your network connection or contact the server administrator.</p>
  
  <p>Any help would be greatly appreciated!</p>
  
  <p>Bill</p>
  
  <p>Variations I've Tried: </p>
  
  <pre><code>sheetname = "'Commit Volumes'!A6:Z5000"; 
  sheetname = "'Commit Volumes!'A6:Z5000" 
  sheetname = "'Commit Volumes$'A6:Z5000" 
  sheetname = "'Commit Volumes'$A6:Z5000" 
  sheetname = "'Commit Volumes'$A$6:$Z$5000" 
  sheetname = "'Commit Volumes!'$A$6:$Z$5000" 
  sheetname = "'Commit Volumes$'$A$6:$Z$5000"
  </code></pre>