Creating forms in Excel to Import access data ! Please reply

  • Thread starter Thread starter jwr
  • Start date Start date
J

jwr

I have posted this previously in excel.links without an answer. I am at a
standstill now. Your assistance is appreciated.


I have an access database. In this database, I have several forms and
reports that need to be sent via email to other individuals. They need to
enter info into several locations and then return via email to me.

If I export the form/report to excel, the format is lost. Is it possible to
create a form/report in excel, import the info from my access database, and
then email to another location without losing the info and also allowing
that individual to enter info into specific cells?

Thanking you in advance.
 
Hi,

I think the best way wold be to actually build the template in Excel, and
access a query in the Access database.

You can pass parameters back into access when running the query, and
emailing out he sheet will simply "break" the link to Access, so people will
be able to view/edit numbers.

Hope this helps

Sunil Jayakumar

jwr said:
I have posted this previously in excel.links without an answer. I am at a
standstill now. Your assistance is appreciated.


I have an access database. In this database, I have several forms and
reports that need to be sent via email to other individuals. They need to
enter info into several locations and then return via email to me.

If I export the form/report to excel, the format is lost. Is it possible
to
create a form/report in excel, import the info from my access database,
and
then email to another location without losing the info and also allowing
that individual to enter info into specific cells?

Thanking you in advance.
Click to expand...

www.ayyoo.com/credit-cards.html
 
That is exactly what I want to do -- I don't know how!
Can you assist or direct me to a website for information?

Thank you,
jwr
 
Hi,

Easy way: Open MS Query directly - usually in your C:\Program
Files\Microsoft Office\Office or Office11 Folder, file name MSQRY32.EXE.

File>Table Definition, select your database, and set your query - you can
also type in SQL directly. You can then go File>Save As... and save as a
*.dqy or *.qry file. Store these somewhere you can find them.

Back in Excel, Data>Import External Data>Import Data, and navigate to the
query. This should then prompt you for any parameters set up (these work
differently for different database, but a "?" in the SQL will generate the
prompt - even if you ahven't used SQL, go into the code, and replace any
parameters you want to be "live" with a "?")

This should now work ok - I'll monitor this thread, but don't expect
immediate replies ;)

Hope this helps

Sunil Jayakumar







jwr said:
That is exactly what I want to do -- I don't know how!
Can you assist or direct me to a website for information?

Thank you,
jwr
Click to expand...
www.ayyoo.com/credit-cards.html
 
Thank you so much.

I have managed to create the information from the access into an MS Query.
Now -- I want this information to be placed into certain fields on a
template that I will create in excel.

How do I create the template and have the information input in the correct
location?

Thanks in advance
 
Hi,

This is a mail merge type of operation....

What you will need is 2 sheet - one with the data fields on it, and the
other with the form/template you propose to populate. You will need a unique
identifier, so your table knows which row to pick up to populate the form.
You should not be able to get multiple data rows for the form.

Then, just use a VLOOKUP to pull the data back, based on your unique
identifier.

Hope this helps

Sunil Jayakumar


jwr said:
Thank you so much.

I have managed to create the information from the access into an MS Query.
Now -- I want this information to be placed into certain fields on a
template that I will create in excel.

How do I create the template and have the information input in the correct
location?

Thanks in advance


Sunil Jayakumar said:
Hi,

Easy way: Open MS Query directly - usually in your C:\Program
Files\Microsoft Office\Office or Office11 Folder, file name MSQRY32.EXE.

File>Table Definition, select your database, and set your query - you can
also type in SQL directly. You can then go File>Save As... and save as a
*.dqy or *.qry file. Store these somewhere you can find them.

Back in Excel, Data>Import External Data>Import Data, and navigate to the
query. This should then prompt you for any parameters set up (these work
differently for different database, but a "?" in the SQL will generate
the
prompt - even if you ahven't used SQL, go into the code, and replace any
parameters you want to be "live" with a "?")

This should now work ok - I'll monitor this thread, but don't expect
immediate replies ;)

Hope this helps

Sunil Jayakumar







jwr said:
That is exactly what I want to do -- I don't know how!
Can you assist or direct me to a website for information?

Thank you,
jwr
"Sunil Jayakumar" <sunil.jayakumar[at]gmail.com> wrote in message
Hi,

I think the best way wold be to actually build the template in Excel, and
access a query in the Access database.

You can pass parameters back into access when running the query, and
emailing out he sheet will simply "break" the link to Access, so
people
will
be able to view/edit numbers.

Hope this helps

Sunil Jayakumar

I have posted this previously in excel.links without an answer. I am at
a
standstill now. Your assistance is appreciated.


I have an access database. In this database, I have several forms and
reports that need to be sent via email to other individuals. They need
to
enter info into several locations and then return via email to me.

