Create Macro to import fixed width text from clipboard or text file and update table

[ghadley_00]

Hi,

I have a MS access database table for which I regularly need to import
fixed width text data. At present I have to to cut and paste the text
data from its source to a text file, save the file, import the text
file as fixed width text [it is not naturally delimited], and then run
an update query to copy the appropriate info into fields of a different
table.

Is it possible to write a macro to do all these steps? Also, is it
possible to import directly from the clipboard (to skip the step of
making a text file)?

Any help provided would be greatly appreciated.

Best wishes,

George Hadley
(e-mail address removed)

 

[Guest]

Hi George,

The first thing that strikes me is the cutting and pasting from the original
source to a text file. What is the original source? Maybe we can skip a step
right there.

Have you created an import specification? If not the next time you do a
manual import of the text data click on the Advanced button on one of the
first windows of the Import Wizard. Use it to create an import spec; save the
spec; and remember the name.

In a new macro named macImport create something like the following:
Action: Transfer Text
Transfer Type: Import Fixed Width
Specification Name: Name of your import spec created above.
Table Name: Name of the table that you're importing data into.
File Name: File name and path to your text file.
Has Field Names: No if there are no field names in the text file.
Action: OpenQuery
Query Name: The name of your query.

You might want to add the Action of SetWarnings No at the beginning and,
this is very important, SetWarnings Yes at the end of the macro so not to be
prompted if you really want to append these records, etc.

 

[John Nurick]

Hi George,

You can do most of it without even a macro. By writing VBA code it's
possible to get the data off the clipboard automatically.

For the macro solution, go like this. (By the way, I'm assuming that the
fixed-width text data always has the same structure of fields. If this
changes from day to day, things get *much* more complicated.)

1) Create a text file as normal.

2) Instead of importing it, link to it (File|Get External Data|Link). As
you work through the Text Import wizard, click the Advanced button and
store the settings for the file as an import specification, using a
convenient name (this is not strictly required but may come in handy in
future).

3) Modify your existing query so it takes its data from the linked
table.

Subsequently, all you need do is paste the new data into a text file as
now, and save the file in the same location and with the same name as
the file you used when you created the linked table. The linked table,
and therefore the query, will therefore use the updated file - so all
you need to is run the query (or run a macro that runs the query.

Post back if you want to get into the clipboard/VBA stuff.

Hi,

I have a MS access database table for which I regularly need to import
fixed width text data. At present I have to to cut and paste the text
data from its source to a text file, save the file, import the text
file as fixed width text [it is not naturally delimited], and then run
an update query to copy the appropriate info into fields of a different
table.

Is it possible to write a macro to do all these steps? Also, is it
possible to import directly from the clipboard (to skip the step of
making a text file)?

Any help provided would be greatly appreciated.

Best wishes,

George Hadley
(e-mail address removed)

 

[ghadley_00]

Thank you very much.

I'll give that a try, however, I do think the clipboard option /VBA
would be a cleaner solution (there are multiple users of the database
and I think it would be easier if I didn't have to worry about them
getting access to the text file).

Thanks again.

George.

 

[John Nurick]

Even when retrieving the data from the clipboard under program control,
I'd still be inclined to save it straight into a text file.

That way, one has the benefit of Access's text-file facilities (either
via a linked table or by modifying your existing query to get its data
direct from the text file). Otherwise, it's necessary to write VBA code
oneself to parse the string that comes from the clipboard into records
and fields and then put them into your table.

I'm not sure why you're worried about the users having access to the
text file. Are you worried (1) that they won't have access when they
need it, or (2) that they will be able to see its contents when you
don't want them to?

If (1), I don't see any problem. The text file is only needed to store
the data from the clipboard until the query has run to move it to the
actual Access table, and the only thing that needs access to it is the
process that creates the textfile and runs the query (or, in your
existing manual setup, the user who's doing this). So the text file can
go in some convenient folder on the user's local drive, and the linked
table will of course be in the front-end mdb file also on the local
drive. Meanwhile the back-end mdb file and the "real" table are in a
shared folder.

(All that assumes that you have split the database: this is strongly
recommended for any multi-user Access database.)

If (2), remember that if the data is on the clipboard they can see it
any time by opening Word or Notepad or something and clicking Paste.

 

[ghadley_00]

You are correct. I setup the process using the linked text file - a
very nice feature with which I was not familiar. Thanks for all your
help.

-George