column header needs symbol

  • Thread starter Thread starter cm
  • Start date Start date
C

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
 
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.
 
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/
__________________________________________
 
Back
Top