挙動

・コネクションは普通に

    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    cn.Execute "Provider=Microsoft.ACE.OLEDB.12.0;" & _
               "Data Source=[fullpath];" & _
               "Extended Properties=""Excel 12.0;HDR=Yes;"""

・存在しないシートにテーブルは作成できない（シートは自動挿入されない。後ろに$を付けても同じ）

'「オブジェクト 'Sheet2' が見つかりませんでした...」エラーが返る
    cn.Execute "CREATE TABLE [Sheet2] (fieldname datatype)"

・存在するシートでも、後ろに$を付けないとシートとして認識しない

'「オブジェクト 'Sheet1' が見つかりませんでした...」エラーが返る
    cn.Execute "CREATE TABLE [Sheet1] (fieldname datatype)"

・シート名に$を付けたのみの場合、A1セルからフィールドが作成される

    cn.Execute "CREATE TABLE [Sheet1$] (test LONG)"

・新規のシートの場合、SQL文からはフィールドを1つしか定義できない

'「定義されているフィールドが多過ぎます」エラーが返る
    cn.Execute "CREATE TABLE [Sheet1$] (foo LONG, bar VARCHAR)"

・これは、$の後にレンジを指定しても回避できない

'やはり「定義されているフィールドが多過ぎます」エラーが返る
    cn.Execute "CREATE TABLE [Sheet1$A1:B1] (foo LONG, bar VARCHAR)"

・フィールド名をシートに予め入力しておくと、SQL文で複数フィールドを定義しても通る

'通る
    cn.Execute "CREATE TABLE [Sheet1$A1:C1] (foo LONG, bar VARCHAR, hoge DATETIME)"

・フィールド名を事前入力すれば、テーブルの場所は自由

'通る
    cn.Execute "CREATE TABLE [Sheet1$C5:D5] (poyo LONG, peyo VARCHAR)"

・一旦定義されたフィールド領域は記憶され、全ての領域の "上下の端" と "左右の端" を含むレンジ全ての範囲（上の例ではA1:D5）で、フィールド名の事前入力なしにSQL文からフィールド定義できるようになる（というか、定義できてしまうので混乱する）

'通る
    cn.Execute "CREATE TABLE [Sheet1$B3:D3] (a LONG, b VARCHAR, c VARCHAR)"

補足

SQL文に記述するシートのストリングは、シートのオブジェクト名ではなくNameプロパティ。
上の例では全てレンジを1行（＝フィールド名の行のみ）としたが、レコード領域を想定した複数行を設定することも可能。ただしその場合でも、記憶される領域（正確には、記憶される領域の基準点となり得るセル範囲）はフィールド名のセルのみ。
記憶されるのはUsedRange的な四角い領域だが、ワークシート上の入力をクリアしても領域が変わることはない。
Microsoft Office Professional 2016 Excelバージョン2006での動作。

EXCEL VBAでワークシートにCREATE TABLE（ADO）

挙動

補足