If I export the form/report to excel, the format is lost. Is it
possible
to
create a form/report in excel, import the info from my access database,
and
then email to another location without losing the info and also
allowing
that individual to enter info into specific cells?

Thanking you in advance.



www.ayyoo.com/credit-cards.html
Click to expand...
www.ayyoo.com/credit-cards.html
Click to expand...
Click to expand...
www.ayyoo.com/loans.html
 
I have created an MS Query and an excel template. The MS Query is saved as
the second sheet. I selected a column first and then tried just one cell --
I clicked on the data, went to data, filter, advanced filter, copy to - went
to sheet one and selected the cell. Then checked Unique records only. I
get the error that I can only use filtered data.

What am I doing wrong?

JR

Sunil Jayakumar said:
Hi,

This is a mail merge type of operation....

What you will need is 2 sheet - one with the data fields on it, and the
other with the form/template you propose to populate. You will need a unique
identifier, so your table knows which row to pick up to populate the form.
You should not be able to get multiple data rows for the form.

Then, just use a VLOOKUP to pull the data back, based on your unique
identifier.

Hope this helps

Sunil Jayakumar


jwr said:
Thank you so much.

I have managed to create the information from the access into an MS Query.
Now -- I want this information to be placed into certain fields on a
template that I will create in excel.

How do I create the template and have the information input in the correct
location?

Thanks in advance


Sunil Jayakumar said:
Hi,

Easy way: Open MS Query directly - usually in your C:\Program
Files\Microsoft Office\Office or Office11 Folder, file name MSQRY32.EXE.

File>Table Definition, select your database, and set your query - you can
also type in SQL directly. You can then go File>Save As... and save as a
*.dqy or *.qry file. Store these somewhere you can find them.

Back in Excel, Data>Import External Data>Import Data, and navigate to the
query. This should then prompt you for any parameters set up (these work
differently for different database, but a "?" in the SQL will generate
the
prompt - even if you ahven't used SQL, go into the code, and replace any
parameters you want to be "live" with a "?")

This should now work ok - I'll monitor this thread, but don't expect
immediate replies ;)

Hope this helps

Sunil Jayakumar







That is exactly what I want to do -- I don't know how!
Can you assist or direct me to a website for information?

Thank you,
jwr
"Sunil Jayakumar" <sunil.jayakumar[at]gmail.com> wrote in message
Hi,

I think the best way wold be to actually build the template in
Click to expand...
Excel,
and
access a query in the Access database.

You can pass parameters back into access when running the query, and
emailing out he sheet will simply "break" the link to Access, so
people
will
be able to view/edit numbers.

Hope this helps

Sunil Jayakumar

I have posted this previously in excel.links without an answer. I
Click to expand...
am
at
a
standstill now. Your assistance is appreciated.


I have an access database. In this database, I have several forms and
reports that need to be sent via email to other individuals. They need
to
enter info into several locations and then return via email to me.

If I export the form/report to excel, the format is lost. Is it
possible
to
create a form/report in excel, import the info from my access database,
and
then email to another location without losing the info and also
allowing
that individual to enter info into specific cells?

Thanking you in advance.



www.ayyoo.com/credit-cards.html




www.ayyoo.com/credit-cards.html
Click to expand...
Click to expand...
www.ayyoo.com/loans.html
Click to expand...
 
Hi,

The "copy to" on the advanced filter only works if you reference the same
sheet. There's no reason for it not to, but it just doesn't work.

If you try to copy it to another location on the same sheet, it will work.

Hope this helps

Sunil Jayakumar


jwr said:
I have created an MS Query and an excel template. The MS Query is saved as
the second sheet. I selected a column first and then tried just one
cell --
I clicked on the data, went to data, filter, advanced filter, copy to -
went
to sheet one and selected the cell. Then checked Unique records only. I
get the error that I can only use filtered data.

What am I doing wrong?

JR

Sunil Jayakumar said:
Hi,

This is a mail merge type of operation....

What you will need is 2 sheet - one with the data fields on it, and the
other with the form/template you propose to populate. You will need a unique
identifier, so your table knows which row to pick up to populate the
form.
You should not be able to get multiple data rows for the form.

Then, just use a VLOOKUP to pull the data back, based on your unique
identifier.

Hope this helps

Sunil Jayakumar


jwr said:
Thank you so much.

I have managed to create the information from the access into an MS Query.
Now -- I want this information to be placed into certain fields on a
template that I will create in excel.

How do I create the template and have the information input in the correct
location?

Thanks in advance


"Sunil Jayakumar" <sunil.jayakumar[at]gmail.com> wrote in message
Hi,

Easy way: Open MS Query directly - usually in your C:\Program
Files\Microsoft Office\Office or Office11 Folder, file name MSQRY32.EXE.

File>Table Definition, select your database, and set your query - you can
also type in SQL directly. You can then go File>Save As... and save as a
*.dqy or *.qry file. Store these somewhere you can find them.

