CODEWITHSUNDEEP
excelms-accessvbams-jet-ace
Jacek Kotowski
Jacek Kotowski

Reputation: 704

ACE OLEDB SQL string in VBA - how to refer to Excel object Table (ctrl+t, former list) in SQL string

My code successfuly reads tabular data from a workseet with data starting from cell A1. A fragment of my code looks like this:

Myconnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                          "Data Source=" & Myworkbook & ";" & _
                          "Extended Properties=Excel 8.0;" & _
                          "Persist Security Info=False"

    'Build SQL Statement


    strSQL = "SELECT  a as kod, b as wartosc, [Descr] as opis_grupy FROM [TableA$] " & _

Where [TableA$] must be an worksheet "TableA" filled with data starting from cell A1.

Is it possible, is there a syntax, that would allow me to refer not to data in a workseet TableA but in an object Table with a name TableA (in MSExcell 2010 one can create with ctrl+t), formerly known as list (in MSExcel2003)?

EDIT: so far I am overcoming the problem by using this function to build a reference to a hard coded range from a reference to table:

Function getAddress()

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

    getAddress = myAddress

End Function

Upvotes: 0

Views: 669

Answers (0)

Related Questions