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!
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]"
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") & ""
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!