asked on

How to format headers and sheet names in access to excel transfer

I have a little program that creates a excel spreadsheet to import data to another program, My problem is the one of the sheet names is "Service-Material Links" and after export it becomes "Service_Material Links" .
A column header is named "Display Name" but after export is labeled "DisplayName"
Another column is Named "% Bonus" I cannot name a field "% Bonus" (I THINK)??

I think what I need is to boiler plate the sheet and column names upon export as they do not change from import to exports??

Any suggestions??
I am attaching the module 1 file which has the mapping (import Function) and the export Data function

Thanks
George
Module-1.txt

Dale Fye

How are you doing your export to Excel, is it via the TransferSheet method, or are you using Excel automation to open Excel and paste your data?

GeorgeSalet

ASKER

Using the transfer sheet: DoCmd.TransferSpreadsheet acExport, 10, "Services", excelfilename, True

aikimark

You will need to use Excel automation to get the results you want. The TransferSpreadsheet is rather generic. You might tweak the source query to get a bit closer, but that method only goes so far.

SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeffrey Coachman

So you are saying that this all works, ...except that the field names are a little off?
For broad compatibility, avoid non-alphanumeric characters and spaces in our object names.
Try using "Camel Case" instead.
So try changing your field names to something like this:
ServiceMaterialLinks
DisplayName
PctBonus

JeffCoachman

ASKER CERTIFIED SOLUTION