Another Newby question about selecting and what it can do [longish]
Once again, thank you Tom!
-gk-
"Tom Ogilvy" wrote in message
...
When you open a dbf file in excel, it will always start in Cell A1 and the
data in the table will be specified in a defined name
(insert=Name=Define)
named database. You can examine this defnition with
msgbox Range("Database").Address(external:=True)
you can get the upper left corner with
msgbox range("Database")(1).Address
you can step through the header with
i = 0
for each cell in Range("Database").Rows(1).Cells
i = i + 1
if i < 6 then ' no use looking at all 45
msgbox "header" & i & ": " & cell.Value
end if
Next
If you add data (New rows), then database will not expand to include that
data and if you save the file as dbf, the new data will not be saved
unless
you redefine database - which you can do with
range("Database").CurrentRegion.Name = "Database"
If you want to know what area on the worksheet is considered Used
msgbox Activesheet.Usedrange.Address(external:=true)
Private Sub CommandButton4_Click()
dim rng as Range
dim varr as variant
dim i as long
dim sStr as string
set rng = ActiveCell.CurrentRegion
if rng.Address = ActiveCell.Address then
msgbox "Please select within the data table"
Exit Sub
End if
' or skip the above and use
' set rng = Range("Database").CurrentRegion
rng.Sort Key1:=rng(1).offset(0,2), Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("A5").Select
varr = rng.rows(1)
for i = lbound(varr,2) to ubound(varr,2)
sStr = sStr & varr(1,i) & ", "
if i mod 5 = 0 then
sStr = sStr & vbNewLine
end if
Next
sStr = Left(sStr,len(sStr)-1)
msgbox sStr
End Sub
--
Regards,
Tom Ogilvy
TBA wrote in message
...
This is a follow-up to the last post I made about selecting a range.
I feel it's necessary at this time to provide a little bit of background
about the table I am working with. I will try to be brief.
The company I work for does digital mapping for enhanced 911 systems.
We
use GIS [Geographical Information Systems] software to manage and edit
our
data. Each feature on a map (roads, structures) is known as a Shape
file.
Each Shape file is in fact at least 3 separate files, all with the same
file
name except for the file extension. One of the file extensions is
*.dbf.
This file is the file that contains all the attribute data for the
feature
on the map. It is a flat two-dimensional file and can be accessed from
within the GIS software. Sometimes it becomes necessary to modify this
*.dbf file in Excel to gain some added functionality.
So, from within my GIS software I have the option of exporting the *.dbf
file as a new *.dbf file. This file can be opened in Excel, edited,
saved,
and then brought back in to the GIS software environment (there's a
little
more to it than that, but you get the basic idea).
What I am doing is playing with a copy of one of our *.dbf files as
sample
data to help me learn VBA. What I have done is exported the file as
described above, but when I open it with Excel I save it as an *.xls
file
to
avoid some minor hassles. This file, or table as our software likes to
call
it, usually contains about 35-45 columns and anywhere from 100 to
10,000+
rows of data. The first row of the table is ALWAYS a header row. The
headers are text and basically describe the nature of the values in that
column.
(If you're still reading this, thank you!)
Yesterday I added some command buttons to the worksheet itself and coded
one
of those buttons like this:
Private Sub CommandButton4_Click()
ActiveCell.CurrentRegion.Select
Selection.Sort Key1:=Range("C5"), Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A5").Select
End Sub
The code for doing the sort I got by recording a macro and then copying
and
pasting the macro code into the code procedure for the command button.
It
works just fine as long as you highlight a cell containing data from the
table before clicking on the command button. I know there is a way to
"trap" errors, and that I can make a message box come up to ask the user
to
"Please select a value from the table containing data and try again."
For
now I am assuming this isn't necessary.
So...I want to start out simple.
Q1: What is the code for the error message box? I'm assuming it's a
type
of "OnError do this" statement.
Q2: I'd like to be able to dump all the column headers into a list,
with
the hope that I can use the actual column header names as variables in
the
coding.
And that's just for starters. :)
Lastly, a major caveat of what I'm trying to do is that the table can
reside
ANYWHERE on the spreadsheet. The table can be of any size. It will
ALWAYS
have column headers. Because of this I can't assume that the column
header
for the first column will always be in cell A1. I am hoping that from
the
selection statement "ActiveCell.CurrentRegion.Select" that I will be
able
to
extract some of the parameters of the table, like what the column
headers
are, where they are, what the actual cell references are that define the
table extents, etc. These are all values that could easily be different
from one table to the next.
Any help with Q1 and Q2 is greatly appreciated. I hope this is clear as
mud. ;)
Thanks for reading!!
-gk-
|