Share variable between Access & Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Access app that Shells to Excel Sheet#1 containing an Auto_Open
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.
 
It might be easier to do everything from Access, using Excel VBA
Automation, instead of your Auto_Open macro.


I have an Access app that Shells to Excel Sheet#1 containing an Auto_Open
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.
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
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?
 
Most of my Access Automation programming has targeted Word rather than
Excel, and I haven't done a huge amount of Excel VBA programming
(though I have done some). I am making the following assumptions:

1) You know the name of the Excel file you are trying to import
(obvious, I know).
2) The Excel file either consists of a single worksheet and/or the
data to be imported is in a named range.
3) You either know how many rows you are trying to import or have a
"flag value" in one of the columns in the spreadsheet to identify the
last record.

You would need to include the appropriate Excel Library in your
References, and would then start up an instance of Excel and open the
source spreadsheet (and, if, necessary, create a reference to the
relevant Range). The simplest way forward from there would be to open
your destination table as a writeable Recordset and loop through each
row in the spreadsheet, moving the value of each column in the
spreadsheet to the appropriate field in a new record and then
appending the record to the Recordset. The loop would terminate either
when you had dealt with the expected number of records or when you saw
the "flag value". There may well be a more efficient way of coding
this.

Obviously, with this approach, you don't have to mess about adding
header rows or special first records, since what each column contains
and where it goes is "known" to the VBA code.

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?
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Back
Top