★ExcelVBA ～ QueryTableオブジェクトをすぐに使えるようにするために、ダミーxlsファイルでQueryTableオブジェクトを作っておくプログラム

★ExcelVBA ～ QueryTableオブジェクトをすぐに使えるようにするために、ダミーxlsファイルでQueryTableオブジェクトを作っておくプログラム
　　
※まだ書きかけです。すみません。
※間違ってたらすみません。
※メモ書きなので、自分でも意味不明な箇所も多いです。ごめんなさい。

あらかじめ、以下のファイルを作っておけばいいと思います。
ひな型として。
（特に会社がVBAを許可してくれないとき。ただ、xlsxファイルであっても、クエリを作ったのちにVBAコードを消しさえしてしまえば、「クエリを生成してxlsxファイルを保存可能」です。なので、そこのところまではできる会社も多いかも・・・。）

あとは毎回、それを（ブック丸ごとかシートを）コピーして使うとかでOKだと思います。
（ダミーのxlsxまたはxlsは削除しないほうが変な挙動が少ないのでそのほうがいいです）
========================

今回の事例では、
あらかじめ、「具」という列だけの「クエリダミー用－★捨てないで.xlsx」を
「D:\1」フォルダに置いておきます。
（xlsでもOK。その場合はコード内の拡張子もxlsxからxlsに変えます。）

それを使って、ダミーのQueryTableオブジェクトを作成します。
具の列には２、３行、適当に具材の名前を入れておきます。

そうやっていったん、ダミーのQueryTableオブジェクトを作成してしまえば、
あとは、ダミーのQueryTableオブジェクトを右クリックして
「クエリの編集」で、Accessクエリのように操作できる。
「必要なシートを全て用意した」、そういうxlsやxlsxを指定し直せるから。

ListObjectオブジェクト（QueryTableオブジェクトを含む）、
つまり、「Excelにおいての”テーブル機能”」だと、
段階的に（ネスト的に）かけていくとすぐエラーになってしまうけど、
QueryTableオブジェクトだと何段階でもエラーにならないから
QueryTableオブジェクトをあえて使います。
パワークエリのようなことが、シートを見て確認しながらできます。

ただし、3万行以下のデータくらいにしか使えないです。
できれば1万行以下。
でもそれでも中小零細の場合はかなり使えると思います。
SQLを使うので、パワークエリやVBAよりは属人化しないかも？

名前定義はExcelが勝手に自動生成するので競合しないようになっています。
その部分でのエラーは出ないと思います。

なお、QueryTableオブジェクトを消す時は、列丸ごとで消すこと。
ただし、勝手に自動生成された名前定義は消えないので手動消す必要があります。
不要な名前定義は「REF!」のようなエラー的な文言が表示されているので
それを目印に消せばいいと思います。
（別シートだど同名の名前定義が作れるっぽいです。）

個人用マクロブックかアドインに仕込んでもいいと思います。
常に使いたいので。（いずれも会社がVBAを許可してくれていれば、ですが。）

'
'
'あらかじめ、以下のファイルを作っておけばいいと思います。
'ひな型として。
'（特に会社がVBAを許可してくれないとき）
'
'あとは毎回、それを（ブック丸ごとかシートを）コピーして使うとかでOKだと思います。
'（ダミーのxlsxまたはxlsは削除しないほうが変な挙動が少ないのでそのほうがいいです）
'========================
'
'あらかじめ、「具」という列だけの「クエリダミー用－★捨てないで.xlsx」を
'「D:\1」フォルダに置いておきます。
'（xlsでもOK。その場合はコード内の拡張子もxlsxからxlsに変えます。）
'
'それを使って、ダミーのQueryTableオブジェクトを作成します。
'具の列には２、３行、適当に具材の名前を入れておきます。
'
'そうやっていったん、ダミーのQueryTableオブジェクトを作成してしまえば、
'あとは、ダミーのQueryTableオブジェクトを右クリックして
'「クエリの編集」で、Accessクエリのように操作できる。
'「必要なシートを全て用意した」、そういうxlsやxlsxを指定し直せるから。
'
'ListObjectオブジェクト（QueryTableオブジェクトを含む）、
'つまり、「Excelにおいての”テーブル機能”」だと、
'段階的に（ネスト的に）かけていくとすぐエラーになってしまうけど、
'QueryTableオブジェクトだと何段階でもエラーにならないから
'QueryTableオブジェクトをあえて使います。
'パワークエリのようなことが、シートを見て確認しながらできます。
'
'ただし、3万行以下のデータくらいにしか使えないです。
'できれば1万行以下。
'でもそれでも中小零細の場合はかなり使えると思います。
'SQLを使うので、パワークエリやVBAよりは属人化しないかも？
'
'名前定義はExcelが勝手に自動生成するので競合しないようになっています。
'その部分でのエラーは出ないと思います。
'
'なお、QueryTableオブジェクトを消す時は、列丸ごとで消すこと。
'ただし、勝手に自動生成された名前定義は消えないので手動消す必要があります。
'不要な名前定義は「REF!」のようなエラー的な文言が表示されているので
'それを目印に消せばいいと思います。
'（別シートだど同名の名前定義が作れるっぽいです。）
'
'個人用マクロブックかアドインに仕込んでもいいと思います。
'常に使いたいので。（いずれも会社がVBAを許可してくれていれば、ですが。）



