outpulsing data of a form in vertical format, not horizontal

[dave]

I have a form that asks around 15 questions. I want to
ultimately outpulse the results in a format that I can
copy and paste into as400. Here is my problem:

When I copy and paste, it shows the fields horizontally,
as it should, but I want it to show vertically.

Example:

Call back hours: 0800-1700
Call back Name: John Doe
Date of Call: 10/7/03

Here is what it outpulses if I send it to an excell doc /
copy and paste into an excell doc:

Call back hours: Call back Name: Date of Call:
0800-1700 John Doe 10/7/03

I want it to show:
Call back hours: 0800-1700
Call back Name: John Doe
Date of Call: 10/7/03

Any ideas?

Ultimate goal is when I click a command button, it will
outpulse it in a report type deal in access that I can
copy / paste into as400 in the format shown above.
..

I have heard that outpulsing this to a report, then
printing the report to file works, and it does, but I wish
to only outpulse the last record into the excel doc, aka,
the one I just entered in.

Also, a suggestion is to create vba code to outpulse the
data into a txt file, which I would think would be
easiest, but I don't know the code...anyone have any ideas
on the code for such a request?

 

[Rebecca Smith]

Hello Dave

You could try something like this (this is air code but it should work with
a bit of tweaking).

Dim xl As Object, wksheet As New Excel.Worksheet
Dim strPath
' the worksheet path

Dim i as integer
Dim RS as DAO.Recordset, DB as DAO.Database

Set DB = CurrentDB()

Set RS = .........

strPath = "path etc. "& ".xls"
Windows.Application.Workbooks.Open FileName:=strPath
Workbooks.Application.ActiveWorkbook.Activate

For i = 1 to RS.Fields.Count
Cells(i, 1).text = RS.Fields(i).Name
Cells(i, 2).text = RS.Fields(i).Value
next

ActiveWorkbook.Close
RS.close
Set RS = nothing
Set DB = nothing

Hope it helps

Rebecca

 

[Dave]

This is looking exactly what I am looking for..but some of
this code I don't understand, or not well enough to know
how to modify it.

Set RS = .........

RS=record source? database name? I don't know what this
is.

strPath = "path etc. "& ".xls"

"path etc."& ".xls" what is path etc. supposed to be?
What path do I put here? Access filename
path? .xls...does that equal the output excel file?

I think I understand the rest, and I think the rest
doesn't need to be changed...but do you think it could be
possible for you to put in fictional names / paths to the
places where I need to modify code? or bold it?

Once again, thanks for your help!!!! I know this is
exactly what I am looking for and have been searching for
a month

Feel free to email at (e-mail address removed) if you choose

 

[Guest]

I receive an error on the line:

Dim xl As Object, wksheet As New Excel.Worksheet

access doesn't recognize the wksheet As New Excel.Worksheet

I couldn't find Excel as the drop down vba after New...is
it because I am using 97 and it isn't recognized?

 

[Rebecca Smith]

This is looking exactly what I am looking for..but some of
this code I don't understand, or not well enough to know
how to modify it. Okay.


RS=record source? database name? I don't know what this
is.

Something like DB.OpenRecordset ("Query name etc", dbopendynaset) - you'll
need to add dbseechanges if you're using an SQL statement instead of a query
or table name.

"path etc."& ".xls" what is path etc. supposed to be?
What path do I put here? Access filename
path? .xls...does that equal the output excel file?

The path to the excel file.

And you'll need to set MS Excel as a reference library. If you need any
more help, let me know.

All the best

Rebecca

 

[dave]

I receive an error on the line:

Dim xl As Object, wksheet As New Excel.Worksheet

access doesn't recognize the wksheet As New Excel.Worksheet

I couldn't find Excel as the drop down vba after New...is
it because I am using 97 and it isn't recognized?

 

[Rebecca Smith]

Hello Dave

Sorry - I was a bit scatty with my explanation!

Go to Tools, References and scroll down till you see Microsoft Excel Object
Library. You'll probably need to choose Microsoft Office as well and
definitely DAO library, if it's not there already.

Let me know if I can expain anything else.

All the best

Rebecca

 

[Dave]

Excellent!!! Very close!

Now, I get a run time error 1004 unable to set the text
property of the range class on this line of code:

Cells(i, 1).Text = RS.Fields(i).Name

now what?

so close and thanks!

 

[Rebecca Smith]

Excellent!!! Very close!

Now, I get a run time error 1004 unable to set the text
property of the range class on this line of code:

Cells(i, 1).Text = RS.Fields(i).Name

Try Cells(i, 1).value = RS.Fields(i).Name

 

