Don't want to transfer table column headings with transferspreadsheet export command

[accessnewbie]

Hello. Thank you in advance for the help. You guys/gals always help me in a pinch.

I'm using the transferspreadsheet VBA command to transfer an Access table to Excel. The problem is, it's exporting the column names, which I don't want. Any way to avoid this?

mw

[pbaldy]

What is your code? One of the arguments determines whether the first row is field names or data.

[maximus]

Set HasFieldNames to False if you are using Docmd.TransferSpreadsheet

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Table8", "C:\Test.xls", False

[accessnewbie]

Thanks for your help! Here is my rather simple code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TEMP_TABLE", "c:\temp\temp"

I tried using True as well as False for the "HasFieldNames" parameter, but in both instances, the column names were exported. My table is very simple. There are 13 columns named C1, C2, etc. In my spreadsheet, the first row is these column names, which I don't want. Thanks for any insights you may have! Right now I'm thinking I'll have to tell my business partner that they'll have to manually delete the first row every time they create the report. I'd like to avoid that, but there are worse things.

Have a great day!
mw

[accessnewbie]

I read this in the Access Help:

Has Field Names
When you export an Access table or select query to a spreadsheet, the field names are inserted into the first row of the spreadsheet no matter what you select in this argument.

I think I'm outta luck. Perhaps I should start a new thread, but here is my challenge...

We have an Excel report that's been in use for a while. For the last couple years, my boss would run 40 queries and update it manually. I created an Access database to generate the information so the 40 queries don't need to be run. I don't want someone to manually update it. I created a report in Access that is perfect. It fits the page and looks beautiful. However, they want to have it in Excel as well. The current Excel report has a lot of formatting (borders, merged cells, etc.). If I export my table, even if we just delete the headers, it doesn't all fit on a page or look near as nice. Can I put macros in Excel to get it right or is it hopeless?

Thank you!!

[pbaldy]

I'm rather annoyed with myself for never noticing that the field names argument only applies to imports.

It is much more involved, but you can do pretty much anything to the Excel worksheet from Access through a process called automation. Here are the basics:

http://www.mvps.org/access/modules/mdl0006.htm

I've done a couple of exports with formatting that get pretty involved. I imagine you could do it with macros from the Excel side, but I have no experience doing it that way. A handy tool I use when I'm not sure how to code something is to go into Excel and record a macro doing the desired task. Then I look at the code that got created. It often requires a little tweaking to work from Access, but it will give you the meat of the code.

[accessnewbie]

I'll give it a whirl! Thanks so much for some direction!!

[pbaldy]

No problemo. Post back if you get stuck.

[accessnewbie]

Goodness, I finally found a solution to my problem. I was hoping to export an Access table to an Excel spreadsheet to provide an electronic version of a report. I created it as a report in Access, but for some reason I'm not able to export the report to Excel. I get some error.

Anyway, I have the existing report as an Excel spreadsheet. Using this code and some other code I found, I'm able to open the Excel spreadsheet and write the information to the correct cells. The spreadsheet will act as sort of a template and once it's all filled in, I hope to be able to save it and close it.

Thank you for pointing me in the direction of this code. Without it, I wouldn't have figured out how to write to Excel or even thought of this resolution!

[pbaldy]

Happy to help. Saving and closing should be no problem, but post back if you get stuck anywhere.

[PeterT]

Hello. Thank you in advance for the help. You guys/gals always help me in a pinch.

I'm using the transferspreadsheet VBA command to transfer an Access table to Excel. The problem is, it's exporting the column names, which I don't want. Any way to avoid this?

mw

If you write your data to a temporary table in access then run the command TransferSpreadsheet to send it to excel.

Then use the Excel file name and Data sheet name from the TransferSpreadsheet command to call this little function.

The function opens an excel object for your named spreadsheet, selects row 1 and deletes it; then saves and closes the spreadsheet.

Example Sub nnnn
Dim etc.

exFileName = "T:\Data148.xls"
myTempTableName = "tmp_myData"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, myTempTableName, exFileName

Call RemoveFirstRowExcel(exFileName, myTempTableName )

End Sub

Called Function as follows

