Transferring datas to excel

  • Thread starter Thread starter Faddrick remo
  • Start date Start date
F

Faddrick remo

Dear all,
Can somebody help me in rectfying the following problem.
I have a table called sales and the fields and the datas
are as
shown below
salesno salesempname salesempdept
1 vvvvv dd
2 hhhhh gg


I want to transfer the above datas to excel using vb in
the
follwoing format
salesno salesempname salesempdept salesno salesempname
salesempdept
1 vvvvv dd 2 hhhhh
gg


Like above i have 1000's of data's.The above shown data is
just an
example

Thanks in advance
Faddrick
 
Faddrick: If I read you correctly, you have thousands of
records which you want to export to Excel as one row with
repeating fields salesno, salesempname, and salesempdept.
Don't forget that Excel can not have more than 256
columns in any spreadsheet - 65,536 rows, but only 256
columns! If you mean you want two Access records per
Excel record, that's doable.

Ray
 
Dear Mr Ray,
Thanks for the help.
Yes you got my point very clearly.
I wanted to transfer maximum 5 or 6 access records per
excel record.
Can u give some idea or source code sothat i can work it
out?
Thank You,
Faddrick
 
Faddrick: The simplest way to complete the task is to
create one Excel record per Access record. Once you have
all the records in Excel, you can double, triple or
quadruble up as you see fit.

Open your Access database and rightclick on the file you
wish to export. Select Export and select the appropriate
version of Excel in the "Save as type:" text box. Now
enter the appropriate path information in the "Save in:"
text box, ensure the filename is what you want, and click
Save. You will now have an Excel file whose first row
matches your field names of the Access file.

If you really want to do it in code, say so and I'll get
you started.

Ray
 
Dear Mr Ray,
Thanks for your help.
The idea what u gave me is transferring the datas or
exporting the access data's from table to excel.
the idea is good and it worked but what iam now looking
for is putting my records in one line as i mentioned in my
previous query.
i have 2 tables
1 emp
2 emp details.
the fields and data's of both the tables are as follows.
table - emp
empno empname empic
1111 ssss 11

table - empdetails
empic empdependents empmomname empsal
11 ffff hhhh 3333
11 gggg iiii 2222
11 llll nnnn 5555

i made a query in access called empdetails which has all
the fields and data's of the tables and it is as
follows.
query - empdetails
empno empname empic empdependsnts empmomname empsal
1111 ssss 11 ffff hhhh 3333
1111 ssss 11 gggg iiii 2222
1111 ssss 11 llll nnnn 5555

Now what i want to do is transferring the above info
into excel as follows.
empno empname empic empdependsnts empmomname empsal
1111 ssss 11 ffff hhhh 3333 gggg iiii 2222 gggg iiii-
-2222 llll nnnn 5555.

the above shown line must on the single cell.
since i cant put it in same line i have just put it like
this.

can u pl help me.

Bye
Faddrick




























i have made a query
 
Hi Faddrick,

If you go to http://www.mvps.org/access and search for
concatenate
you will find a VBA function fConcatChild().

The following general approach should work:

1) Create a new query - I'll call it qryEmpDetails - on your empdetails
table that returns two fields: empic, and a second field consisting of
the other three fields concatenated with tab characters. In query design
view the field will look like this:
EmpData: [empdependents] & Chr(9) & [empmomname] & Chr(9) & [empsal]

2) Modify the code of fConcatChild() to use Chr(9) instead of ";" to
separate the fields in its output.

3) Create a new query on your emp table that uses fConcatChild() to get
and concatenate the EmpData fields from qryEmpDetails.

4) When this query is working properly, export it to a "tab-delimited"
text file with no text qualifier.

5) Import the text file into Excel.
 
Back
Top