Hi,

I'm creating a query in Excel from my AS/400 server. It's working fine but i
need to do some changes! (i'm doing them manually but don't like that...)
1 - the date comes like "10207" when i want "01/02/07"
2 - all the values in my sheet need to be / by 1000
Question: where can i setup these things? in the query? in VBA? and how......

hope that someone can help me.
Thanks,
Barbara

Hope this help.

1. To change the date format, break the date that you get into 3 columns
using a text to column under data(You could use fixed width). Then just
concatenate in the format you want using / as delimiters using the formula
Concatenate.

2. To divide everything by 1000, write 1000 in a cell, copy, select the
range you want to divide by 1000, do a paste special, values and select
divide in the lower part of the paste special window.

Thanks.

"Barbara" wrote:

Hi,

I'm creating a query in Excel from my AS/400 server. It's working fine but i
need to do some changes! (i'm doing them manually but don't like that...)
1 - the date comes like "10207" when i want "01/02/07"
2 - all the values in my sheet need to be / by 1000
Question: where can i setup these things? in the query? in VBA? and how......

hope that someone can help me.
Thanks,
Barbara

Thanks for your reply. But I was wondering if it's possible to setup the
query with these formats so that when you open your excel you don't have to
format the cells!

Need some more help.
Barbara

"Vivek Shah" wrote:

Hope this help.

1. To change the date format, break the date that you get into 3 columns
using a text to column under data(You could use fixed width). Then just
concatenate in the format you want using / as delimiters using the formula
Concatenate.

2. To divide everything by 1000, write 1000 in a cell, copy, select the
range you want to divide by 1000, do a paste special, values and select
divide in the lower part of the paste special window.

Thanks.

"Barbara" wrote:

Hi,

I'm creating a query in Excel from my AS/400 server. It's working fine but i
need to do some changes! (i'm doing them manually but don't like that...)
1 - the date comes like "10207" when i want "01/02/07"
2 - all the values in my sheet need to be / by 1000
Question: where can i setup these things? in the query? in VBA? and how......

hope that someone can help me.
Thanks,
Barbara

Yes - you can have a macro which run everytime you open a specific workbook -
but sorry to say I am not too proficient @ macros, but can give it a try.

I shall try and send a simple macro to do that - give you a way to modify it
in your file.


"Barbara" wrote:

Thanks for your reply. But I was wondering if it's possible to setup the
query with these formats so that when you open your excel you don't have to
format the cells!

Need some more help.
Barbara

"Vivek Shah" wrote:

Hope this help.

1. To change the date format, break the date that you get into 3 columns
using a text to column under data(You could use fixed width). Then just
concatenate in the format you want using / as delimiters using the formula
Concatenate.

2. To divide everything by 1000, write 1000 in a cell, copy, select the
range you want to divide by 1000, do a paste special, values and select
divide in the lower part of the paste special window.

Thanks.

"Barbara" wrote:

Hi,

I'm creating a query in Excel from my AS/400 server. It's working fine but i
need to do some changes! (i'm doing them manually but don't like that...)
1 - the date comes like "10207" when i want "01/02/07"
2 - all the values in my sheet need to be / by 1000
Question: where can i setup these things? in the query? in VBA? and how......

hope that someone can help me.
Thanks,
Barbara

OK! I 'll wait. Thanks a lot for your help!


"Vivek Shah" wrote:

Yes - you can have a macro which run everytime you open a specific workbook -
but sorry to say I am not too proficient @ macros, but can give it a try.

I shall try and send a simple macro to do that - give you a way to modify it
in your file.


"Barbara" wrote:

Thanks for your reply. But I was wondering if it's possible to setup the
query with these formats so that when you open your excel you don't have to
format the cells!

Need some more help.
Barbara

"Vivek Shah" wrote:

Hope this help.

1. To change the date format, break the date that you get into 3 columns
using a text to column under data(You could use fixed width). Then just
concatenate in the format you want using / as delimiters using the formula
Concatenate.

2. To divide everything by 1000, write 1000 in a cell, copy, select the
range you want to divide by 1000, do a paste special, values and select
divide in the lower part of the paste special window.

Thanks.

"Barbara" wrote:

Hi,

I'm creating a query in Excel from my AS/400 server. It's working fine but i
need to do some changes! (i'm doing them manually but don't like that...)
1 - the date comes like "10207" when i want "01/02/07"
2 - all the values in my sheet need to be / by 1000
Question: where can i setup these things? in the query? in VBA? and how......

hope that someone can help me.
Thanks,
Barbara