Re: Access to Excel

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 work***.
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 o*** As Excel.Work***

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

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

Set o*** = 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.



On Tue, 20 Feb 2007 21:07:44 -0800, Steve.M
<SteveM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Hi John,

I think I understand what you are saying, there will be a specific cell on a
specific *** 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" wrote:

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
<SteveM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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" wrote:

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
<SteveM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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" wrote:

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
<SteveM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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 [Microsoft Access MVP]

Please respond in the newsgroup and not by email.


--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.


--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.


--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.
.

Loading