Import Access data into Excel - Looking for programmer

Hello All.

I need some advice or paid help from someone in the business of doing the type
of work referred to in the Subject line.

I have a spreadhsheet, very complex with VB scripting. I did not produce it.
It came from a branch of government running a Tribunal that deals with landlord
and tenant disputes in Ontario, Canada. By filling in the various data fields
in the first of the 40 worksheets (the sheet’s labeled DATA), you then hit a
button and it exports the data to one of the other sheets based on which of
their 30 forms you are trying to produce, and it writes to the cells in the
other sheet in a format needed to create an fdf file. Adobe then opens up,
grabs the data in the proper fdf format from the proper worksheet (depending on
the form you’re producing) and imports the data into the editable pdf form.
It’s quite nice, and allows lawyers and paralegals who do a lot of applications
to this quasi-judicial Tribunal to produce professional looking PDF forms from
the excel data file. They can then print them, or fax them, or send them in as
PDFs to the Ontario Rental Housing Tribunal.

Here’s my problem. On the first sheet, the main data sheet with over 800 rows,
the applicant infomation needs to be entered every time I do a form, a notice of
termination, an application for termination, an affidavit of service, a motion
for setting aside a judgement, etc. It’s a pain to enter the applicant
information each time. The applicants are my clients, and I have about 200 of
them at any given timem mostly repeat customers.

I’d like to creatre an Access database, or Filemaker database, or even another
Excel sheet, to keep track of my clients who will eventually be applicants for
the purpose of the spreadsheet. There are 17 fields for "Applicant" in the
spreadsheet. I would rather enter my clients into Access (which I hate) or
Filemaker, or worst case, another Excel sheet (using a spreadsheet to do the job
of a database sucks) and import the PROPER client info when creating a new form
in the complicated spreadsheet rather than re-keying it each time I create a
form.

I’d like to do it as follows. When I come to the first of the 17 "Applicant"
fields on the "Data" sheet of the Tribunal’s worksheet, I’d like to run a macro
(or something) or have it ask me without doing anything, which client I’d like
to import. I’d like it to present me with a pull-down list so I could scroll
through and pick the Client Name from the database, click on it, and all 17
fields in the Access or Filemaker or Excel file would be imported into the
appropriate fields in that worksheet. I’d also be able to decline selecting one
of the existing records (if it’s a new client) and enter the 17 fields manually
into the big Excel Tribunal sheet, and then have it ask me if I would like to
create a new record in my Access or Filemaker database (or Excel spreadsheet).

The agent information fields (that’s me) can be filled in on the big
spreadsheet permanently and saved, since the agent never changes. But the
clients change each time, and it’s a pain to type all that stuff in, when I
typed the same client in the week before. The respondent information (the
person the action is being filed against) is new every time, so I have no choice
but to enter those names every time.

Thanks

Harry

Hello Harry:

You can definitely do this with Access (I agree, Access is not my favorite
app... (sorry MS)), and might be able to do same with Filemaker if it has
ODBC driver (I don't use it so can't say for sure, but becoming a common
feature. ODBC = Open Database Connectivity, Windows standard for data
access...). And there are a few different ways to go about it. The
SQL.REQUEST function might do the trick since you will only be working on one
record at a time. MSQuery is a little easier to set up and use, but it would
only be good for reading in data, not for creating a new record if the
applicant does not yet exist. The next step up would be to use ADO to write
VBA routines that can run SQL commands on your database - this gives complete
flexibility, within the limits of SQL, to read or update data. Another way
would be to use Automation to start Access running from within Excel and
control the session using VBA code.

All of these have their pros/cons. All methods except MSQuery would require
knowledge of ODBC and SQL, and the last two would require advanced VBA
knowledge. Not sure what your knowledge level is in these. If interested in
discussing, contact me at this address (which I have modified to avoid spam):
kgdcc(-at-)westelcom(-dot-)com.

K Dales
(P.S. though I live in New York State, I have some Ontario in my blood:
grandfather came from there, Orillia area)

"Harry" wrote:

> Hello All.
>
> I need some advice or paid help from someone in the business of doing the type
> of work referred to in the Subject line.
>
> I have a spreadhsheet, very complex with VB scripting. I did not produce it.
> It came from a branch of government running a Tribunal that deals with landlord
> and tenant disputes in Ontario, Canada. By filling in the various data fields
> in the first of the 40 worksheets (the sheet’s labeled DATA), you then hit a
> button and it exports the data to one of the other sheets based on which of
> their 30 forms you are trying to produce, and it writes to the cells in the
> other sheet in a format needed to create an fdf file. Adobe then opens up,
> grabs the data in the proper fdf format from the proper worksheet (depending on
> the form you’re producing) and imports the data into the editable pdf form.
> It’s quite nice, and allows lawyers and paralegals who do a lot of applications
> to this quasi-judicial Tribunal to produce professional looking PDF forms from
> the excel data file. They can then print them, or fax them, or send them in as
> PDFs to the Ontario Rental Housing Tribunal.
>
> Here’s my problem. On the first sheet, the main data sheet with over 800 rows,
> the applicant infomation needs to be entered every time I do a form, a notice of
> termination, an application for termination, an affidavit of service, a motion
> for setting aside a judgement, etc. It’s a pain to enter the applicant
> information each time. The applicants are my clients, and I have about 200 of
> them at any given timem mostly repeat customers.
>
> I’d like to creatre an Access database, or Filemaker database, or even another
> Excel sheet, to keep track of my clients who will eventually be applicants for
> the purpose of the spreadsheet. There are 17 fields for "Applicant" in the
> spreadsheet. I would rather enter my clients into Access (which I hate) or
> Filemaker, or worst case, another Excel sheet (using a spreadsheet to do the job
> of a database sucks) and import the PROPER client info when creating a new form
> in the complicated spreadsheet rather than re-keying it each time I create a
> form.
>
> I’d like to do it as follows. When I come to the first of the 17 "Applicant"
> fields on the "Data" sheet of the Tribunal’s worksheet, I’d like to run a macro
> (or something) or have it ask me without doing anything, which client I’d like
> to import. I’d like it to present me with a pull-down list so I could scroll
> through and pick the Client Name from the database, click on it, and all 17
> fields in the Access or Filemaker or Excel file would be imported into the
> appropriate fields in that worksheet. I’d also be able to decline selecting one
> of the existing records (if it’s a new client) and enter the 17 fields manually
> into the big Excel Tribunal sheet, and then have it ask me if I would like to
> create a new record in my Access or Filemaker database (or Excel spreadsheet).
>
> The agent information fields (that’s me) can be filled in on the big
> spreadsheet permanently and saved, since the agent never changes. But the
> clients change each time, and it’s a pain to type all that stuff in, when I
> typed the same client in the week before. The respondent information (the
> person the action is being filed against) is new every time, so I have no choice
> but to enter those names every time.
>
> Thanks
>
> Harry
>
>