Reference named range in closed workbook

Discussion in 'Excel Programming' started by metricsinstitute@gmail.com, Apr 22, 2014.

  1. Guest

    Hello,

    Is there a way to retrieve all the named ranges names in a CLOSED workbook then, picking one of them, copy its content to the clipboard?

    Remark: without opening the wbk...


    Thanks
    Avi
     
    , Apr 22, 2014
    #1
    1. Advertisements

  2. GS Guest

    > Hello,
    >
    > Is there a way to retrieve all the named ranges names in a CLOSED
    > workbook then, picking one of them, copy its content to the
    > clipboard?
    >
    > Remark: without opening the wbk...
    >
    >
    > Thanks
    > Avi


    You can do this via ADODB! Here's something that'll get you started...

    http://www.appspro.com/conference/DatabaseProgramming.zip

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 22, 2014
    #2
    1. Advertisements

  3. isabelle Guest

    hi,

    Excel 2007 +, OpenXML format xlsx and xlsm

    Sub Sheets_list_and_named_ranges_on_CLOSED_workbook()

    'Need to activate the Microsoft ADO Ext xx reference for DLL and Security
    'Need to activate the reference Microsoft ActiveX Data Objects xx Library

    Dim Cn As ADODB.Connection
    Dim oCat As ADOX.Catalog
    Dim oFile As String, Resultat As String
    Dim oSheet As ADOX.Table

    oFile = "C:\MyFile.xlsm"

    Set Cn = New ADODB.Connection
    Set oCat = New ADOX.Catalog

    '--- Connexion ---
    With Cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
    & oFile & ";Extended Properties=""Excel 12.0;HDR=YES;"""
    .Open
    End With

    Set oCat.ActiveConnection = Cn

    For Each oSheet In oCat.Tables
    Resultat = Resultat & oSheet.Name & vbCrLf
    Next

    MsgBox Resultat

    Set oSheet = Nothing
    Set oCat = Nothing
    Cn.Close
    Set Cn = Nothing
    End Sub

    isabelle


    Le 2014-04-22 07:35, a écrit :
    > Hello,
    >
    > Is there a way to retrieve all the named ranges names in a CLOSED workbook then, picking one of them, copy its content to the clipboard?
    >
    > Remark: without opening the wbk...
    >
    >
    > Thanks
    > Avi
    >
     
    isabelle, Apr 22, 2014
    #3
  4. Guest

    Thanks

    It runs well but lists worksheets and named ranges

    How can I identify the named ranges only and more important, how can I copy the named range content to the clipboard?

    Avi
     
    , Apr 22, 2014
    #4
  5. GS Guest

    > Thanks
    >
    > It runs well but lists worksheets and named ranges
    >
    > How can I identify the named ranges only and more important, how can
    > I copy the named range content to the clipboard?
    >
    > Avi


    If you explore the samples in the download link I gave you, you'll see
    how to load the contents of a named range into a recordset. Once there
    you can do whatever you want with the data. Assuming you want to put it
    into the Clipboard for pasting somewhere, you can bypass that step and
    transfer the data directly to wherever...

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 22, 2014
    #5
  6. Guest

    Thanks I'll explore it
     
    , Apr 22, 2014
    #6
  7. isabelle Guest

    hi Avi,

    the sheets names is indicated by the symbol $, which is not the case of the
    named cells,
    so, you can replace:

    For Each oSheet In oCat.Tables
    Resultat = Resultat & oSheet.Name & vbCrLf
    Next

    by

    For Each oSheet In oCat.Tables
    If Not Right(oSheet.Name, 1) = "$" Then
    Resultat = Resultat & oSheet.Name & vbCrLf
    End If
    Next

    isabelle


    Le 2014-04-22 13:13, a écrit :
    >
    > Thanks
    >
    > It runs well but lists worksheets and named ranges
    >
    > How can I identify the named ranges only and more important, how can I copy the named range content to the clipboard?
    >
    > Avi
    >
     
    isabelle, Apr 22, 2014
    #7
  8. Guest

    Thanks all for introducing me to this ADO thing. Looks extremely promising and not so complicated as it seemed to me before

    Avi
     
    , Apr 23, 2014
    #8
  9. Guest

    Strange behavior with the provided sample sales.xls (or any other wbk)
    szSQL = "SELECT * FROM [Sales$A1:E19];"
    Set rsData = New ADODB.Recordset
    rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
    Sheet1.Range("A1").CopyFromRecordset rsData

    If a column contains both numerical and string values, only the numerical values are copied !!!

    Any idea?
     
    , Apr 23, 2014
    #9
  10. Guest

    Looks as I found the solution for my own question

    I have to use HDR=YES;IMEX=1"; in the connection string

    Avi
     
    , Apr 23, 2014
    #10
  11. GS Guest

    > Looks as I found the solution for my own question
    >
    > I have to use HDR=YES;IMEX=1"; in the connection string
    >
    > Avi


    Ok! Glad you were able to figure it out. It does take a bit of practice
    to catch on to the nuances related to working with text files and
    workbooks, being they both have/use different criteria args in their
    respective SQL statements. For this reason I use a wrapper function to
    return the data, which accepts 'sSource$' args "mdb", "wkb" or "txt"
    which uses a 'Select Case' construct to build the appropriate SQL
    statement. Also required as arg is 'sSelect$' which contains the table
    info for building the "SELECT FROM" part. I use it like this...

    <snip>
    Set RS = GetRS(sTable, "mdb") 'database file
    Set RS = GetRS(sTable, "wkb") 'Excel file
    Set RS = GetRS(sTable, "txt") 'text file
    </snip>

    The function also determines whether to use "Jet" or "Ace" depending on
    Excel version (though Jet is still working in all). This allows me to
    reuse the function for any number of recordsets I want to access during
    runtime without having to 'hard code' every time. Once you're familiar
    with how ADODB works you may want to build your own reusable function!

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 23, 2014
    #11
  12. Guest

    Thanks again

    Now I am facing a new annoying issue: numbers are copied as "number stored as text" format

    Is there a straightforward solution?

    BTW, where can I find the wrapper function and how is it used in VBA?

    Avi
     
    , Apr 23, 2014
    #12
  13. GS Guest

    > Thanks again
    >
    > Now I am facing a new annoying issue: numbers are copied as "number
    > stored as text" format
    >
    > Is there a straightforward solution?


    You'll need to format the fields (columns) containing numbers/dates as
    desired per your data layout on the target sheet.
    >
    > BTW, where can I find the wrapper function and how is it used in VBA?


    This is a 'make-your-own' thingy that you configure for how you (want
    to) work with data. I don't have an encapsulated wrapper because I
    rarely use ADODB, and when I do I use SQLite.

    The same principles apply, though, and so you should be able to do same
    for your VBA projects by putting everything ADODB related in a standard
    module that you can import into any project as needed. My earlier
    example of how to use such a function was a rather simplified sample of
    a more complex process which is generic so I can use it with VB6
    projects as well as VBA projects. This is stored in a text file from
    which I grab snippets on an as needed basis. (I primarily use '.dat'
    files for storing data so I can work with the standard VB[A] built-in
    I/O functions and arrays)

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 23, 2014
    #13
  14. Guest

    One more issue,please

    In the sample you provide, I use
    ' Query based on a sheet-level range name.
    'szSQL = "SELECT * FROM [EZZ$ddd];"

    where EZZ is the sheet name and ddd the sheet-level range name

    I get an error stating that the engine can't find the object EZZ$ddd

    No chance also with a workbook level name

    Thanks again for your very valuable help
     
    , Apr 24, 2014
    #14
  15. GS Guest

    > One more issue,please
    >
    > In the sample you provide, I use
    > ' Query based on a sheet-level range name.
    > 'szSQL = "SELECT * FROM [EZZ$ddd];"
    >
    > where EZZ is the sheet name and ddd the sheet-level range name
    >


    I'll have to review the example files but I think you may need to
    include the punctuation Excel requires for the ref...

    'Sheet1'!MyRange

    > I get an error stating that the engine can't find the object EZZ$ddd
    >
    > No chance also with a workbook level name


    I don't recommend using workbook level names unless absolutely
    unavoidable!

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 24, 2014
    #15
  16. GS Guest

    Ok.., I see that the exclamation character is not required but the
    apostrophes must be there if the sheetname has spaces...

    ['my sheet'$MyRange]

    ...otherwise...

    [MySheet$MyRange]

    ...should work. Perhaps there's something else causing it to fail and so
    can you post your entire code for the connection string + SQL
    statement?

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 24, 2014
    #16
  17. Guest

    Hello again,

    I'm completely new at using SQL from VB. I'm trying to merge 2 tables (I connect successfully to them) based on a common key. This is the code I am using but I naturally get errors. Could you help me getting to the right direction?

    Sub RefeWbk()
    Dim rsData As ADODB.Recordset
    'Create the connection string.
    Dim oConn As New ADODB.Connection
    oConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:\Dropbox\word\VlookupPlus\VlookupTestFile.xls;" & _
    "Extended Properties=""Excel 12.0;HDR=NO;IMEX=1"";"

    WhereTo = "A1" ' First cell in the range to paste to

    'Master table
    Set rsData = New ADODB.Recordset
    rsData.Open "Select * from [DatForSAS (5)$A1:T3100]", oConn, adOpenStatic

    'Lookup table
    Set LKdata = New ADODB.Recordset
    LKdata.Open "Select * from [ListFromPop (2)$A1:D410]", oConn, adOpenStatic

    'Merged table
    Dim rs As New ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open "SELECT rsData.a001, LKdata.MgrLevel", oConn, adOpenStatic 'Error!!!!

    'Merging query
    Dim cm As New ADODB.Command
    Set cm = New ADODB.Command
    With cm
    .ActiveConnection = oConn
    .CommandTimeout = 300
    .CommandType = adCmdText
    .CommandText = "From rsData" & "INNER JOIN LKdata" & "ON rsData.EMPnum=LKdata.EMPnum"
    .Execute 'Error!!!!
    End With

    ActiveSheet.Range(WhereTo).CopyFromRecordset rs

    End Sub

    Thanks for your help

    Avi
     
    , May 8, 2014
    #17
  18. GS Guest

    I'm surprised your code gets that far since the previous statements'
    sheet names have spaces and I don't see the apostrophes!

    I don't use ADODB very much, and when I do it's usually with SQLite. I
    only refered you to that because it's the least complex route to go. I
    mostly use built-in VBA file I/O functions and arrays for working with
    data in my projects. For example, I'd 'dump' the data from sheets in
    your "VlookupTestFile.xls" into separate arrays and go from there. I
    felt going this way with you might be too complex since it also
    requires a fairly deep understanding of VB arrays and how to
    'efficiently' work them.

    That said, once you get your code syntax corrected perhaps someone
    (like Isabelle or others) more familiar with the nuances of ADODB in
    VBA will chime in. Otherwise, you have to send me your files so I can
    step through the code to 'debug' it for you. (Hope there's no hurry,
    though!)

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, May 9, 2014
    #18
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Gixxer_J_97

    referencing a named range from a closed workbook

    Gixxer_J_97, Apr 8, 2005, in forum: Excel Programming
    Replies:
    4
    Views:
    175
    Gixxer_J_97
    Apr 8, 2005
  2. MattShoreson

    ADO - closed workbook - Named range

    MattShoreson, Jun 2, 2005, in forum: Excel Programming
    Replies:
    2
    Views:
    167
    Aonghus
    Aug 31, 2005
  3. JJ
    Replies:
    3
    Views:
    218
  4. Jac Tremblay

    Named range in a closed workbook

    Jac Tremblay, Feb 9, 2007, in forum: Excel Programming
    Replies:
    3
    Views:
    167
  5. Tim Lund
    Replies:
    2
    Views:
    174
    Tim Lund
    Mar 16, 2007
  6. Matt.Russett
    Replies:
    0
    Views:
    98
    Matt.Russett
    Mar 29, 2007
  7. Phraedrique

    VBA code to copy named range in closed workbook

    Phraedrique, Jun 17, 2009, in forum: Excel Programming
    Replies:
    2
    Views:
    198
    Phraedrique
    Jun 19, 2009
  8. Luc
    Replies:
    6
    Views:
    291
    michdenis
    Jan 13, 2010
Loading...