Public Sub RemoveFirstRowExcel(SSFile As String, SSSheet As String)
On Error GoTo Exit_Proc
Dim xlApp As Object
Dim xlSheet As Object
Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(SSFile).Sheets(1)
With xlApp
.Application.Sheets(SSSheet).Select
.Application.Rows("1:1").Select
.Application.Selection.EntireRow.Delete
.Application.Activeworkbook.Save
.Application.Activeworkbook.Close
.Quit
End With
Exit_Proc:
Set xlApp = Nothing
Set xlSheet = Nothing
End Sub

I use this in Access 2003 but I see no reason why it should not work in Access 2007 (or 2010)

[MichaelN]

If you write your data to a temporary table in access then run the command TransferSpreadsheet to send it to excel.

Then use the Excel file name and Data sheet name from the TransferSpreadsheet command to call this little function.

The function opens an excel object for your named spreadsheet, selects row 1 and deletes it; then saves and closes the spreadsheet.

Example Sub nnnn
Dim etc.

exFileName = "T:\Data148.xls"
myTempTableName = "tmp_myData"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, myTempTableName, exFileName

Call RemoveFirstRowExcel(exFileName, myTempTableName )

End Sub

Called Function as follows

Public Sub RemoveFirstRowExcel(SSFile As String, SSSheet As String)
On Error GoTo Exit_Proc
Dim xlApp As Object
Dim xlSheet As Object
Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(SSFile).Sheets(1)
With xlApp
.Application.Sheets(SSSheet).Select
.Application.Rows("1:1").Select
.Application.Selection.EntireRow.Delete
.Application.Activeworkbook.Save
.Application.Activeworkbook.Close
.Quit
End With
Exit_Proc:
Set xlApp = Nothing
Set xlSheet = Nothing
End Sub

I use this in Access 2003 but I see no reason why it should not work in Access 2007 (or 2010)

PeterT.

Can you assist me?

I am modifying an Access 2007 database as a volunteer for a charitable organization, and I need to automate export of Access Query data to an existing Excel 2007 work-sheet.

So far, I have got to the stage where this happens, but I need to have the column headings left-out.

Here is the code that works, but it brings-in the column headings which I don't want.

'------------------------------------------------------------
' Command104ContrDonatWeekly_Click
'
'------------------------------------------------------------

Private Sub Command104ContrDonatWeekly_Click()
On Error GoTo Command104ContrDonatWeekly_Click_Err
    
    DoCmd.OpenQuery "Contributors Who Donated in Past Week", acViewNormal, acEdit
    
    XLFile = "C:\Users\Michael1\Desktop\KSN\DistributionListWeekly.xlsb"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, _
    "Contributors Who Donated in Past Week", XLFile, True, "Sheet2"
    FollowHyperlink XLFile
        
Command104ContrDonatWeekly_Click_Exit:
    Exit Sub

Command104ContrDonatWeekly_Click_Err:
    MsgBox Error$
    Resume Command104ContrDonatWeekly_Click_Exit

End Sub

Can you show me precisely where your code would meld with the one I am using (I am lost when you say "Example Sub nnnn
Dim etc." & not sure if "Called Function as follows" is a part of the code or your passing comment.

Cheers.

MichaelN

[Mayuri]

If you write your data to a temporary table in access then run the command TransferSpreadsheet to send it to excel.

Then use the Excel file name and Data sheet name from the TransferSpreadsheet command to call this little function.

The function opens an excel object for your named spreadsheet, selects row 1 and deletes it; then saves and closes the spreadsheet.

Example Sub nnnn
Dim etc.

exFileName = "T:\Data148.xls"
myTempTableName = "tmp_myData"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, myTempTableName, exFileName

Call RemoveFirstRowExcel(exFileName, myTempTableName )

End Sub

Called Function as follows

Public Sub RemoveFirstRowExcel(SSFile As String, SSSheet As String)
On Error GoTo Exit_Proc
Dim xlApp As Object
Dim xlSheet As Object
Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(SSFile).Sheets(1)
With xlApp
.Application.Sheets(SSSheet).Select
.Application.Rows("1:1").Select
.Application.Selection.EntireRow.Delete
.Application.Activeworkbook.Save
.Application.Activeworkbook.Close
.Quit
End With
Exit_Proc:
Set xlApp = Nothing
Set xlSheet = Nothing
End Sub

I use this in Access 2003 but I see no reason why it should not work in Access 2007 (or 2010)

I was looking for this from almost 2 hours ...You just saved me..It was very helpful and worked on my Access with 2007...Thank you so much