Export column [ExclComp] as Excludable Compensation

  • Thread starter Thread starter Bonnie A
  • Start date Start date
B

Bonnie A

Hi everyone! I'm using A02 on XP. I have this nice little macro that
exports a query via 'TransferSpreadsheet' and names it using the contract
number ="S:\RPS\PTS\CensusConversion\ToClient\" &
[Forms]![fCensus1Conversion]![RunThisOne] & ".xls"

It has run well for a few years now. The client is sent the exported file,
they update data, add and delete employees and return it. We re-import and
run a few routines on it and re-export it for loading back to the original
system it was extracted from. Now they want to rename a few columns for the
client. It shouldn't be a big deal and they at least agreed to no spaces in
the new names BUT...how do I re-rename the columns when the client returns
the file and I re-import the data for processing? The renamed fields are:
EXLCOMP to ExcludableCompensation; DEFAMT to DeferralAmount and COMP to
W2Box1. Like I said, I can export the new field name but don't want to
reprogram all my routines when I re-import. My re-import needs to change
them back.

Can this be done with my limited programming abilities? I do work with
expressions and some VB so would appreciate any and all assistance or
suggestions.

Thank you very much!
 
Probably the easiest thing to do is to import the spreadsheet's data into an
interim table that has the spreadsheet's column names as the field names,
then use an append query to copy the data from that interim table to your
real table -- you can map the new field names to your desired ones in the
append query. No VBA needed; just a change to the Table argument for the
TransferSpreadsheet action, a new append query, a new interim table, two new
queries (one to delete data from the interim table, one to append data from
it to your real table), and two more macro actions to run the delete and
append queries.
 
Back
Top