Access to Excel

[Steve.M]

Basically I want to type a name in an access table/field titled name. I want
the name to simply be inserted in a cell named name in excel. I want that
done on 3 excel forms.

The excel forms are just report forms with a description part number etc. no
calculations or special features, just type and save.

Everything I have read seems to say that I can, but how? Please help.

 

[John Nurick]

Hi Steve,

Naming fields with words that are also the names of common functions or
properties sooner or later causes problems, so don't use "Name" as a
name. Use something else, e.g. PartName.

The general idea is

1) set a reference to the Microsoft Excel Object Library

2) Then use VBA code along these lines (this is untested air code):

Dim oBook As Excel.Workbook
Dim strPartName As String

Set oBook = GetObject("D:\Folder\File.xls")

'need to add a criterion to the next line to specify the record
strPartName = DLookup("PartName", "MyTable", ???)

oBook.Names("PartName").Value = strPartName

oBook.Close True

 

[Steve.M]

John I really apreciate your feed back. As I was reading your reply I could
see that I was clearly over my head. I am guessing that what you were giving
me were action arguements.

I have read over some of them and I simply don't understand the language in
them or where and how they are applied.

Can you point me to a resource that can show me these things? I searched all
over the help files in Office and read all kinds of posts here but I can't
find the command stuctures terms and symbols that you guys are using let
alone where to place them in the macro box.

But I'll tell you this, when I figure this out I'll be the hero in our
office at work : )

 

[John Nurick]

Hi Steve,

If by "action arguments" you mean things you type into the fields in an
Access macro grid, the answer is no. The only way to do what you want is
with VBA code.

Probably the best resource is a good beginner's Access book. I'm not
going to recommend one, because everyone has different ideas of the kind
of book they learn from best. But if you look at the shelves in a good
bookshop you should find an assortment; pick one you like, as long as it
has chapters that cover VBA and, preferably, Automation.

Also, take a look at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
where there are links to a huge amount of information on using Access.

 

[Steve.M]

John,
if I could ask one more question, If I want to enter text from one sinsle
cell to another as I have proposed, is it better to go from my already
established access database table, or to create a form in Excel that
transfers text to other excel cells and also to my access table? I am willing
to go either way.

 

[John Nurick]

Steve,

The only time "one more question" is unwelcome is when the questioner
hasn't read the responses to their previous messages<g>.

But "one single cell" doesn't really have a meaning in relational
database software such as Access. The closest equivalent is something
like "the value of a particular field in a particular record in a
particular table" (or of a column of a tuple in a relation, but let's
not go there).

Equally, there's nothing in Access that corresponds to the way Excel
recalculates formulas throughout the workbook every time the user types
something in a cell and hits Enter.

On the other hand it's not all that complicated to set up a button on an
Access form that when clicked takes a value displayed on the form and
pokes it into a cell in an Excel workbook.

Would you like to give a fuller description of just what you're trying
to achieve?

 

[Steve.M]

Hi John,

I think I understand what you are saying, there will be a specific cell on a
specific sheet etc. I don't know if the cell is named I haven't played with
the real thing yet. Right now I have been setting up test databases and test
excel files here at home. I don't want to guinea pig at work yet, that could
be bad.

I did not set this up originally and I don't know much about it but here
goes.

In our access database we have a set up called DR REC.

When we open it, it takes us to a switch board where there are several
options. I select a button called New DR.

That takes me to a form with more buttons and text fields.

On that form there is a field called DR #. If you click on that field you
can scroll from the first entry the the last.

I push a button next to that field labeled, NEW DR and the field called DR #
goes blank.

I then enter an alpha-numeric in that field called DR #.
There are many other field on that form that recieve info as well.

That alpha-numeric number goes to a couple of diferent places within that
database.One specifically is in a table with 20 more columns.

The DR # is the key to the whole database. It is a number that we use as a
defeciency report serial number. Everything that is done and entered revolves
around that number.

When I hit enter on my keyboard after typing the alpha-numeric on that
access form, I want that alpha-numeric number to go to cell G8 in one Excel
form than to B4 on another form. Then I want the Item description field to do
the same thing in completely seperate fields.

