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

  • Thread starter Thread starter Robert
  • Start date Start date
R

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
 
Only way is to use Automation after the export to open the EXCEL file and
change the column names directly.
 
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.
 
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/
 
This appears to be what I need. Thank you.

Ken Snell MVP said:
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/
Click to expand...
 
Back
Top