Exporting Data from Access To Excel


R

Ryan

I have a customer who has a predefined Excel Spreadsheet layed out in a form
format. They want this spreadsheet populated with data from our existing
access database. They would also like the name of the excel file to change
based on information collected in the database. How do i populate specific
cells in the spreadsheet with data from my access database and rename the
excel file to what i need it to be. I am currently using access 2007, and
their sheet is excel 2003.

Any help would be appreciated.

Ryan
 
Ad

Advertisements

K

Ken Snell

PieterLinden via AccessMonster.com said:
Ryan,
I would start at Access Web. There's an example of CopyFromRecordset
there.
Ken Snell has a bunch of stuff on his website for doing that kind of thing
too. There should be some code in Developer's Handbook too. You
basically
pass an ADO recordset and Excel can read it and manipulate it.

Write Data From a Recordset into an EXCEL Worksheet using Automation (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#WriteRstFile
 
R

Rod

Ryan said:
I have a customer who has a predefined Excel Spreadsheet layed out in a
form
format. They want this spreadsheet populated with data from our existing
access database. They would also like the name of the excel file to
change
based on information collected in the database. How do i populate
specific
cells in the spreadsheet with data from my access database and rename the
excel file to what i need it to be. I am currently using access 2007, and
their sheet is excel 2003.

Any help would be appreciated.

Ryan


Two ways

1) Using VBA. Using a program you can put data in specific cells, format the
cells, etc. More-or-less anything you can do using Excel manually you can
get VBA to do.
Once you get the hang of it then this is easy to do.

2) Put all the relevant data into a single sheet of the spreadsheet and get
the spreadsheet to pick-out what it needs. This is a bit more messy but
maybe preferable if you are an expert at Excel and not Access. You can of
course use a query in the spreadsheet to pull the data out of Access.

I would favour the first option, especially if you are going to be doing a
lot of this sort of thing.
 
R

Ryan

Hi Rod,
I am neither an expert in access or excel....but i have tried the second way
using kens coding and it works great, i would like however to learn the 1st
way., if there is any docs on the subject that you could poiint me in the
right direction or give me a samp,e code to play with it would be appreciated.

Thanks

Ryan
 
R

Ryan

Hi Ken,

how would i filter the query based on a field on the report that my button
is to call the function? The filter field is a number.
 
Ad

Advertisements

R

Ryan

This works great Ken for exporting the whole query, how do I filter the query
down to one record. I tried running the query in a form but as soon as the
code calls the query it resets and i get all 12000 records. I checked out
the other codes on your site, but to be honest, its all new to me. where
would i need to enter a criteria or filter in the code to filter the query?
 
K

Ken Snell

You cannot use a field from a report as the source of data to be used in a
filter, as data displayed in a report cannot be read by VBA, macro, form,
query, or any other object. You'll need to read the filtering information
from somewhere else, perhaps a form that is being used to run the report?

Post more details about your setup and post your code that you're using.

Essentially, after you identify how to read the filtering data, what one
would do is to change this generic step in my code example (note that there
are other ways to do this too, but this is probably the most straightforward
way to do it):

' Replace QueryOrTableName with the real name of the table or query
' whose data are to be written into the worksheet
Set rst = dbs.OpenRecordset("QueryOrTableName", dbOpenDynaset, dbReadOnly)


With something like this:

' Replace QueryOrTableName with the real name of the table or query
' whose data are to be written into the worksheet
Set rst = dbs.OpenRecordset("SELECT * FROM QueryOrTableName WHERE
FieldNameInQuery = " & VariableOrFormControlThatHasFilteringValue,
dbOpenDynaset, dbReadOnly)
--

Ken Snell
http://www.accessmvp.com/KDSnell/
 
R

Ryan

Hi Ken,

I added this to your code and it seems to work fine.

Set rstUnfiltered = dbs.OpenRecordset("TAQAREPORT")
rstUnfiltered.Filter = "[Inspection ID] = " & Forms![PSV Work
Order]![INSPECTION DATA].Form![Inspection ID]
Set rstFiltered = rstUnfiltered.OpenRecordset

Set rst = rstFiltered
Unfortunately now, my customer has informed me that they need each cell
filled in seperately, and that there sheet cannot reference another worksheet
in the file. My goal was to export the data to a second worksheet then have
the 1st worksheet reference the second worksheet. This apparently will not
work for them. So Now I need to figure out how to copy to new file the first
sheet with pastespecial values so that the values get copied over not the
references....or learn how to write each field into a specific cell on a
worksheet....either way, have never done either, and will be a challenge.
thanks for all your help up to this point.

