Selecting Excel ranges (dynamic) from Access VBA

[wiklendt]

Hi everyone,

what i'm trying to do is manipulate excel data in preparation for import into access. i have progressed quite well, but i'm stuck on how to select a dynamic range from within access:

i have some columns of data, which i want to copy to a new worksheet. They are columns A:I, but non-fixed rows. There is superfluous data both above and below this range, so i can't use "last row" (at least, not in the sense that everyone else seems to be using it)

i have managed to find the start position of this data using:

            With xlWSh
                .Activate
                For i = 1 To .UsedRange.Rows.Count
                    If .Cells(i, 1) = "Isolate AST Results" Then

                        [i]...more code here[/i]

                    End If
                Next
            End With

in "more code here" i need to then select down the column (in a 'currentregion' fashion) and then across to the ninth column (I), so in essence i get a block selection from .Cells(i+2,1), down column A to the last cell before a blank cell break, then across to the I column. (see below):

(doesn't have to select, i just need to be able to copy it, but "select" lets me at least see visually that the code is working as it's supposed to while i'm testing).

once the range is found, i need to copy it to another new worksheet - this portion at least is working for me, but it's not finding the correct range to copy.

If i use:

.Range("A24:I24").Copy Destination:=xlWShExtract.Range("A2")

then that works, but it's only one row.

i've tried using .Currentregion.Copy, but it only copies over the data from column A, so it appears that Currentregion is only good for the column of the active cell, and, if Excel Help is anything to go by, there can only be one active cell per region.

i thought myself terribly clever when i got the range address figured out with the following code:

                        'find the address of the appropriate range
                        strRangeLeft = ApXL.Cells(i + 2, 1).Address(0, 0)
                        strRangeRight = ApXL.Cells(i + 2, 9).Address(0, 0)
                        strRange = Chr$(34) & strRangeLeft & ":" & strRangeRight & Chr$(34)
                        'returns A1 range reference - e.g., "A24:I24"
                        'MsgBox strRange

which returns what i expect: "A24:I24"

but, either i have not declared it as the correct type, or i am not using the string correctly in my code (e.g. ".Range(strRange).Select"), because i get "Error 1004 - Application-defined or object-defined error". i get this error even if i leave it as an 'open' declaration (i.e., Dim strRange, without the "As String").

I tried to declare it as Range, but access didn't like that, so i tried ApXL.Range (i'd set my ApXL earlier), but access errors on that too - i've tried also declaring it as an object, with no luck.

to test my syntax, i used .Range("A24:I24").Select, i do not get an error, but again, i can't hardcode the range because it changes from file to file.

so i'm at my wits end (been at this for a week). Any suggestions mightily appreciated.

