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/
.