Sub SimpleDummyQTmake()

    Dim qt              As QueryTable
    Dim s_DirPath01     As String
    Dim s_FileName01    As String
    Dim s_FullPath01    As String
    Dim s_TrgWsName     As String
    Dim s_TrgCelAddr    As String
    Dim s_SQLStr        As String
    
    Let s_DirPath01 = "D:\1"     'フォルダパスの指定（基本・変更不要。常に5.xlsのようなファイルが在れば。）
    Let s_FileName01 = "クエリダミー用－★捨てないで.xlsx"   'ファイル名の指定（基本・変更不要。常に5.xlsのようなファイルが在れば。）
    
    Let s_FullPath01 = s_DirPath01 & "\" & s_FileName01
    
    Let s_TrgWsName = "Sheet1"   '（★要変更）QueryTableオブジェクト出力先シート名の指定
    Let s_TrgCelAddr = "A1"      '（★要変更）QueryTableオブジェクト出力先セル位置の指定
    
    Let s_SQLStr = "SELECT * FROM [Sheet1$]"  'SQL文（基本・変更不要）

    
    Set qt = Worksheets(s_TrgWsName).QueryTables.Add( _
        Connection:= _
            "ODBC;DSN=Excel Files;" & _
            "DBQ=" & s_FullPath01 & ";" & _
            "DefaultDir=" & s_DirPath01 & ";" & _
            "DriverId=1046;" & _
            "MaxBufferSize=2048;" & _
            "PageTimeout=5;", _
        Destination:=Worksheets(s_TrgWsName).Range(s_TrgCelAddr))
    
    With qt
        .CommandText = Array(s_SQLStr)
        .Name = "QT1"
        .BackgroundQuery = False
        .Refresh
    End With

End Sub
'
'

'あらかじめ、以下のファイルを作っておけばいいと思います。

'ひな型として。

'（特に会社がVBAを許可してくれないとき）

'あとは毎回、それを（ブック丸ごとかシートを）コピーして使うとかでOKだと思います。

'（ダミーのxlsxまたはxlsは削除しないほうが変な挙動が少ないのでそのほうがいいです）

'========================

'あらかじめ、「具」という列だけの「クエリダミー用－★捨てないで.xlsx」を

'「D:\1」フォルダに置いておきます。

'（xlsでもOK。その場合はコード内の拡張子もxlsxからxlsに変えます。）

'それを使って、ダミーのQueryTableオブジェクトを作成します。

'具の列には２、３行、適当に具材の名前を入れておきます。

'そうやっていったん、ダミーのQueryTableオブジェクトを作成してしまえば、

'あとは、ダミーのQueryTableオブジェクトを右クリックして

'「クエリの編集」で、Accessクエリのように操作できる。

'「必要なシートを全て用意した」、そういうxlsやxlsxを指定し直せるから。

'ListObjectオブジェクト（QueryTableオブジェクトを含む）、

'つまり、「Excelにおいての”テーブル機能”」だと、

'段階的に（ネスト的に）かけていくとすぐエラーになってしまうけど、

'QueryTableオブジェクトだと何段階でもエラーにならないから

'QueryTableオブジェクトをあえて使います。

'パワークエリのようなことが、シートを見て確認しながらできます。

'ただし、3万行以下のデータくらいにしか使えないです。

'できれば1万行以下。

'でもそれでも中小零細の場合はかなり使えると思います。

'SQLを使うので、パワークエリやVBAよりは属人化しないかも？

'名前定義はExcelが勝手に自動生成するので競合しないようになっています。

'その部分でのエラーは出ないと思います。

'なお、QueryTableオブジェクトを消す時は、列丸ごとで消すこと。

'ただし、勝手に自動生成された名前定義は消えないので手動消す必要があります。

'不要な名前定義は「REF!」のようなエラー的な文言が表示されているので

'それを目印に消せばいいと思います。

'（別シートだど同名の名前定義が作れるっぽいです。）

'個人用マクロブックかアドインに仕込んでもいいと思います。

'常に使いたいので。（いずれも会社がVBAを許可してくれていれば、ですが。）

Sub SimpleDummyQTmake()

Dim qt As QueryTable

Dim s_DirPath01 As String

Dim s_FileName01 As String

Dim s_FullPath01 As String

Dim s_TrgWsName As String

Dim s_TrgCelAddr As String

Dim s_SQLStr As String

Let s_DirPath01 = "D:\1" 'フォルダパスの指定（基本・変更不要。常に5.xlsのようなファイルが在れば。）

Let s_FileName01 = "クエリダミー用－★捨てないで.xlsx" 'ファイル名の指定（基本・変更不要。常に5.xlsのようなファイルが在れば。）

Let s_FullPath01 = s_DirPath01 & "\" & s_FileName01

Let s_TrgWsName = "Sheet1" '（★要変更）QueryTableオブジェクト出力先シート名の指定

Let s_TrgCelAddr = "A1" '（★要変更）QueryTableオブジェクト出力先セル位置の指定

Let s_SQLStr = "SELECT * FROM [Sheet1$]" 'SQL文（基本・変更不要）

Set qt = Worksheets(s_TrgWsName).QueryTables.Add( _

Connection:= _

"ODBC;DSN=Excel Files;" & _

"DBQ=" & s_FullPath01 & ";" & _

"DefaultDir=" & s_DirPath01 & ";" & _

"DriverId=1046;" & _

"MaxBufferSize=2048;" & _

"PageTimeout=5;", _

Destination:=Worksheets(s_TrgWsName).Range(s_TrgCelAddr))

With qt

.CommandText = Array(s_SQLStr)

.Name = "QT1"

.BackgroundQuery = False

.Refresh

End With

End Sub

MENU

関連記事

最近の投稿

カテゴリー

MENU

★ExcelVBA ～ QueryTableオブジェクトをすぐに使えるようにするために、ダミーxlsファイルでQueryTableオブジェクトを作っておくプログラム

関連記事

最近の投稿

カテゴリー

タグ