Reference named range in closed workbook

  • Thread starter Thread starter metricsinstitute
  • Start date Start date
sorry i forgot to declare

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

isabelle
 
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
 
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 :
 
hi Gary,
also if you do not know in advance the name of the named ranges in
the workbook?
Click to expand...

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

The point of using ADODB with workbooks or text files is so the file
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
 
Back
Top