here are some more attempts of mine to select the correct range, commented with the associated error:

                        ' Copies only either column A data, or first row of block data.
                        '.Range(ApXL.Cells(i + 2, 1), ApXL.Cells(i + 2, 9)).CurrentRegion.Copy Destination:=xlWShExtract.Range("A2")
                        '.Range("A24:I24").Copy Destination:=xlWShExtract.Range("A2")
                        
                        '' Error 1004 - Application-defined or object-defined error (does strRange need to be declared something different?
                        '.Range(strRange).Select
                        
                        '' Error 438 - Object does not support this property or method
                        '.Range(ApXL.strRange).Select
                        
                        '' Error 91 - object variable or with block variable not set
                        '.Range(Selection, Selection.End(xlDown)).Copy Destination:=xlWShExtract.Range("A2")
                        
                        '.Range(ApXL.objRange).CurrentRegion.Copy Destination:=xlWShExtract.Range("A2")
                        
                        ' Does not error, but also copies only column A
                        '.Range(ApXL.Cells(i + 2, 1), ApXL.Cells(i + 2, 9)).CurrentRegion.Copy Destination:=xlWShExtract.Range("A2")
                        
                        ' The following worked to copy correct data from Column A - it's a start!
                        '.Range(ApXL.Selection, ApXL.Selection).CurrentRegion.Copy Destination:=xlWShExtract.Range("A2")
                        
                        '' Error 1004 - Application-defined or object-defined error (does strRange need to be declared something different?
                        '.Range(ApXL.Selection, ApXL.Selection.End(xlDown)).Copy Destination:=xlWShExtract.Range("A2")
                        
                        '---- Recorded Excel Macro doing various interesting things ----
                        '' CopyDeleteMoveColumns Macro
                        '    Range("A24:I24").Select 'select the first antimicrobial and across to the Final SIR (9th col, 8 cols from A)
                        '    Range(Selection, Selection.End(xlDown)).Copy Destination:=xlWShExtract.Range("A2") 'copy/paste to Extract!A2
                        '---------------------------------------------------------------

Attached is my full code for the function, which prompts user to select a file, adds a new worksheet and other bits and pieces directly related to what i want to do.

Here also is a sample xls file that needs this data manipulation done to it, in case anyone feels the urge to test stuff...

anyone need clarification on what i'm doing or why just let me know.

ta muchly for any assistance, guidance or tutorials on this sort of stuff!

 

[Banana]

In general, and assuming this is the same Excel we talked of earlier where it's generated by an external program so you have no control over the formatting, I'd probably settle on a simple method of dimensioning the range. For example:

 With xlWSh
                .Activate
                For i = 1 To .UsedRange.Rows.Count
                    If .Cells(i, 1) = "Isolate AST Results" Then

                        j = i
                        Do Until .Cells(j, 1).Value = ""
                            j = j + 1
                        Loop
                        With .Range(.Cells(i,1), .Cells(j,1))
                            .Select
                            'We now have our column selected
                         End with

                    End If
                Next
            End With

Do note, though, you probably want to select more than just one columns, so you may need to set the column accordingly for the ".Cells(j, XXX)", either using a fixed number (counting on the assumption that the numbers of columns won't change) or by doing similar technique (e.g. scan each of column and incrementing a variable by one to determine your final column counts like you did with the variable j).

I hope that'll help. There are of course other ways and I'm not sure if I've entirely your clever use of Address which I've used myself for cases where incrementing a variable by one would not be of much help for whatever reasons.

 

[wiklendt]

 With xlWSh
                .Activate
                For i = 1 To .UsedRange.Rows.Count
                    If .Cells(i, 1) = "Isolate AST Results" Then

                        j = i
                        Do Until .Cells(j, 1).Value = ""
                            j = j + 1
                        Loop
                        With .Range(.Cells(i,1), .Cells(j,1))
                            .Select
                            'We now have our column selected
                         End with

                    End If
                Next
            End With

thanks for your quick response. yes, this is the same excel sheet as i've previously posted about.

i'll look into that. looks like it might help. yes, as far as i can tell from the files i've looked at, the number of relevant columns is fixed (it'd better be!)

i thought about doing something similar but couldn't clarify what how exactly i wanted to do it, so i never got around to that logic. looks like it might be useful.

thanks again

 

[vicodin]

xlWSh.Range(xlWSh.UsedRange.Find("Isolate AST Results").Offset(2, 0), xlWSh.Range("I" & xlWSh.UsedRange.Find("Isolate AST Results").Offset(2, 0).End(xlDown).Row)).Select

 

[Brianwarnock]

ActiveCell.CurrentRegion.Select
Selection.Copy

or eg Range("B2").CurrentRegion.copy
or Cells(2,2).CurrentRegion.Copy

ie only select a single Cell to use CurrentRegion

Does this help?

Brian

 

[wiklendt]

Hey guys, thanks for all the help.

vicodin, i got error 91 on yours, and when i broke up the code into smaller sections (to see which bits access/excel was unhappy about) i got error 1004.... (or the other way around?)

Brianwarnock, yup, i'd originally tried all sort of variations on that kind of code. the issue was that i wanted to select a current region of a range, rather than an active (or just one) cell, and that the further columns were 'broken' by empty cells, so current region on just that column wouldn't select all the data for that block.


Banana's code did the trick for me - i may have to adapt it if the column ever changes (not my current experience with these third-party-exported files).

I'm progressing very well now and have almost all the data arranged in such a way that it could be imported... (which is my next step)

 

[Banana]

Just a FWIW - I avoid Active<anything> as much as I can. I've had a instance where a Excel report acted funky - they'd get a report with missing chunks which later turned out to be on entirely different sheet or even in different Excel workbook that were open at the time! So, I'm of mind it's best to do everything through Workbooks & WorkSheets practically exclusively. YMMV.

 

[wiklendt]

Hi Banana, yes, i've been working towards streamlining my code. have already removed any hint of "select" and now i'm sure i don't need my "activate"s either.

i will only use 'select' if i'm trying to test (edit: i.e., testing my code) that i've got the right range or cells or something (edit: then i'll remove the 'select').

by the by - in terms of excel, is

ApXL.ScreenUpdating = False

redundant if i have

ApXL.Visible = True

?

or is it best to have both....

 

[Banana]

xlWSh.Range(xlWSh.UsedRange.Find("Isolate AST Results").Offset(2, 0), xlWSh.Range("I" & xlWSh.UsedRange.Find("Isolate AST Results").Offset(2, 0).End(xlDown).Row)).Select

That's cool. I wished I knew about that End method. Will have to make use of that and save me a variable.

wiklendt - I don't know what happened with your code and how you tried to troubleshoot but if this help...

Dim rng1 As Excel.Range
Dim rng2 As Excel.Range

Set rng1 = xlWSh.UsedRange.Find("Isolate AST Results").Offset(2, 0)
Set rng2 = xlWSh.Range("I" & xlWSh.UsedRange.Find("Isolate AST Results").Offset(2, 0).End(xlDown).Row)

xlWs.Range(rng1, rng2).Select

It'll help pinpoint any problem with the syntax, I hope.

 

[Banana]

Regarding the question of Visible & ScreenUpdating... I looked at the Excel help and there was nothing to suggest that they are affected by each other. Therefore, I'd think both should be done together, but I could be wrong here.

 

[boblarson]

If it isn't visible then you don't need to worry about screen updating.

 

[wiklendt]

Dim rng1 As Excel.Range
Dim rng2 As Excel.Range

Set rng1 = xlWSh.UsedRange.Find("Isolate AST Results").Offset(2, 0)
Set rng2 = xlWSh.Range("I" & xlWSh.UsedRange.Find("Isolate AST Results").Offset(2, 0).End(xlDown).Row)

xlWs.Range(rng1, rng2).Select

As a start, Access didn't let the code run at all, throwing an error at "Dim rng1 As Excel.Range" - Error "User defined type not defined".

I tried something like that before, and access just wouldn't like any of my declaration types (Range, Excel.Range, ApXL.Range, etc etc) eventually i tried it with an open decalration (i.e., just "Dim rng1") so that it could make its own mind up about what type the data is, but then it has issues with the code.

if i leave off the added complexity of declarations and sets, and concentrate solely on the code, by breaking up the ranges and stepping into each line in debug mode (edit: these were tested separately, not one-after the other in a block of code), these are the errors i get with their respective codes:

'this one works
xlWSh.UsedRange.Find("Isolate AST Results").Offset(2, 0).Select

'Error 1004 - Application defined or object defined error
xlWSh.UsedRange.Find("Isolate AST Results").Offset(2, 0).End(xlDown).Row.Select

'Error 91 - object variable or with block variable not set
xlWSh.Range("I" & xlWSh.UsedRange.Find("Final SIR").Offset(2, 0)).Select

'Error 91 - object variable or with block variable not set
xlWSh.Range(xlWSh.UsedRange.Find("Final SIR").Offset(2, 0)).Select

'Error 91 - object variable or with block variable not set
xlWSh.UsedRange.Find("Final SIR").Offset(2, 0).Select

'Error 91 - object variable or with block variable not set
xlWSh.Range("I" & xlWSh.UsedRange.Find("Final SIR").Offset(2, 0).End(xlDown).Row).Select

i can't seem to use either: "ranges" as 'strings' or 'ranges', nor can i seem to be able to use xlDown, and no idea why the second last one won't work when the first did...?

in any case, i have the code working with j & i & loops.... so this isn't urgent, but by all means, i'd still love to know how to
(a) declare a 'range' type
(b) select with xlDown

thanks for the posts, guys

 

[wiklendt]

if you guys want to test this code on my file in your environment, i posted the excel file in my first post as an attachment.

edit: and here's the latest required code. it has three modules.

the module with the code we're manipulating here is called modImportPhoenix.

the code asks the user to browse for a file, which is the basOpenFile module, and there's one tiny iddy biddy bit of code in modFunctions that is used in my Error Handling.

 

[Banana]

You probably need to add an reference to Microsoft Excel XX.XX object library so VBA can recognize the Excel classes such as Ranges. Of course, you don't want to leave it in for production and you can change to late-binding once you've built the code, but for development it's alot much easier using early binding by adding the references.

BTW, it probably chokes on the xlDown because it's not a recognized constant (no Excel references) so in late binding, you need to make sure you supply the literal number (e.g. declaring the constant in the same module instead of depending on one in Excel library).

 

[wiklendt]

If it isn't visible then you don't need to worry about screen updating.

ta. i was worried it'd still go through the motions even if it isn't visible...

 

[wiklendt]

You probably need to add an reference to Microsoft Excel XX.XX object library so VBA can recognize the Excel classes such as Ranges. Of course, you don't want to leave it in for production and you can change to late-binding once you've built the code, but for development it's alot much easier using early binding by adding the references.

BTW, it probably chokes on the xlDown because it's not a recognized constant (no Excel references) so in late binding, you need to make sure you supply the literal number (e.g. declaring the constant in the same module instead of depending on one in Excel library).

and so my tuition continues - i now have about 15 tabs open in my browser defining, comparing, and showing examples of both "late" and "early" binding...

thanks Banana - i strive to make my code as elegant, error-free and streamlined as i can...!

 

[Brianwarnock]

Just a FWIW - I avoid Active<anything> as much as I can. I've had a instance where a Excel report acted funky - they'd get a report with missing chunks which later turned out to be on entirely different sheet or even in different Excel workbook that were open at the time! So, I'm of mind it's best to do everything through Workbooks & WorkSheets practically exclusively. YMMV.

I rarely use it but have never had a problem if I select the Active <anything> before using it.

brian

 

[Brianwarnock]

Hey guys, thanks for all the help.

Brianwarnock, yup, i'd originally tried all sort of variations on that kind of code. the issue was that i wanted to select a current region of a range, rather than an active (or just one) cell, and that the further columns were 'broken' by empty cells, so current region on just that column wouldn't select all the data for that block.

Sorry I didn't examine your requirements fully, with you talking of regions and attempting to use CurrentRegion I assumed a proper region, ie a block of contiguos cells bounded by blank rows and columns, and as your sample had shown had a range of cells thought that was your problem.

Brian

 

[Brianwarnock]

'Error 91 - object variable or with block variable not set
xlWSh.Range("I" & xlWSh.UsedRange.Find("Final SIR").Offset(2, 0).End(xlDown).Row).Select

[/code]

and no idea why the second last one won't work when the first did...?

thanks for the posts, guys

Final SIR has an unprintable character in it in the spread sheet so it is not found, however if it did work it would return row 33 e before the first blank in col I. I am not familiar with running EXCEL from access it all works simply in Excel I suspect Banana has put his finger on it with reference to references.

Brian

 

[Brianwarnock]

Inserted a sheet2 into the spreadsheet you posted and working in native Excel I would do the following to copy the area you require, I have assumed that you want the headings, to sheet2

Dim rng1 As Range
Dim rng2 As Range
Dim myrange As Range

Set rng1 = Worksheets("sheet1").UsedRange.Find("Isolate AST Results")
Set rng2 = Worksheets("sheet1").Range("a" & Worksheets("sheet1").UsedRange.Find("Isolate AST Results").Offset(2, 0).End(xlDown).Row)

Set myrange = Range(rng1, rng2.Offset(0, 8))

myrange.Copy

Sheets("sheet2").Range("A1").PasteSpecial

Brian