Right now I am just trying to understand commands and lingo enough to set up
a test database and a test access form here at home.

Again I don't care if I have to start in Access or Excel or scrap it all
together. I just have 2 excel forms to fill out and a form on a web sight as
well as enter data into a couple of access tables from that one access form.

It would probably be easier to screen paste the 4 forms and let you see
those.

In the end I would have to expand one of the forms. I want to make all of my
data entry in one place and let it go to it's seperate cells and columns.

All of the forms involved share some info, not all have the same info.

No I'm not really crazy, just ambitious. But I can't tell you how much I
appreciate the time you have already put into this
Steve

 

[John Nurick]

Steve,

Here's a sample VBA procedure that will take the value in a textbox on
an Access form and poke it into a specified cell in an Excel worksheet.
You'll need to open the Access VB Editor (hit Alt+F11) and:

(a) Using the Tools|References menu command, set a reference to the
Microsoft Excel X Object Library (where X is your version, e.g. 11 for
Excel 2003).

(b) Paste the code into an event procedure in the code module of your
Access form and

(c) Modify it so the names match yours and it puts the value into both
the locations you need.

If you want the values to be poked into Excel automatically whenever you
change the value in the textbox, use the textbox's AfterUpdate event
procedure. But more often one would want it only after entering or
editing a record in the table to which the form is bound: in that case,
use the form's AfterUpdate event procedure (or for more control over
what happens, BeforeUpdate).

Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

Set oBook = GetObject("D:\Folder\FileName.xls")
Set oSheet = oBook.Worksheets("Sheet1")

oSheet.Range("G8").Value = Me.XXX.Value 'XXX = name of textbox

Set oSheet = Nothing

oBook.Windows(1).Visible = True
oBook.Close True 'True to save before closing
Set oBook = Nothing

I don't know what you mean by two "Excel forms". I guess you mean two
worksheets, but you don't say whether they're in the same or separate
workbooks. But it's not hard to modify the code above to suit either
case.

That's the basics. If you need more features (e.g. to let the user
specify which workbook should be opened) things get more complicated. If
you need "production quality" code - that will handle errors elegantly,
cope with incorrect input from users, and so on - they get a lot more
complicated.

 

[Steve.M]

Thanks John I will be trying this later,

If I call my access table "table 1" and the cell that I want to draw the
text from "DR#"

Then my new excel form "test 1" and the cell I want to input to "DR#" in
Cell # "G8" what would that look like in the code below? would they conflict?

Do I literally enter Me.xxx.'Value.xxx= ? and do I create a name for a
cell for "name of text box" such as "DR #" ?

The 2 excel forms are in seperate workbooks, when we print them they have
your standard labeled boxes with lines to handwrite info. But since they are
created in excel we type the info in the cells which we have to combine cells
to give us enough room to type in info. Again there are no calculations or
special functions with the data, this would equate to using a simple
typewriter.

I am thinking that I could put both forms in one book say as example as
above, book "test 1" sheet 1 and sheet 2.

And my apologies, I didn't know that I was responding via e-mail.
Steve

 

[John Nurick]

Steve,

Once again, Access has no concept of a "cell", let alone cells you can
name.

Also, life is simpler in the long run if you don't use spaces or special
characters in the names of tables or other objects. Rename "DR#" to
DRHash or whatever # means to you; and I'd always call a table something
more informative than "table1".

I still don't know what you mean by "excel form". Do you mean a
UserForm, a form created by the Data|Form... menu command, a worksheet,
or something else?

Thanks John I will be trying this later,

If I call my access table "table 1" and the cell that I want to draw the
text from "DR#"

Then my new excel form "test 1" and the cell I want to input to "DR#" in
Cell # "G8" what would that look like in the code below? would they conflict?

Do I literally enter Me.xxx.'Value.xxx= ? and do I create a name for a
cell for "name of text box" such as "DR #" ?

