Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Newby question about selecting and what it can do [longish]
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- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Newby question about selecting and what it can do [longish]
suggest making your sort range a defined name, something like.
sortrng =offset($a$2,0,0,counta($A:$A),6) "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- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Newby question about selecting and what it can do [longish]
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- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel (XP) - longish, sorry | Excel Discussion (Misc queries) | |||
how best to handle longish text fields in excel? | Excel Discussion (Misc queries) | |||
My Vlookup solution is too clumsy (longish) | Excel Discussion (Misc queries) | |||
Newby help | Excel Worksheet Functions | |||
Yet another Newby question about selecting | Excel Programming |