Do you freelance? Might need to hire you to write some of this code for me.

Ryan
 
Ad

Advertisements

K

Ken Snell

I have been known to do work for hire, but my regular work schedule right
now is too full to let me do this right now. If you want someone to help you
for a fee, send me an email at the address on the home page in my signature,
and I'll put you in touch with a good ACCESS person who's done work with
EXCEL and ACCESS working with each other.

Adding the filtering of the recordset is one of the other approaches that I
didn't mention <smile>.

In my sample code, I assumed that you'd be writing the data into sequential
cells and rows in EXCEL. However, you can tweak the code to write to the
specific cells that you want, by referencing the cell for each field in each
recordset record.

This is the basic code from my page:

' write data to worksheet
Do While rst.EOF = False
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value =
rst.Fields(lngColumn).Value
Next lngColumn
rst.MoveNext
Set xlc = xlc.Offset(1,0)
Loop

You could use something like this:

' write data to worksheet
Do While rst.EOF = False
xlc.Offset(0, 0).Value =
rst.Fields("NameOfOneFieldInRecordset).Value ' column A in EXCEL
xlc.Offset(0, 2).Value =
rst.Fields("NameOfAnotherFieldInRecordset).Value ' column C in EXCEL
xlc.Offset(0, 5).Value =
rst.Fields("NameOfYetAnotherFieldInRecordset).Value ' column F in EXCEL
' etc. you just keep specifying the exact cell to which a field
is to be written
rst.MoveNext
Set xlc = xlc.Offset(1,0) ' this can change if you don't want
sequential rows
Loop

Post back with more specifics about how the data are laid out in the EXCEL
file, perhaps we can give you just the right boost in the direction you need
to go.

Else, you can use Automation to open the two EXCEL files and to copy /
paste-special the data from one worksheet to another, just like you can do
in EXCEL VBA. In my sample code, you can see the steps that are used to open
the one workbook file into which you're writing the original data, and then
you can use similar code to set another workbook object to the other
workbook file's Open method, and go from there. Exact code that you'd need
obviously depends upon what you want to exactly do.
--

Ken Snell
http://www.accessmvp.com/KDSnell/





Ryan said:
Hi Ken,

I added this to your code and it seems to work fine.

Set rstUnfiltered = dbs.OpenRecordset("TAQAREPORT")
rstUnfiltered.Filter = "[Inspection ID] = " & Forms![PSV Work
Order]![INSPECTION DATA].Form![Inspection ID]
Set rstFiltered = rstUnfiltered.OpenRecordset

Set rst = rstFiltered
Unfortunately now, my customer has informed me that they need each cell
filled in seperately, and that there sheet cannot reference another
worksheet
in the file. My goal was to export the data to a second worksheet then
have
the 1st worksheet reference the second worksheet. This apparently will
not
work for them. So Now I need to figure out how to copy to new file the
first
sheet with pastespecial values so that the values get copied over not the
references....or learn how to write each field into a specific cell on a
worksheet....either way, have never done either, and will be a challenge.
thanks for all your help up to this point.

Do you freelance? Might need to hire you to write some of this code for
me.

Ryan
Ken Snell said:
You cannot use a field from a report as the source of data to be used in
a
filter, as data displayed in a report cannot be read by VBA, macro, form,
query, or any other object. You'll need to read the filtering information
from somewhere else, perhaps a form that is being used to run the report?

Post more details about your setup and post your code that you're using.

Essentially, after you identify how to read the filtering data, what one
would do is to change this generic step in my code example (note that
there
are other ways to do this too, but this is probably the most
straightforward
way to do it):

' Replace QueryOrTableName with the real name of the table or query
' whose data are to be written into the worksheet
Set rst = dbs.OpenRecordset("QueryOrTableName", dbOpenDynaset,
dbReadOnly)


With something like this:

' Replace QueryOrTableName with the real name of the table or query
' whose data are to be written into the worksheet
Set rst = dbs.OpenRecordset("SELECT * FROM QueryOrTableName WHERE
FieldNameInQuery = " & VariableOrFormControlThatHasFilteringValue,
dbOpenDynaset, dbReadOnly)
--

Ken Snell
http://www.accessmvp.com/KDSnell/







.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top