The 2 excel forms are in seperate workbooks, when we print them they have
your standard labeled boxes with lines to handwrite info. But since they are
created in excel we type the info in the cells which we have to combine cells
to give us enough room to type in info. Again there are no calculations or
special functions with the data, this would equate to using a simple
typewriter.

I am thinking that I could put both forms in one book say as example as
above, book "test 1" sheet 1 and sheet 2.

And my apologies, I didn't know that I was responding via e-mail.
Steve

Steve,

Here's a sample VBA procedure that will take the value in a textbox on
an Access form and poke it into a specified cell in an Excel worksheet.
You'll need to open the Access VB Editor (hit Alt+F11) and:

(a) Using the Tools|References menu command, set a reference to the
Microsoft Excel X Object Library (where X is your version, e.g. 11 for
Excel 2003).

(b) Paste the code into an event procedure in the code module of your
Access form and

(c) Modify it so the names match yours and it puts the value into both
the locations you need.

If you want the values to be poked into Excel automatically whenever you
change the value in the textbox, use the textbox's AfterUpdate event
procedure. But more often one would want it only after entering or
editing a record in the table to which the form is bound: in that case,
use the form's AfterUpdate event procedure (or for more control over
what happens, BeforeUpdate).

Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

Set oBook = GetObject("D:\Folder\FileName.xls")
Set oSheet = oBook.Worksheets("Sheet1")

oSheet.Range("G8").Value = Me.XXX.Value 'XXX = name of textbox

Set oSheet = Nothing

oBook.Windows(1).Visible = True
oBook.Close True 'True to save before closing
Set oBook = Nothing

I don't know what you mean by two "Excel forms". I guess you mean two
worksheets, but you don't say whether they're in the same or separate
workbooks. But it's not hard to modify the code above to suit either
case.

That's the basics. If you need more features (e.g. to let the user
specify which workbook should be opened) things get more complicated. If
you need "production quality" code - that will handle errors elegantly,
cope with incorrect input from users, and so on - they get a lot more
complicated.

Hi John,

I think I understand what you are saying, there will be a specific cell on a
specific sheet etc. I don't know if the cell is named I haven't played with
the real thing yet. Right now I have been setting up test databases and test
excel files here at home. I don't want to guinea pig at work yet, that could
be bad.

I did not set this up originally and I don't know much about it but here
goes.

In our access database we have a set up called DR REC.

When we open it, it takes us to a switch board where there are several
options. I select a button called New DR.

That takes me to a form with more buttons and text fields.

On that form there is a field called DR #. If you click on that field you
can scroll from the first entry the the last.

I push a button next to that field labeled, NEW DR and the field called DR #
goes blank.

I then enter an alpha-numeric in that field called DR #.
There are many other field on that form that recieve info as well.

That alpha-numeric number goes to a couple of diferent places within that
database.One specifically is in a table with 20 more columns.

The DR # is the key to the whole database. It is a number that we use as a
defeciency report serial number. Everything that is done and entered revolves
around that number.

When I hit enter on my keyboard after typing the alpha-numeric on that
access form, I want that alpha-numeric number to go to cell G8 in one Excel
form than to B4 on another form. Then I want the Item description field to do
the same thing in completely seperate fields.

Right now I am just trying to understand commands and lingo enough to set up
a test database and a test access form here at home.

Again I don't care if I have to start in Access or Excel or scrap it all
together. I just have 2 excel forms to fill out and a form on a web sight as
well as enter data into a couple of access tables from that one access form.

It would probably be easier to screen paste the 4 forms and let you see
those.

In the end I would have to expand one of the forms. I want to make all of my
data entry in one place and let it go to it's seperate cells and columns.

All of the forms involved share some info, not all have the same info.

No I'm not really crazy, just ambitious. But I can't tell you how much I
appreciate the time you have already put into this
Steve


:

Steve,

The only time "one more question" is unwelcome is when the questioner
hasn't read the responses to their previous messages<g>.

But "one single cell" doesn't really have a meaning in relational
database software such as Access. The closest equivalent is something
like "the value of a particular field in a particular record in a
particular table" (or of a column of a tuple in a relation, but let's
not go there).

