Convert Excel Sheet to Table

#1 09-07-2008, 03:50 AM

Hi
Pls. help me to convert my Excel sheet into access Table by writing
recordset. Sheet is some how like this:
Code Kwt Bhn Qtr
USD 0.268 0.377 3.643
BHD 0.710 1.000 9.654
DKK 0.053 0.073 0.717
I want to convert with access table (TblCurrency) in this way:
Country Code Rate
Kwt USD 0.268
Kwt BHD 0.710
Kwt DKK 0.053
Bhn USD 0.377
Bhn BHD 1.000
Bhn DKK 0.073
Qtr USD 3.643
Qtr BHD 9.654
Qtr DKK 0.717
please show me how i will write recordset to convert Excel sheet
thanks

#2 09-07-2008, 06:58 PM

On Sun, 7 Sep 2008 00:50:06 -0700, Wahab <Wahab@discussions.microsoft.com>
wrote:

>Hi
>Pls. help me to convert my Excel sheet into access Table by writing
>recordset. Sheet is some how like this:
>Code Kwt Bhn Qtr
>USD 0.268 0.377 3.643
>BHD 0.710 1.000 9.654
>DKK 0.053 0.073 0.717
>I want to convert with access table (TblCurrency) in this way:
>Country Code Rate
>Kwt USD 0.268
>Kwt BHD 0.710
>Kwt DKK 0.053
>Bhn USD 0.377
>Bhn BHD 1.000
>Bhn DKK 0.073
>Qtr USD 3.643
>Qtr BHD 9.654
>Qtr DKK 0.717
>please show me how i will write recordset to convert Excel sheet
>thanks
>

No code is needed, or even appropriate. Instead you can use a "Normalizing
Union Query".

Use File... Get External Data... Link to link to your table (well, you can use
code using the TransferSpreadsheet method if that's more convenient). Have
your target tblCurrency already defined. Create a UNION query based on the
linked spreadsheet:

SELECT "Kwt" AS COuntry, [code], [Kwt] AS RATE
FROM linkedspreadsheet
UNION ALL
SELECT "Bhn", [code], [BHN]
FROM linkedspreadsheet
UNION ALL
SELECT "Qtr", [code], [Qtr]
FROM linkedspreadsheet;

Add more select clauses if you have more countries.

Base an Append query on this Union query and run it to append the data into
your table.
--

John W. Vinson [MVP]

#3 09-08-2008, 03:11 AM

"John W. Vinson" wrote:

> On Sun, 7 Sep 2008 00:50:06 -0700, Wahab <Wahab@discussions.microsoft.com>
> wrote:
>
> >Hi
> >Pls. help me to convert my Excel sheet into access Table by writing
> >recordset. Sheet is some how like this:
> >Code Kwt Bhn Qtr
> >USD 0.268 0.377 3.643
> >BHD 0.710 1.000 9.654
> >DKK 0.053 0.073 0.717
> >I want to convert with access table (TblCurrency) in this way:
> >Country Code Rate
> >Kwt USD 0.268
> >Kwt BHD 0.710
> >Kwt DKK 0.053
> >Bhn USD 0.377
> >Bhn BHD 1.000
> >Bhn DKK 0.073
> >Qtr USD 3.643
> >Qtr BHD 9.654
> >Qtr DKK 0.717
> >please show me how i will write recordset to convert Excel sheet
> >thanks
> >
>
> No code is needed, or even appropriate. Instead you can use a "Normalizing
> Union Query".
>
> Use File... Get External Data... Link to link to your table (well, you can use
> code using the TransferSpreadsheet method if that's more convenient). Have
> your target tblCurrency already defined. Create a UNION query based on the
> linked spreadsheet:
>
> SELECT "Kwt" AS COuntry, [code], [Kwt] AS RATE
> FROM linkedspreadsheet
> UNION ALL
> SELECT "Bhn", [code], [BHN]
> FROM linkedspreadsheet
> UNION ALL
> SELECT "Qtr", [code], [Qtr]
> FROM linkedspreadsheet;
>
> Add more select clauses if you have more countries.
>
> Base an Append query on this Union query and run it to append the data into
> your table.
> --
>
> John W. Vinson [MVP]
>
Thanks Mr.John
This will not solve my problem, sometime countries are increasing or
decreasing and I have more than 110 countries and currencies and want to
compare the monthly rate form past and current month and feed in system with
correct decimals, slight mistake in entry gives hell of difference. My idea
is to compare both tables and mark only fields which rates are changed, so I
can enter those rates only and minimise the entry mistakes.
For this reason I need to write code which will make my job automatically. I
appreciate if someone will give me how to write codes.
Thanks and regards

#4 09-08-2008, 12:24 PM

On Mon, 8 Sep 2008 00:11:01 -0700, Wahab <Wahab@discussions.microsoft.com>
wrote:

>This will not solve my problem, sometime countries are increasing or
>decreasing and I have more than 110 countries and currencies and want to
>compare the monthly rate form past and current month and feed in system with
>correct decimals, slight mistake in entry gives hell of difference. My idea
>is to compare both tables and mark only fields which rates are changed, so I
>can enter those rates only and minimise the entry mistakes.
>For this reason I need to write code which will make my job automatically. I
>appreciate if someone will give me how to write codes.
>Thanks and regards

Well, it would have helped to say so.

You can still link to the spreadsheet and write VBA code to loop through its
Fields collection and construct SQL, or open a recordset based on your actual
table. Perhaps you should start a new thread posting these additional
requirements and the structure of your Access table, so that someone else
could jump in.
--

John W. Vinson [MVP]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode