Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default Reference named range in closed workbook

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Reference named range in closed workbook

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/Da...rogramming.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


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default Reference named range in closed workbook

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

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default Reference named range in closed workbook


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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Reference named range in closed workbook

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




  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default Reference named range in closed workbook

Thanks I'll explore it
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default Reference named range in closed workbook

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

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default Reference named range in closed workbook

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

Avi
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default Reference named range in closed workbook

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?
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default Reference named range in closed workbook


Looks as I found the solution for my own question

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

Avi


  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Reference named range in closed workbook

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


  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default Reference named range in closed workbook

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
  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Reference named range in closed workbook

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


  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default Reference named range in closed workbook


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
  #15   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Reference named range in closed workbook

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




  #16   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Reference named range in closed workbook

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


  #17   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default Reference named range in closed workbook

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
  #18   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Reference named range in closed workbook

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


  #19   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default Reference named range in closed workbook

On Tuesday, April 22, 2014 10:18:17 PM UTC+3, isabelle wrote:
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




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 words, I can't know its range address...

Any idea?
Thanks a lot
Avi



  #20   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default Reference named range in closed workbook

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, 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

words, I can't know its range address...
Any idea?
Thanks a lot
Avi




  #21   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default Reference named range in closed workbook

sorry i forgot to declare

Dim i As Integer, n As Integer, rw As Long
Dim sSheetName As ADOX.Table

isabelle

  #22   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Reference named range in closed workbook

Isabelle,
If you only knew how much easier this would be using arrays, you might
never use ADODB for workbooks or text files again!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #23   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default Reference named range in closed workbook

hi Gary,

also if you do not know in advance the name of the named ranges in the workbook?
but despite this problem i am always open to learn more, please tell me

isabelle

Le 2014-05-17 02:08, GS a écrit :
Isabelle,
If you only knew how much easier this would be using arrays, you might never use
ADODB for workbooks or text files again!

  #24   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Reference named range in closed workbook

hi Gary,

also if you do not know in advance the name of the named ranges in
the workbook?


This is also true for table names since named ranges are considered
tables when using ADODB!

but despite this problem i am always open to learn more, please tell
me


The point of using ADODB with workbooks or text files is so the file[s]
do not open in the *user's instance* of Excel. The 'short' story for
using arrays is...


[Retrieving data from a workbook]
You can dump data into arrays by using an automated instance of Excel
via CreateObject("Excel.Application"), then open the file to access the
data as desired. (This essentially duplicates what happens with ADODB's
'Open' statement)

Treat each array as a separate recordset, or build new arrays
(recordsets) by filtering or selecting fieldnames via
Application.Index(array, RowIndex, ColIndex). Filtering the table
(sheet or named range) allows dumping visible rows into an array.

To select individual records:

rs2 = Application.Index(rs1, RowIndex, 0)

To select individual fields:

rs2 = Application.Index(rs1, 0, ColIndex)

Note: As you know, dumping data from a worksheet always results a
1-based 2D array.


[Retrieving data from a text file]
Use standard VB file I/O functions to read/write data in text files. Of
course, it's important that the text file be properly configured as a
data table for this to work well. That means the first line must
contain only fieldnames, and the data must not be comma delimited
because record data often contains commas if 'Note' or 'Text' type. (I
use the pipe character)

Read the file into an array rs using rs0 = Split(TextIn, vbCrLf). This
results a 1D array where its UBound is the record count. You'll need to
loop to create a 2D array of the data as follows.

Dim vTmp, rs1(), n&, j&
Redim rs1(Ubound(rs0), UBound(Split(rs0(0), "|")))
For n = LBound(rs0) To UBound(rs0)
vTmp = Split(rs0(n), "|")
For j = LBound(vTmp) To UBound(vTmp)
rs1(n, j) = vTmp(j)
Next 'j
Next 'n

Note that this 2D array is 0-based. Reloading it from the worksheet
makes it 1-based! Your code should always use the 1-based 2D array
so the sheet/array data indexes are 'in sync'!

Dump the 2D array into a temp hidden worksheet for sorting/filtering.
You can now work with the data same way as described for workbooks.
Only difference is text file data won't have named ranges.

Note: It's a good idea to name the temp sheet and 'Set' a fully
qualified ref to it for use in your code.

Offline assistance is available if you want to post an email address.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA code to copy named range in closed workbook Phraedrique Excel Programming 2 June 19th 09 08:51 PM
Workbook named range not seen by ADO when workbook closed Tim Lund[_2_] Excel Programming 2 March 16th 07 09:59 AM
ADO - closed workbook - Named range MattShoreson[_16_] Excel Programming 2 August 31st 05 10:37 AM
referencing a named range from a closed workbook Gixxer_J_97[_2_] Excel Programming 4 April 8th 05 08:45 PM


All times are GMT +1. The time now is 02:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"