[Dave]

I am getting errors while trying this, verying from run-
time error 3265 "item not found in collection" if i have
data in the form...if I don't, then I get run time error
1004 on
Cells(i, 2).Text = RS.Fields(i).Value

if i change .text to .name or .value i get 1004 that name
is no valid

if i change it to .value i get 3265 item not found in
collection

could you describe what:
Cells(i, 1).Value = RS.Fields(i).Name
Cells(i, 2).Value = RS.Fields(i).Value

does or is supposed to do then i could probably figure it
out? thanks!

 

[Rebecca Smith]

I am getting errors while trying this, verying from run-
time error 3265 "item not found in collection" if i have
data in the form...if I don't, then I get run time error
1004 on
Cells(i, 2).Text = RS.Fields(i).Value
Bugger!!!


if i change .text to .name or .value i get 1004 that name
is no valid

if i change it to .value i get 3265 item not found in
collection

could you describe what:
Cells(i, 1).Value = RS.Fields(i).Name
Cells(i, 2).Value = RS.Fields(i).Value

Basically it's looping through (using i as a variable) from 1 to the number
of fields. Then it's saying, e.g. if i = 3, then Cell (row 3, column 1) =
the name of field number 3 in the recordset and that cell (row 3, column 2)
= the value of field 3.

:-(

I'll have a think and see if I can come up with something else!

All the best
Rebecca

 

[Dave]

Thanks REbecca

If I do Excel.ActiveWorkbook.Sheets("Long Distance").Cells
(R, C).Value = whatever

it works...but I have to do it for each question in my
form...and I forgot how I made it work the one time it
did

Thanks again!

 

[Rebecca Smith]

Hey Dave

You might want to try debugging. Click to the left of one line in the code,
so that you see a red dot on the side and hit F8. Then try CTRL G for each
line of code (e.g. ?RS.Fields(i).Value) and if it doesn't show a value,
that'll probably be the problem line. Let me know how it goes.

Rebecca

 

[Dave]

I am receiving errors on the line
Cells(i, 2).value = RS.Fields(i).Value

It is giving me
: BatchCollisionCount : <Operation is not supported
for this type of object.> : Long
: BatchCollisions : <Operation is not supported for
this type of object.> : Variant
: BatchSize : <Operation is not supported for this
type of object.> : Long
All under the RS expression

Hope this helps

 

[Rebecca Smith]

Can you tell me what libraries you have ticked under Tools - References?

Thanks

Rebecca

 

[Dave]

Microsoft Visual Basics for Applications
Microsoft Access 8.0 Object Library
Microsoft Excell 8.0 Object Library
Microsoft Office 9.0 Object Library
Microsoft DAO 3.6 Object Library

Thanks!

 

[Rebecca Smith]

There are a couple of things you could try. Select OLE Automation as a
library - it may or may not help - not sure!

Otherwise, try Cells(i.row, 2).value = RS.Fields(i).Value

Hope it helps!!! We'll get there eventually.

Rebecca

 

[Dave]

None of those worked...but I have it:

For i = 1 To 10
Cells(i, 2) = RS.Fields(i).Value

Cells(i, 2) = RS.Fields(i).Value

Next

This works grea!!! But!!!

The forms I made are based on a table, and not queries...I
have made the queries and made the forms to work fine
(form wise)...but, here is where the problem lies now.

Set RS = DB.OpenRecordset("Issues", dbOpenDynaset)

If I change "Issues" to the query name of qryCkt...then I
get error messages stating RS.Fields(i).Value..item no
found in collection. How do I tell it to call from a
query instead of a table?

Thanks!

 

[Rebecca Smith]

None of those worked...but I have it:

For i = 1 To 10
Cells(i, 2) = RS.Fields(i).Value

Cells(i, 2) = RS.Fields(i).Value

Next

This works grea!!! But!!!

The forms I made are based on a table, and not queries...I
have made the queries and made the forms to work fine
(form wise)...but, here is where the problem lies now.

Set RS = DB.OpenRecordset("Issues", dbOpenDynaset)

Try using an SQL statement instead. e.g. DB.openrecordset ("Select * from
Issues", dbopendynaset, dbseechanges)

you have to use dbseechanges if you use an sql statement.

Good luck!!!

 

[Dave]

If i use select *, won't that list all the fields that are
in my table? Each one will have only 8 or 9 or thet total
30 or so from the table. Or do I replace the * with the
fields I want? If that is the case, how do I seperate
them? (meaning do I put them all in paranthees split by a
comma?) thanks!

-----Original Message-----



Try using an SQL statement instead. e.g.

DB.openrecordset ("Select * from