Question...

  • Thread starter Thread starter skc
  • Start date Start date
S

skc

I have a table that contains car registration plates that
are entered everyday into frontpage forms on the web.
These are posted into a Access table. I then have a query
to extract only registrations that begin with a 'X' and
these are sent to an external agency (pasted into Excel
from a FPRW .asp page) who then populate the spreadsheet
with extra fields (Make, Model, Year, CC etc...).

I then want to paste back into my table only those files
that are on the spreadsheet.

Is there a special statement I can use in Access, to read
my Spreadsheet and post into the fields the data that I
require. E.g., X234JJD (Access) --> X234JJD (Excel) then
copy all the data into the fields Make, Model, Year...

Help will be appreciated.

Thanks in advance,

skc
 
Quite easy. Go to menu item File > Get External Data > Link Files, and link
your Excel spreadsheet; you will then "see" it as a table in Access.
Then make a query and include your Access table and the linked spreadsheet;
join the two on the registration field, and drag the fields to be updated
from the Access table to the grid. Then go Query > Update Query, and in the
Update To line in the grid, type for each field to be updated the name of
the corresponding field in the linked spreadsheet, enclosed in [ ]. Run the
query and you are done.
Note: if the names of the fields in the spreadsheet are the sam eas in your
Access table, you will need to precede it with the name of the linked table
(as it appears in the database window), so Access can tell which field to
read the values from. e.g. [MyLinkedTable].[FieldName]

HTH,
Nikos
 
Nikos,

This worked like a dream thanks!

I have another question though...

When I ran the query to update I saved it. What I want to
do is to rerun the same query on another linked Excel
sheet as I may get these Excel sheets for updates every
few days or so. Do I just save the query in query view
and make sure the linked sheet is named the same as the
one in the query? I may have two or three types of sheets
that I may need to link for updates.

Please advise.

Thanks.

skc
-----Original Message-----
Quite easy. Go to menu item File > Get External Data > Link Files, and link
your Excel spreadsheet; you will then "see" it as a table in Access.
Then make a query and include your Access table and the linked spreadsheet;
join the two on the registration field, and drag the fields to be updated
from the Access table to the grid. Then go Query > Update Query, and in the
Update To line in the grid, type for each field to be updated the name of
the corresponding field in the linked spreadsheet, enclosed in [ ]. Run the
query and you are done.
Note: if the names of the fields in the spreadsheet are the sam eas in your
Access table, you will need to precede it with the name of the linked table
(as it appears in the database window), so Access can tell which field to
read the values from. e.g. [MyLinkedTable].[FieldName]

HTH,
Nikos

I have a table that contains car registration plates that
are entered everyday into frontpage forms on the web.
These are posted into a Access table. I then have a query
to extract only registrations that begin with a 'X' and
these are sent to an external agency (pasted into Excel
from a FPRW .asp page) who then populate the spreadsheet
with extra fields (Make, Model, Year, CC etc...).

I then want to paste back into my table only those files
that are on the spreadsheet.

Is there a special statement I can use in Access, to read
my Spreadsheet and post into the fields the data that I
require. E.g., X234JJD (Access) --> X234JJD (Excel) then
copy all the data into the fields Make, Model, Year...

Help will be appreciated.

Thanks in advance,

skc
Click to expand...


.
Click to expand...
 
Saving a new Excel workbook with the same name in the same folder will do
the trick, as long as the format (Sheet name, Column headers and data types)
is the same.
Now, for different types of sheets, I would guess two or three different
linked sheets and their respective update queries, would do the trick.

For your info, it is possible to change the link for an external worksheet
programatically, so you could even get the standard Windows File Open
dialog, select the workbook and link it, in order to work with a different
one every time! Just takes a litthe VBA.

HTH,
Nikos

skc said:
Nikos,

This worked like a dream thanks!

I have another question though...

When I ran the query to update I saved it. What I want to
do is to rerun the same query on another linked Excel
sheet as I may get these Excel sheets for updates every
few days or so. Do I just save the query in query view
and make sure the linked sheet is named the same as the
one in the query? I may have two or three types of sheets
that I may need to link for updates.

Please advise.

Thanks.

skc
-----Original Message-----
Quite easy. Go to menu item File > Get External Data > Link Files, and link
your Excel spreadsheet; you will then "see" it as a table in Access.
Then make a query and include your Access table and the linked spreadsheet;
join the two on the registration field, and drag the fields to be updated
from the Access table to the grid. Then go Query > Update Query, and in the
Update To line in the grid, type for each field to be updated the name of
the corresponding field in the linked spreadsheet, enclosed in [ ]. Run the
query and you are done.
Note: if the names of the fields in the spreadsheet are the sam eas in your
Access table, you will need to precede it with the name of the linked table
(as it appears in the database window), so Access can tell which field to
read the values from. e.g. [MyLinkedTable].[FieldName]

HTH,
Nikos

I have a table that contains car registration plates that
are entered everyday into frontpage forms on the web.
These are posted into a Access table. I then have a query
to extract only registrations that begin with a 'X' and
these are sent to an external agency (pasted into Excel
from a FPRW .asp page) who then populate the spreadsheet
with extra fields (Make, Model, Year, CC etc...).

I then want to paste back into my table only those files
that are on the spreadsheet.

Is there a special statement I can use in Access, to read
my Spreadsheet and post into the fields the data that I
require. E.g., X234JJD (Access) --> X234JJD (Excel) then
copy all the data into the fields Make, Model, Year...

Help will be appreciated.

Thanks in advance,

skc
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top