Reference named range in closed workbook

M

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
 
G

GS

I

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 :
 
M

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
 
G

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
Click to expand...

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
 
I

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 :
 
M

metricsinstitute

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

Avi
 
M

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?
 
M

metricsinstitute

Looks as I found the solution for my own question

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

Avi
 
G

GS

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

Avi
Click to expand...

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
 
M

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
 
G

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?
Click to expand...

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?
Click to expand...

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
 
M

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
 
G

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
Click to expand...

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
Click to expand...

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
 
G

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
 
M

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)$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
 
G

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
 
M

metricsinstitute

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 :
Click to expand...

Hello again,

I was able to connect and retrieve the names, but the problem seems that the name has no use in closed workbook as I can't refer to it in any functionor in other words, I can't know its range address...

Any idea?
Thanks a lot
Avi
 
I

isabelle

hi Avi,

i put the range names in named_range () var
Dim named_range()

For Each sSheetName In oCat.Tables
If Not Right(sSheetName.Name, 1) = "$" Then
ReDim Preserve named_range(n)
named_range(n) = sSheetName.Name
n = n + 1
End If
Next

and i used that result in cn.Execute

For i = LBound(named_range) To UBound(named_range)
rw = Cells(65000, 1).End(xlUp).Row + 1
Set rs = cn.Execute("SELECT * FROM " & named_range(i))
Cells(rw + 1, 1) = named_range(i) ' modify destination cell
Cells(rw + 2, 1).CopyFromRecordset rs ' modify destination cell
Next



the full code:
'______________________________________________________________________
Sub copy_cells_of_named_range_from_closed_workbook()
'Need to activate the reference Microsoft ADO ext x.x for DLL and Security
'Need to activate the reference Microsoft ActiveX Data Objects x.x Library
Dim cn As ADODB.Connection
Dim oCat As ADOX.Catalog
Dim oFile As String, Resultat As String
Dim oSheet As ADOX.Table
Dim named_range()

oFile = "C:\MyFile.xlsm"

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

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 sSheetName In oCat.Tables
If Not Right(sSheetName.Name, 1) = "$" Then
ReDim Preserve named_range(n)
named_range(n) = sSheetName.Name
n = n + 1
End If
Next

For i = LBound(named_range) To UBound(named_range)
rw = Cells(65000, 1).End(xlUp).Row + 1
Set rs = cn.Execute("SELECT * FROM " & named_range(i))
Cells(rw + 1, 1) = named_range(i) ' modify destination cell
Cells(rw + 2, 1).CopyFromRecordset rs ' modify destination cell
Next

Set sSheetName = Nothing
Set oCat = Nothing
cn.Close
Set cn = Nothing
End Sub
'____________________________________________________________________
isabelle

Le 2014-05-15 11:33, (e-mail address removed) a écrit :
On Tuesday, April 22, 2014 10:18:17 PM UTC+3, isabelle wrote:
Hello again,
I was able to connect and retrieve the names, but the problem seems that the name
has no use in closed workbook as I can't refer to it in any function or in other
Click to expand...
words, I can't know its range address...
 

Ask a Question

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. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Named range in a closed workbook 3
Returning a named range source reference 3
Delete named ranges in VBA - by scope? 2
Reference a workbook level named variable. 8
Copying sheets without copying named ranges 1
VBA code to copy named range in closed workbook 2
Copy Range, Paste Value in New Workbook 2
Retrieving a range of values from a closed file 6

Top