Equally, there's nothing in Access that corresponds to the way Excel
recalculates formulas throughout the workbook every time the user types
something in a cell and hits Enter.

On the other hand it's not all that complicated to set up a button on an
Access form that when clicked takes a value displayed on the form and
pokes it into a cell in an Excel workbook.

Would you like to give a fuller description of just what you're trying
to achieve?


On Tue, 20 Feb 2007 05:27:00 -0800, Steve.M

John,
if I could ask one more question, If I want to enter text from one sinsle
cell to another as I have proposed, is it better to go from my already
established access database table, or to create a form in Excel that
transfers text to other excel cells and also to my access table? I am willing
to go either way.

:

Hi Steve,

If by "action arguments" you mean things you type into the fields in an
Access macro grid, the answer is no. The only way to do what you want is
with VBA code.

Probably the best resource is a good beginner's Access book. I'm not
going to recommend one, because everyone has different ideas of the kind
of book they learn from best. But if you look at the shelves in a good
bookshop you should find an assortment; pick one you like, as long as it
has chapters that cover VBA and, preferably, Automation.

Also, take a look at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
where there are links to a huge amount of information on using Access.

On Mon, 19 Feb 2007 00:52:16 -0800, Steve.M

John I really apreciate your feed back. As I was reading your reply I could
see that I was clearly over my head. I am guessing that what you were giving
me were action arguements.

I have read over some of them and I simply don't understand the language in
them or where and how they are applied.

Can you point me to a resource that can show me these things? I searched all
over the help files in Office and read all kinds of posts here but I can't
find the command stuctures terms and symbols that you guys are using let
alone where to place them in the macro box.

But I'll tell you this, when I figure this out I'll be the hero in our
office at work : )

:

Hi Steve,

Naming fields with words that are also the names of common functions or
properties sooner or later causes problems, so don't use "Name" as a
name. Use something else, e.g. PartName.

The general idea is

1) set a reference to the Microsoft Excel Object Library

2) Then use VBA code along these lines (this is untested air code):

Dim oBook As Excel.Workbook
Dim strPartName As String

Set oBook = GetObject("D:\Folder\File.xls")

'need to add a criterion to the next line to specify the record
strPartName = DLookup("PartName", "MyTable", ???)

oBook.Names("PartName").Value = strPartName

oBook.Close True


On Fri, 16 Feb 2007 21:07:22 -0800, Steve.M

Basically I want to type a name in an access table/field titled name. I want
the name to simply be inserted in a cell named name in excel. I want that
done on 3 excel forms.

The excel forms are just report forms with a description part number etc. no
calculations or special features, just type and save.

Everything I have read seems to say that I can, but how? Please help.

 

[Steve.M]

John,

by cell I mean the cell in the excel sheet in this case cell G8 I am trying
to do just as you stated previously as I have quoted below.

By form I mean an excel sheet that has been designed with lines and boxes
that is printed out and handwriten on. it is like what you might see on a
medical bill that you get in the mail.

I have been trying all evening to build a code I have pasted the code and
changed names. I can't get past the beforeupdate I can't figure out how to
enter that. I keep taking all the info you give me and I research it in help
files on and offline. I can't find any straight forward instructions.
Basically I have enough info now I think, I am too linear and I don't have a
clue about this VBA set up.

Sometimes I wonder if instructions are mixed with the code.

I am very linear with stuff like this I need a step by step from the very
begining.

So I am going to have to find a way to start from the begining.

create a table
create a field
create an excel sheet
create a field or cell in excel
Open this this way
enter this that way
Create VBA
type as follows with the created names

I think I am going to have to take a class or find a good book, the help
files are not helping, and you have bigger fish to fry. It seems like
everything I am reading assumes that I know what they are talking about. It
took me an hour to figure out how to get a code module going and I don't know
if it is set up correctly. It's asking me to choose, public, static, etc.
like I know this stuff. The hlep files that I look up on this stuff have not
been helpful.

Sorry , but on the upside, i am researching what you are giving me.

Thank you

Steve