Export Table Access -> Excel with unwanted first row of spreadshe

[Tsoying]

Does anyone knwo why 'When you export Microsoft Access table or select query
data to a spreadsheet, the field names are inserted into the first row of the
spreadsheet no matter what you enter for this argument.' I have to manually
remove the first row for all Excel sheets 'TransferSpreadsheet'ed. How can I
program in VBA so it will not be there when the transfer is completed?

Any help will be appreciated.

 

[John Nurick]

I don't know why, but that's the way it is.

The simplest way to get rid of the field names is probably to use
Automation to open the worksheet and delete the first row, using
something like this air code:

Dim strWorkbook As String
Dim strWorksheet As String
Dim oxlBook as Excel.Workbook
Dim oxlSheet as Excel.Worksheet

...
strWorkbook = "C:\Folder\File.xls"
strQuery = "MyQuery"
...

DoCmd.TransferSpreadsheet acExport,,strQuery, strWorkbook

'Open workbook
Set oxlBook = GetObject(strWorkbook)
Set oxlSheet = oxlBook.Worksheets(MyQuery)

'Delete row
oxlSheet.Rows(1).Delete

oxlBook.Save 'Save workbook
oxlBook.Parent.Quit 'Close Excel

 

[Guest]

Use a make table query instead of transferspreadsheet,
and include Hdr=No in the connect string.

(david)

 

[John Nurick]

Use a make table query instead of transferspreadsheet,
and include Hdr=No in the connect string.

Are you sure, David? When I try this

SELECT * INTO [Excel 8.0;HDR=No;Database=C:\Temp\Test.xls;].[Sheet1]
FROM MyTable;

the field names appear as column headers.

 

[david epsom dot com dot au]

Are you sure, David? When I try this

Curses! My error.

(david)

Use a make table query instead of transferspreadsheet,
and include Hdr=No in the connect string.

Are you sure, David? When I try this

SELECT * INTO [Excel 8.0;HDR=No;Database=C:\Temp\Test.xls;].[Sheet1]
FROM MyTable;

the field names appear as column headers.

 

[Tsoying]

John,

Thank you for all the info. They were helpful. I tried all the steps you
listed here and did not get the right results. What I got back when all your
steps completed was 4 blank worksheets (Sheet1, Sheet2, Sheet3 and Sheet4)
and my orignal contents were wiped out. Do you know why?

Tsoying

 

[John Nurick]

Please post the exact code you ran.

John,

Thank you for all the info. They were helpful. I tried all the steps you
listed here and did not get the right results. What I got back when all your
steps completed was 4 blank worksheets (Sheet1, Sheet2, Sheet3 and Sheet4)
and my orignal contents were wiped out. Do you know why?

Tsoying

 

[Tsoying]

I do not think you want me to post all code I ran becasue it is over 1000
lines.
Here I post my code plus your suggested code.
I have a stop right at your first DIM code and check the contents in the
'Consumer Adjustments 2005.xls' which was fine at the points. When the
process is completed, I can see my file is there. However sometimes I
cannot open it because it tells me it is locked and used by me. I click it,
it open up with Book1.xls with Sheet1, Sheet2, Sheet3 and Sheet4. Why it
turned out to be Book1.xls not my file. The last posting I made I did not
notice this fact. All I saw was 4 sheets.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Consumer",
(Path & "Consumer Adjustment 2005.xls"), False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, VpName(2),
(Path & "Consumer Adjustment 2005.xls"), False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, VpName(5),
(Path & "Consumer Adjustment 2005.xls"), False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, VpName(4),
(Path & "Consumer Adjustment 2005.xls"), False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, VpName(3),
(Path & "Consumer Adjustment 2005.xls"), False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, VpName(1),
(Path & "Consumer Adjustment 2005.xls"), False
'----------------------------
'Path = "C:\Documents and Settings\V344053\My Documents\Consumer\"

Dim strWorkbook As String
Dim strWorksheet As String
Dim oxlBook As Excel.Workbook
Dim oxlSheet As Excel.Worksheet

'strWorkbook = "C:\Folder\File.xls"
'strQuery = "MyQuery"

'DoCmd.TransferSpreadsheet acExport, , strQuery, strWorkbook

'Open workbook
Set oxlBook = GetObject(Path & "Consumer Adjustment 2005.xls")
Set oxlSheet = oxlBook.Worksheets("Consumer")

'Delete row
oxlSheet.Rows(1).Delete

oxlBook.Save 'Save workbook
oxlBook.Parent.Quit 'Close Excel

Tsoying,

 

[John Nurick]

This could happen if Excel is already running when the Access code runs.
In that case, the GetObject() call opens your workbook in the (or a)
current instance of Excel, but if there is another workbook open the
oxlBook.Parent.Quit
won't be able to close Excel and therefore won't close your workbook. A
safer but more complicated approach is along these lines:

Dim blExcelIsMine As Boolean
Dim oxlApp As Excel.Application
Dim oxlBook As Excel.Workbook
Dim oxlSheet As Excel.Worksheet

'Get an instance of Excel
On Error Resume Next
blExcelIsMine = False
'If Excel is already loaded, grab it
Set oXL = GetObject(, "Excel.Application")
Err.Clear
If IsNull(oXL) Then 'Excel not loaded
'Create a new instance
Set oXL = CreateObject("Excel.Application")
blExcelIsMine = True
If Err.Number > 0 Then
MsgBox "Sorry, couldn't launch Excel", _
vbOKOnly + vbExclamation
Exit Function 'or other actoin
End If
End If
On Error Goto 0

'Open workbook
Set oxlBook = oxlApp.Workbooks.Open(Path _
& "Consumer Adjustment 2005.xls")
Set oxlSheet = oxlBook.Worksheets("Consumer")

'Delete row
oxlSheet.Rows(1).Delete

oxlBook.Save 'Save

If blExcelIsMine Then 'we opened it so we must close it
'Close any workbooks that may have been opened by macros or add-ins
Do Until oxlApp.Workbooks.Count = 0
oxlApp.Workbooks(1).Close False 'close without saving
Loop
oxlApp.Quit 'Quit Excel
Else 'the instance of Excel was already open: don't close it
oxlBook.Close
End If