Reference named range in closed workbook

[metricsinstitute]

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

 

[GS]

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

 

[isabelle]

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, (e-mail address removed) a écrit :

 

[metricsinstitute]

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

 

[GS]

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

 

[metricsinstitute]

Thanks I'll explore it

 

[isabelle]

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, (e-mail address removed) a écrit :

 

[metricsinstitute]

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

Avi

 

[metricsinstitute]

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?

 

[metricsinstitute]

Looks as I found the solution for my own question

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

Avi

 

[GS]

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

 

[metricsinstitute]

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

 

[GS]

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

 

[metricsinstitute]

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

 

[GS]

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]

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

 

[metricsinstitute]

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)$A1410]", 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

 

[GS]

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