Specifying a funny range 2

[WilliamMathias]

Hi,

I want to select a range of values from an excel spreadsheet.

At the moment I'm using:
[Nat Gas Download Data$C1:Z200]

but I only actually want columns C,D, F and Z

Is there a way of doing this?

Also I dont actually want all the rows, I just whant does where column A's value is after a certain date?

thanx

Will

 

[SkipVought]

Hi,
Try using AutoFilter to select the dates (custom filter value).
What are you trying to do with the selected range? There are ways to do what I think that you want to do, but just tell me. Skip,
metzgsk@voughtaircraft.com

 

[WilliamMathias]

Hi Skip,

I'm working from Access using an ADO link to Excel.

At the mo I can only select a square of data ( if u know what I mean)

I believe there are objects called "named ranges" that can permanently refer to a range. I'd ideally like to name a range that only specified the data I'm after, rather than the huge square of data thast I'm currently getting.

So, lets say I'm aftrer colum B, C and E (but not column D)

currently I'm using B1:F100

but I'd like to not get the data from Column D.

ie: B1:C100 & E1:E100


The actual data I'm selecting has 40 columns (of which I only requies 5)and 13,000 rows, so it's very slow if I select all the data.

thanx

 

[SkipVought]

You have told me THAT you want to select non-contiguous ranges, but you still have not told me WHAT you want to do with that data. Are you going to copy/paste the selection, evaluate each value within the selection, use the selection for lookup values...???

Help me out Skip,
metzgsk@voughtaircraft.com

 

[WilliamMathias]

Oh!!

I'm selecting the data to insert it into an ADO recorset and then place it into an Access table. From there it will be queried (SQL Select) to provide the data for a report.


Will

 

[SkipVought]

So you're going to take data from each row within the selected ranges to build the ado update recordset? Skip,
metzgsk@voughtaircraft.com

 

[WilliamMathias]

Ideally just a selection of rows, but if you can show how to name some columns, instead of all I'd be half way there.

If you have experience of SQL, what I'd really like to do is the following:

SELECT colA, colB, colD FROM sheet1 WHERE colA > earliestDateToGetDataFrom

but this kind of query doesn't seem to work (any where criteria seems to cause an error), so I've been reduced to the following:

Set rsData = New ADODB.Recordset

With rsData
.CursorLocation = adUseClient
.ActiveConnection = cnnActive
.LockType = adLockReadOnly
.Open "select * from " & strRange, cnnActive, adOpenForwardOnly ', adLockReadOnly
End With

where strRange is just a square range

ie "A1:r5"

 

[SkipVought]

Doing a query will get you a resultset that is usually stored and manipulated in an array. So here's a way to extract the data you want -- just modify to suite

Sub ExtractData()

'--what columns do you want?
'  assuming that your table starts in row1 col1
'  AND column headings are in row1
'  AND every column in the table has a heading value
    Dim iColHeading1 As Integer, iColHeading2 As Integer, iColHeading3 As Integer
    Dim rngHeadings As Range, rngHeading As Range
    Dim lRow As Long, iCol As Integer, lRowCount As Long, iColCount As Integer
    Dim vArray(), idx As Integer, i, j
    
    Set rngHeadings = Range(Cells(1, 1), Cells(1, 1).End(xlToRight))
    For Each rngHeading In rngHeadings
        With rngHeading
            Select Case .Value
                Case "Date"     'heading value from colA
                   iColHeading1 = .Column
                Case "Whatever" 'heading value from colB
                   iColHeading2 = .Column
                Case "Last"     'heading value from colD
                   iColHeading3 = .Column
            End With
        End With
    Next
'>>OK now we know what columns we are in
'  (incidently, we have just made it possible for you to insert a column ANYWHERE
'  and as long as the heading values don't change, the procedure will get the right data!!!

'--Now we want to process each row ind select the data you want
    With Cells(1, 1).CurrentRegion
        lRowCount = .Rows.Count
        iColCount = .Columns.Count
    End With
    idx = 1
    For lRow = 2 To lRowCount 
    '--here's where you decide if this row is one that you want
        If Cells(lRow, iColHeading1).Value = criterion Then
            'redim the array
            ReDim Preserve vArray(3, idx)
            'assign the data
            For iCol = 1 To iColCount
                Select Case iCol
                    Case iColHeading1
                        vArray(0, idx - 1) = Cells(lRow, iColHeading1).Value
                    Case icilheading2
                        vArray(1, idx - 1) = Cells(lRow, iColHeading2).Value
                    Case iColHeading3
                        vArray(2, idx - 1) = Cells(lRow, iColHeading3).Value
                        'don't need any mor columns -- get out!
                        Exit For
                End Select
            Next
            'incriment the index
            idx = idx + 1
        End If
    Next
'--Now you have all the data that you've selected in an array
'  and you can manipulate the array as follows
    For i = LBound(vArray, 2) To UBound(vArray, 2)
        For j = LBound(vArray, 1) To UBound(vArray, 1)
            x = vArray(j, i)
        Next
    Next
End Sub

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[WilliamMathias]

Hi Skip,

Yuor solution would work exellently, if I were workinmg from within Excel, but without using OLE Automation (which is v slow and awkward) or running the macro remotely, I can't run code in excel. Also I'm limited in the changes I can make to the excel sheet, so I'd like to do the processing on the Access side of things, and so I'd like to know how to select the minimum of data.

Is it possible to specify a range with non contiguous columns?

 

[SkipVought]

Can't you do your query...

select HeadingA, HesdingB, HeadingD
From workbook.tableName
where criteria1 and criteria2 and ....

Is the problem that since you do not have and SQL server that the entire table comes into Access before the sql is evaluated? I am not seeing the problem
You need to have and ODBC dirver for Excel configured to do this kind of database query.
Let me know what happens. Skip,
metzgsk@voughtaircraft.com

 

[DaleWatson123321]

Skip, and William,

Your level of VBA is FAR beyond mine, but I thought I could perhaps generate some "food for thought" with the following suggestion...

What about... from Access, run a routine which would:

1) load a separate Excel file which would contain the required code in a WorkbookOpen Event.

2) The code would first merge the entire block of data from the source file, extract out the required data and save it to a separate file.

3) The code would then return to Access and merge the data from the separate file.

Hope this qualifies as a (worthwhile) "assist".

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[WilliamMathias]

Skip & Dale,

I have a version that works by processing the data in Access.

To move this processing to Excel would achieve little.

Re: ODBC

This is another method of reading data from a remote data source, an alternative to ADO, however the problem I have would not be fixed by swapping my OLEDBProvider to the ODBC version or using plain old ODBC data sources.

Does anyone know about range specification, not in a "square", in excel?

Thank you for your responses,

Will

 

[TeddB]

Will,

In answer to your question: "Does anyone know about range specification, not in a "square", in excel?"

The syntax would be: (using example from your original post)
Range("B1:C100,E1:e100&quot.Select

Hope this helps.
Ted

 

[ilses]

Not a "square" range:
Range("E7,A1,B3,C1,D3,E1,F3,E7&quot

Ilses

 

[WilliamMathias]

Hi,

A big thank you to Skip,Dale,Ted and Ilses

I used Ted and ilses range method and it worked a treat!

My import routine now works 2 1/2 times faster!

I'm now just selecting the columns that I need.

Cheers,

Will