column header needs symbol

[cm]

I need to export a query (to excel format) for use in uploading data to a
system. That system requires specific column headers on the upload file.

One of the column headers needs to be: File # (no quotes)

The data in the query field is File #: Right([datafield],6). The displayed
header is fine, but when exporting, the # symbol is replaced with a period.

I tried converting it in sql using cstr(), however that resulted in a column
header of "File ." (including the quotes).

Does anyone have any suggestions on how to remedy this?




cm

 

[Doug Robbins - Word MVP]

Emailing a query that has a field with such a column heading and selecting
Excel as the format for the attachment results in the creation of an Excel
file in which the # is retained.

--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.

 

[Tom Wickerath]

Hi CM,

It looks like you can get what you need by using the OutPutTo method of the
DoCmd object. If my memory serves correctly (?) the OutPutTo method has a
limit of 2^14 rows = 16,384 rows (including the column header). So, as long
as the number of records limit will not be an issue, then this method should
work. Here is code I set up to test this for you:

Option Compare Database
Option Explicit

Sub ExportTest()
On Error GoTo ProcError

'The OutputTo method will retain "File #"
DoCmd.OutputTo _
ObjectType:=acOutputQuery, _
ObjectName:="Query1", _
OutputFormat:=acFormatXLS, _
OutputFile:="C:\Users\Tom\Documents\Q1_OutputTo.xls"

'The TransferSpreadsheet method will result in "File ."
DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadsheetType:=acSpreadsheetTypeExcel9, _
TableName:="Query1", _
fileName:="C:\Users\Tom\Documents\Q1_TranSpread.xls", _
HasFieldNames:=True

MsgBox "Export Completed.", vbInformation, "Export Complete..."

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in ExportTest Procudure..."
Resume ExitProc
End Sub



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________