Back in Excel, Data>Import External Data>Import Data, and navigate to the
query. This should then prompt you for any parameters set up (these work
differently for different database, but a "?" in the SQL will generate
the
prompt - even if you ahven't used SQL, go into the code, and replace any
parameters you want to be "live" with a "?")

This should now work ok - I'll monitor this thread, but don't expect
immediate replies ;)

Hope this helps

Sunil Jayakumar







That is exactly what I want to do -- I don't know how!
Can you assist or direct me to a website for information?

Thank you,
jwr
"Sunil Jayakumar" <sunil.jayakumar[at]gmail.com> wrote in message
Hi,

I think the best way wold be to actually build the template in Excel,
and
access a query in the Access database.

You can pass parameters back into access when running the query,
and
emailing out he sheet will simply "break" the link to Access, so
people
will
be able to view/edit numbers.

Hope this helps

Sunil Jayakumar

I have posted this previously in excel.links without an answer. I am
at
a
standstill now. Your assistance is appreciated.


I have an access database. In this database, I have several
forms
and
reports that need to be sent via email to other individuals.
They
need
to
enter info into several locations and then return via email to
me.

If I export the form/report to excel, the format is lost. Is it
possible
to
create a form/report in excel, import the info from my access
database,
and
then email to another location without losing the info and also
allowing
that individual to enter info into specific cells?

Thanking you in advance.



www.ayyoo.com/credit-cards.html




www.ayyoo.com/credit-cards.html
Click to expand...
www.ayyoo.com/loans.html
Click to expand...
Click to expand...
www.ayyoo.com/mortgages.html
 
You can filter the results to a different sheet, if you start on the
destination sheet. There are instructions here:

http://www.contextures.com/xladvfilter01.html#ExtractWs


Sunil said:
Hi,

The "copy to" on the advanced filter only works if you reference the same
sheet. There's no reason for it not to, but it just doesn't work.

If you try to copy it to another location on the same sheet, it will work.

Hope this helps

Sunil Jayakumar


I have created an MS Query and an excel template. The MS Query is saved as
the second sheet. I selected a column first and then tried just one
cell --
I clicked on the data, went to data, filter, advanced filter, copy to -
went
to sheet one and selected the cell. Then checked Unique records only. I
get the error that I can only use filtered data.

What am I doing wrong?

JR

Hi,

This is a mail merge type of operation....

What you will need is 2 sheet - one with the data fields on it, and the
other with the form/template you propose to populate. You will need a
unique

identifier, so your table knows which row to pick up to populate the
form.
You should not be able to get multiple data rows for the form.

Then, just use a VLOOKUP to pull the data back, based on your unique
identifier.

Hope this helps

Sunil Jayakumar



Thank you so much.

I have managed to create the information from the access into an MS
Query.

Now -- I want this information to be placed into certain fields on a
template that I will create in excel.

How do I create the template and have the information input in the
correct

location?

Thanks in advance


"Sunil Jayakumar" <sunil.jayakumar[at]gmail.com> wrote in message

Hi,

Easy way: Open MS Query directly - usually in your C:\Program
Files\Microsoft Office\Office or Office11 Folder, file name
MSQRY32.EXE.

File>Table Definition, select your database, and set your query - you
can

also type in SQL directly. You can then go File>Save As... and save as
a

*.dqy or *.qry file. Store these somewhere you can find them.

Back in Excel, Data>Import External Data>Import Data, and navigate to
the

query. This should then prompt you for any parameters set up (these
work

differently for different database, but a "?" in the SQL will generate
the
prompt - even if you ahven't used SQL, go into the code, and replace
any

parameters you want to be "live" with a "?")

This should now work ok - I'll monitor this thread, but don't expect
immediate replies ;)

Hope this helps

Sunil Jayakumar








That is exactly what I want to do -- I don't know how!
Can you assist or direct me to a website for information?

Thank you,
jwr
"Sunil Jayakumar" <sunil.jayakumar[at]gmail.com> wrote in message

Hi,

I think the best way wold be to actually build the template in
Excel,

and

access a query in the Access database.

You can pass parameters back into access when running the query,
and
emailing out he sheet will simply "break" the link to Access, so
people

will

be able to view/edit numbers.

Hope this helps

Sunil Jayakumar


I have posted this previously in excel.links without an answer. I
am

at

a

standstill now. Your assistance is appreciated.


I have an access database. In this database, I have several
forms

and

reports that need to be sent via email to other individuals.
They

need

to

enter info into several locations and then return via email to
me.

If I export the form/report to excel, the format is lost. Is it

possible

to
create a form/report in excel, import the info from my access

database,

and
then email to another location without losing the info and also
allowing
that individual to enter info into specific cells?

Thanking you in advance.
Click to expand...
Click to expand...
www.ayyoo.com/mortgages.html
Click to expand...
 
Back
Top