Dynamic range
Posted by Steve K. on April 18, 2001 10:41 PM
I am trying to import Excel97 spreadsheet data into Access 97 using the TransferSpreadsheet Macro. This macro requires a range as one of its arguments but the range will vary according to the number of rows entered into the spreadsheet each month.
How do you define a named range so that it changes dynamically?
Cheers ...Steve
Posted by Aladin Akyurek on April 18, 2001 11:19 PM
Posted by Steve K on April 19, 2001 12:02 AM
Aladin
Thank you very much for that - just what I needed I looked at the COUNTA function but was unsure how to incorporate it.
Posted by Steve K on April 19, 2001 5:52 PM
I can't get Excel to define the Name "Input" as
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$B:$B),17)
The first column is blank so the number of rows is based on the entries in Column B. There are 17 columns.
Access says it can't see the object "Input" nor does it appear in the Name box in Excel. I can't see anything wrong with the syntax. The range has to select all rows containing data in columns A to Q
Cheers...Steve
Posted by Dave Hawley on April 19, 2001 6:51 PM
Hi Steve
With dynamic ranges they will not appear in the Excel NameBox, you will only see them in InsertName box. While Excel itself may not have a problem seeing them, Access may well! I know that if you use these type of ranges to define a Pivot Table range and then set up your Pivot Table in another Workbook, Excel even has problems. So it would seem they are only a viable solution when used within the Workbook in which they are created. However I do have a solution to this sa I have had to overcome the problem myself.
Right click on the Sheet picture, top left next to "File" and select "View Code". Paste in the code below.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Written by OzGrid Business Applications
'www.ozgrid.com
'''''''''''''''''''''''''''''''''''''''''''
'Defines an named range dynamically.
'Used as an alternative to Insert>Name>Define OFFSET method.
'''''''''''''''''''''''''''''''''''''''''''''''''
Dim LlastRw As Long
Dim RmyRange As Range
'Pass the last row number to a Long variable
LlastRw = Sheet1.Range("B65536").End(xlUp).Row
'Use the Long variable to Resize from A1.
'Then set a Range variable to the resized range
Set RmyRange = Sheet1.Range("A1").Resize(LlastRw, 17)
'Give the Range variable a name
RmyRange.Name = "Input"
'Clear memory
Set RmyRange = Nothing
End Sub
Change "Sheet1" to suit and any other code. You could aslo place this in anyone of Excel many other Events. I prefer to use the Save myself though.
My Website has 6 more types of dynamic ranges under "Dynamic Ranges" and another VBA method under "VBA Tips and Tricks"
Dave
OzGrid Business Applications
Posted by Steve K on April 19, 2001 7:28 PM
I think the problem is with MS Access as Excel could see this range when I tried using it in a chart as an experiment.
Out of interest, I checked this in the MSKB and found MSKB article Q183446 used the rand() function to ensure of automatic update in a dynamic range.. Any thoughts on this?
Posted by Steve K on April 19, 2001 8:45 PM
I think the problem is with MS Access as Excel could see this range when I tried using it in a chart as an experiment.
Out of interest, I checked this in the MSKB and found MSKB article Q183446 used the rand() function to ensure of automatic update in a dynamic range.. Any thoughts on this?
