Re: Ron de Bruin's code "Copy a range from all files in a folder and subfolders (optional)" ???

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Read the notes on the page Mark

Important info about the ADO examples

1) The code in the workbook is working in Excel 2000-2007.

2) In a Database you cannot mix data types, a column must be all numbers or all text. If there
are different data types in the column ADO will copy only the Data type that have the majority.

3) If you want to copy only one cell from each workbook then use A3:A3 and not A3 in the code.



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Mark Ivey" <wmivey6311@xxxxxxxxxxx> wrote in message news:8F9BCD5F-8C19-4EB0-A0F9-0FB1CBFD9C14@xxxxxxxxxxxxxxxx
Ron,

Thanks again for your help on this item...

I do have one question for you.

I am actually copying three individual cells using your code. But if I use a single cell reference as a Range in your code, it will not pull any data.

Example: GetData MyFiles(Fnum), "Sheet 1", "C5", destrange, False, False

It has to be listed like this:

Example 2: GetData MyFiles(Fnum), "Sheet 1", "C5:C5", destrange, False, False

I don't mind using it this way, but I was under the impression you could specify a range using a single cell reference. It must have something to do with the next portion of code that deals with the ADO connection. I am not sure.

Here is a snippet of how I am using your code:

1st I added the new variables

Dim sh As Worksheet, destrange As Range, destrange2 As Range, destrange3 As Range

Then I changed up the reference points to get just what I needed to build a summary page:

For Fnum = LBound(MyFiles) To UBound(MyFiles)

'Find the last row with data
rnum = LastRow(sh)

'create the destination cell address
Set destrange = sh.Cells(rnum + 1, "B")
Set destrange2 = sh.Cells(rnum + 1, "C")
Set destrange3 = sh.Cells(rnum + 1, "A")

'Get the cell values and copy it in the destrange
'Change the Sheet name and range as you like
GetData MyFiles(Fnum), "Sheet 1", "C5:C5", destrange, False, False
GetData MyFiles(Fnum), "Sheet 1", "G25:G25", destrange2, False, False
GetData MyFiles(Fnum), "Sheet 1", "E3:E3", destrange3, False, False

Next


Just curious....


Mark Ivey

.

Relevant Pages

  • Re: Ron de Bruins code "Copy a range from all files in a folder and subfolders (optional)" ???
    ... In a Database you cannot mix data types, a column must be all numbers or all text. ... are different data types in the column ADO will copy only the Data type that have the majority. ... If you want to copy only one cell from each workbook then use A3:A3 and not A3 in the code. ... Dim sh As Worksheet, destrange As Range, destrange2 As Range, destrange3 As Range ...
    (microsoft.public.excel.programming)
  • Re: Data Mix in ADO recordset column.
    ... > I am pulling out data from excel thorugh ADO recordset object. ... If even one cell in that column contains ... > cell contents in that column have correct numeric data. ...
    (microsoft.public.data.ado)
  • Re: Dictionary, hashing limitations
    ... It gives you a collection of words written in Forth, in areas such as data types, collections, development support, interfaces and compound modules. ... acn - AVL binary tree cell node module ... dci - Double Linked Cell List Iterator ...
    (comp.lang.forth)
  • Re: ADODB.Recordset from workbooks : numbers only?
    ... ADO is basically treating you Excel sheet as a table in a database. ... , formula and cell references have no meaning, only the result in each cell. ... Set rsData = New ADODB.Recordset ...
    (microsoft.public.excel.programming)
  • Re: Luckily Im bald, otherwise Id be pulling all my hair out! Please help.
    ... cell to string.Empty. ... > There has got to be a good strategy for dealing with null values in C#, ... > I'm mainly concerned with allowing nulls in DateTime and Int data types. ... > I've tried the nullable types on this web site ...
    (microsoft.public.dotnet.languages.csharp)