Is there anyway to get 2 columns with the same name in a query?

[Robert]

I am doing a transfershpreadsheet from Access to Excel. The field names of
the query become the top line of the spreadsheet when the transfer is
executed. What I need is 2 columns with the exact same name. I have tried
using dummy tables and queries but it always rejects 2 columns with the same
name. Anyone know of a way?

Robert

 

[Ken Snell MVP]

Only way is to use Automation after the export to open the EXCEL file and
change the column names directly.

 

[Robert]

Thank you. How does that get kicked off? Can you change formatting also?

 

[KARL DEWEY]

Maybe have two rows with column names, second row with duplicate column name.
Then use Automation after the export to open the EXCEL file and first row
directly.

 

[Ken Snell MVP]

This is sample code:

Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim blnEXCEL As Boolean
blnEXCEL = False

' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

' Change True to False if you do not want the workbook to be
' visible when the code is running
xlx.Visible = True

' Replace C:\Filename.xls with the actual path and filename
' of the EXCEL file from which you will read the data
Set xlw = xlx.Workbooks.Open("C:\Filename.xls", , True) ' opens in read-only
mode

' Replace WorksheetName with the actual name of the worksheet
' in the EXCEL file
Set xls = xlw.Worksheets("WorksheetName")

' Replace A1 with the cell reference

Set xlc = xls.Range("A1") ' this is the cell that contains the field name to
be changed

xlc.Value = "NewFieldName"' Close the EXCEL file without saving the file,
and clean up the EXCEL objects
Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Robert]

This appears to be what I need. Thank you.

This is sample code:

Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim blnEXCEL As Boolean
blnEXCEL = False

' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

' Change True to False if you do not want the workbook to be
' visible when the code is running
xlx.Visible = True

' Replace C:\Filename.xls with the actual path and filename
' of the EXCEL file from which you will read the data
Set xlw = xlx.Workbooks.Open("C:\Filename.xls", , True) ' opens in
read-only mode

' Replace WorksheetName with the actual name of the worksheet
' in the EXCEL file
Set xls = xlw.Worksheets("WorksheetName")

' Replace A1 with the cell reference

Set xlc = xls.Range("A1") ' this is the cell that contains the field name
to be changed

xlc.Value = "NewFieldName"' Close the EXCEL file without saving the file,
and clean up the EXCEL objects
Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/