Share variable between Access & Excel

[Brian]

I have an Access app that Shells to Excel Sheet#1 containing an Autpen
macro which, in turn, opens and reformats various parts of Sheet#2 in
preparation for import into Access. I would like to consolidate the
references to the location of Sheet#2 so that I can pass the name/location of
Sheet#2 to Sheet#1 rather than hard-coding the reference within both Access &
Sheet#1. If I update the path right now, I have to update both the Access DB
& Excel Sheet #1.

Does anyone have a clever way to do this? Perhaps store the path as a string
in a table and have both Excel & Access read from the table at runtime? I can
only guess how to do that.

 

[Brian]

OK, give me a hint here on how to do the following from Access:

1. The incoming raw data file has no column headings. When I import, Access
seems to require these and that they match the field names when inserting
into an existing table. To avoid this problem, my macro inserts a header line
containing valid field names.

2. When the first incoming record happens to be all digits for an
alphanumeric (text) field, Access reads it as numeric, then generates import
errors upon reaching those fields having mixed alphanumeric characters. This
is happening even though the table into which I am importing has that field
formatted as text. To solve this one, my macro inserts a dummy line just
below the header, having numbers/literals in the correct columns so that
Access will recognize text-based columns as text even when the first true
entry is entirely digits. I strip this line out after import.

Better ideas?