de-vraag
Lebih
Question

Melakukan kueri SQL pada Tabel Excel dalam Buku Kerja dengan Makro VBA

Saya mencoba membuat makro excel yang akan memberi saya fungsi berikut di Excel:

=SQL("SELECT heading_1 FROM Table1 WHERE heading_2='foo'")

Memungkinkan saya untuk mencari (dan bahkan mungkin memasukkan) data di Tabel Workbook's saya menggunakan kueri SQL.

Inilah yang telah saya lakukan sejauh ini:

Sub SQL()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT * FROM [Sheet1$A1:G3]"

rs.Open strSQL, cn

Debug.Print rs.GetString

End Sub

Skrip saya bekerja seperti pesona dengan rentang yang dikodekan dengan keras seperti yang ada di cuplikan di atas. Ini juga bekerja sangat baik dengan rentang bernama statis.

Namun, ini tidak akan bekerja dengan rentang bernama dinamis atau NAMA TABEL yang paling penting bagi saya.

Jawaban terdekat yang saya temukan adalah orang ini yang menderita penderitaan yang sama: http://www.ozgrid.com/forum/showthread.php?t=72973

Ada yang bisa membantu?

Edit

Saya telah memasak ini sejauh ini, saya kemudian dapat menggunakan nama yang dihasilkan dalam kueri SQL saya. Keterbatasannya adalah saya perlu tahu di sheet mana tabel-tabel tersebut berada. Bisakah kita melakukan sesuatu tentang itu?

Function getAddress()

    myAddress = Replace(Sheets("Sheet1").Range("Table1").address, "$", "")
    myAddress = "[Sheet1$" & myAddress & "]"

    getAddress = myAddress

End Function

Terima kasih!

Joan-Diego Rodriguez
Joan-Diego Rodriguez
Pertanyaan edit 26 Februari 2017 в 6:48
Pemrograman
vba
sql
excel
excel-vba
Pertanyaan ini memiliki :value jawaban dalam bahasa Inggris, untuk membacanya masuk ke akun Anda.
Solution / Answer

Satu hal yang mungkin bisa Anda lakukan adalah mendapatkan alamat rentang bernama dinamis, dan menggunakannya sebagai input dalam string SQL Anda. Sesuatu seperti:

Sheets("shtName").range("namedRangeName").Address

Yang akan mengeluarkan string alamat, sesuatu seperti $A$1:$A$8

Edit:

Seperti yang saya katakan dalam komentar saya di bawah ini, Anda bisa secara dinamis mendapatkan alamat lengkap (termasuk nama sheet) dan menggunakannya secara langsung atau mengurai nama sheet untuk digunakan nanti:

ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal

Yang menghasilkan string seperti =Sheet1!$C$1:$C$4. Jadi untuk contoh kode Anda di atas, pernyataan SQL Anda bisa berupa

strRangeAddress = Mid(ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal,2)

strSQL = "SELECT * FROM [strRangeAddress]"
Jake Bathman
Jake Bathman
Jawaban edit 4 November 2013 в 3:26
14 
Public Function GetRange(ByVal sListName As String) As String

Dim oListObject As ListObject
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook

For Each ws In wb.Sheets
    For Each oListObject In ws.ListObjects
        If oListObject.Name = sListName Then
            GetRange = "[" & ws.Name & "$" & Replace(oListObject.Range.Address, "$", "") & "]"
        Exit Function
        End If
    Next oListObject
Next ws

End Function

Dalam SQL Anda, gunakan seperti ini

sSQL = "Select * from " & GetRange("NameOfTable") & ""
8

Saya seorang pemula yang mengutak-atik kode orang lain, jadi mohon maaf dan perbaiki kesalahan saya lebih lanjut. Saya mencoba kode Anda dan bermain dengan bantuan VBA Berikut ini bekerja dengan saya:

Function currAddressTest(dataRangeTest As Range) As String

    currAddressTest = ActiveSheet.Name & "$" & dataRangeTest.Address(False, False)

End Function

Ketika saya memilih argumen sumber data untuk fungsi saya, itu berubah menjadi format Sheet1 $ A1: G3. Jika excel mengubahnya menjadi referensi Table1[#All] dalam rumus saya, fungsinya masih berfungsi dengan baik

Saya kemudian menggunakannya di fungsi Anda (mencoba bermain dan menambahkan argumen lain untuk diinjeksi ke WHERE...

Function SQL(dataRange As Range, CritA As String)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim currAddress As String

currAddress = ActiveSheet.Name & "$" & dataRange.Address(False, False)

strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT * FROM [" & currAddress & "]" & _
         "WHERE [A] =  '" & CritA & "'  " & _
         "ORDER BY 1 ASC"

rs.Open strSQL, cn

SQL = rs.GetString

End Function

Semoga fungsi Anda berkembang lebih jauh, saya merasa sangat berguna. Semoga harimu menyenangkan!

2