Someone came up with the bright idea that we should
list all variables and values and labels from a database into
excel.
If we are asking something like race and storing a value or a label in
the table they want it so that it is put into excel in a format like
this.

name Value label
ethorig 1 caucasian
2 black
3 asian
4 natives
5 others
or

officespace 1 no problem
2 cramped
3 can't move


(they found a program online that would read this type of thing into
sas) shortening their work time

AND making mine a whole lot longer.



At first I was going to read the varname value and label from a from
but I quickly found out I was dealing with looking at the parent of a
control and not a child of the main control. (that would have been a
lot better I think)
well any way I can't see how to do it.

is there some problem or code that would read this in and store it in
a format like this??

thanks for any ideas on this

the examples you gave look like the records in supporting tables (tables
used to supply values to combobox controls in forms). you can export table
data to Excel, or write a query to alias fieldnames, if you wish, and export
the query to Excel. my question would be Why? what are you going to do with
the data in Excel that you can't do in Access?

hth


"sparks" <sparks (AT) comcast (DOT) net> wrote

No the values stored in the table migh come from an option group with
8 buttons or check boxes.
0-7 is stored in the table

but they are wanting a listing of what they stand for.

0 missing
1 slow

etc

so in an excel spreadsheet it would have

leader 0 missing
1 slow


this way that in their new program it would read the excel spreadsheet
and auto convert 0 to missing 1 to slow.





On Mon, 22 Jun 2009 13:55:32 GMT, "tina" <nospam (AT) address (DOT) com> wrote:

sas) shortening their work time
<<

It sounds like you have to transfer some data into Excel. What is the
data source for this data that needs to end up in Excel? At first I
thought the data resiced inside an Access mdb, but you never mention
Access or tables. Instead, you mention SAS. Is the source of this data
from SAS? If this is the case - your task is simple because SAS exports
data into delimited text files (any delimiter you like I believe - pipes
Now, if your job is to extract data from the SAS datapull, then just
import the text file(s) into Access and perform your data manipulations
there. Then export the data to Excel. The simplest method to automate
data transfer from Access to Excel is the TransferSpreadsheet method
(but this is also the least flexible method - a more flexible method is
to use ADO but that is a litte more sophisticated).





Rich

*** Sent via Developersdex http://www.developersdex.com ***

ah, i understand now. well, you might write some VBA code to loop through
the option group and write the option values and associated labels to a
table for export, or directly to an Excel spreadsheet perhaps. but unless
you have a lot of forms with a lot of option groups to deal with, i'd think
it would be just as fast to look at the option groups and type the data into
Excel manually.

hth


"sparks" <sparks (AT) comcast (DOT) net> wrote

8 buttons or check boxes.
0-7 is stored in the table

but they are wanting a listing of what they stand for.

0 missing
1 slow

etc

so in an excel spreadsheet it would have

leader 0 missing
1 slow


this way that in their new program it would read the excel spreadsheet
and auto convert 0 to missing 1 to slow.
<<
<<


One other thing you could do is to create a template style excel file
(just a .xls file) that would contain the basic layout of your report.
Then do some Excel programming where you read data from the respective
Access tables and place the data into the corresponding cells. In
Excel, the primary object is the Range object. The plan would be that
you have a button on the Sheet which would update the Excel file at the
user's desk. Each user could have a copy of this Excel file. The only
caveat is that the Access mdb would have to reside in a location (on a
common computer - preferrably a server computer) that each user could
reach. Here is a sample of how you read Access data in Excel: goto
Tools/Macros/Visual Basic Editor to open up a code module - and in the
Tools/References and Check "Microsoft ActiveX Data Objects 2.5
(preferrably higher than 2.5 if you have it installed) Library"

"Microsoft ActiveX Data Objects 2.7 Library"

This is the ADO library, and here is the code sample:

-------------------------------------------------------
Sub ReadDataFromAccess()
Dim cmd As New ADODB.Command, RS As ADODB.Recordset
Dim sht As Worksheet
Dim i As Integer
Set sht = Sheets("Sheet1")
sht.Range("A2:R2000").ClearContents

cmd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\code\testacc\db1test.mdb"

cmd.CommandType = adCmdText
cmd.CommandText = "Select * From stuff"
Set RS = cmd.Execute
sht.Range("A2").CopyFromRecordset RS
RS.Close
cmd.ActiveConnection.Close
-------------------------------------------------------

You can specify what specific Excel cells you want to populate. This is
just a generic example.


End Sub

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Well it seems I am getting info from 2 people about 2 different
things.

This is done with a script they are trying to use that will take
formatted data in an excel sheet and convert this data into variables
with the values they can have.

like this example

name Value label
ethorig 1 caucasian
2 black
3 asian
4 natives
5 others


so in sas when it is imported they can have the 1 2 3 converted to the
label so they work with the name instead of the value.

======================================

the other person is talking about a program called redcap.
That uses something similar to this to build a form and database.

It seems he saw the excel reference and decided that is what we were
talking about.

PS has anyone used redcap....they are saying they are going to phase
out all databases and go to this within the next year.

sparks wrote:
with long gray hair and claws in stead of hands. He lives on the
Scottish Border in ancient ruins of castles, especially in those with a
bloody history of war and murder. He owes his name to the fact that he
wears a red hat, which is colored by the blood of his victims. Red Cap
moves with remarkable speed, despite the fact that he wears iron boots.
He can overcome even the strongest man, unless the intended victim
remembers to quote a few words from the Bible.

Could you give a sample of what the data looks like which the end users
are working with?

It sounds like they get a data export from SAS which they can open in
Excel, but they want to use variable names instead of numbers for
filling out some information. Excel actually does have forms, but you
build them from Tools/Macros/Visual Basic Editor. If the users want to
fill out fields on a form - then Access, of course, would be easier to
work with than Excel.

Where I don't understand your problem is that you (the users) get a data
export from SAS. It has a certain format. The users can open this data
export in Excel. It would seem to me that you could import this data
into Access and create the desired form(s) with the desired fields. You
are probably already doing this (or maybe not). You are experiencing a
difficulty, but it is not clear what that difficulty is.

After the data from the data export has been manipulated - it sounds
like this data needs to end up in Excel. Here is another thought -
assuming you are manipulating the data in Access and then exporting it
again to Excel. You can use Excel Macros (not the kind you record) that
you write yourself in visual basic in the Excel visual basic editor to
manipulate the data just the way you want it. The Excel Visual Basic
Editor is actually a little more sophisticated than the Access VB editor
(same stuff - it just has a few more objects - mostly Excel objects -
which yes - you could get in Access if you reference the Excel library).
It sounds like you will need to do some Excel programming to resolve
your issue. If you are not currently and Excel programmer - you need to
become one. The easiest way is to start recording macros and then spend
some time in Excel Newsgroups. Here is the link to the msdn Excel
Programming NG

http://www.microsoft.com/communities...ult.aspx?dg=mi
crosoft.public.excel.programming&cat=en_US_3a793e1 f-4961-419d-9ec7-899d6
e6086cd&lang=en&cr=US

Rich

*** Sent via Developersdex http://www.developersdex.com ***