none
ADO syntax help required RRS feed

  • Question

  • I am using the following to access an Excel worksheet from a Word VBA userform. It does the job, but would appreciate some assistance on the syntax to limit the data area to a named range, which I presume is possible?

    Const strData As String = "C:\Path\WorkBookName.xlsx"
    Const sSheetSrc As String = "Sheet1"
        Set cn = New ADODB.Connection
        cn.Provider = "Microsoft OLE DB Provider for ODBC Drivers"
        cn.ConnectionString = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & strData
        cn.Open
        Set rs = New ADODB.Recordset
        rs.Open "SELECT * FROM [" & sSheetSrc & "$]", cn, 2, 1

    I asked a similar question in the Microsoft Answers Forums, but am stuck on this part of the problem.

    Thanks!


    Graham Mayor - Word MVP
    www.gmayor.com

    Friday, April 19, 2013 1:58 PM

Answers

  • The Answers forum came up trumps :)

    The answer for those following is to change

    Const  sSheetSrc = "RangeName"

    and

    rs.Open "SELECT * FROM [" & sSheetSrc & "]", cn, 2, 1


    Graham Mayor - Word MVP
    www.gmayor.com

    Friday, April 19, 2013 2:04 PM

All replies