Results 1 to 13 of 13
  1. #1
    accessnewbie is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    20

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

    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

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What is your code? One of the arguments determines whether the first row is field names or data.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Set HasFieldNames to False if you are using Docmd.TransferSpreadsheet

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

  4. #4
    accessnewbie is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    20

    Here's my code

    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

  5. #5
    accessnewbie is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Oct 2009
    Posts
    20

    Not looking good.

    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!!

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    accessnewbie is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Oct 2009
    Posts
    20
    I'll give it a whirl! Thanks so much for some direction!!

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problemo. Post back if you get stuck.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    accessnewbie is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Oct 2009
    Posts
    20

    Thanks!

    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!

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help. Saving and closing should be no problem, but post back if you get stuck anywhere.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    PeterT is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2010
    Posts
    1

    Thumbs up TransferSpreadsheet without Column Headings

    Quote Originally Posted by accessnewbie View Post
    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)

  12. #12
    MichaelN is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2012
    Posts
    1
    Quote Originally Posted by PeterT View Post
    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.

    Code:
    '------------------------------------------------------------
    ' 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

  13. #13
    Mayuri is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    1
    Quote Originally Posted by PeterT View Post
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. command button to export to excel
    By jains in forum Forms
    Replies: 5
    Last Post: 06-30-2015, 06:27 PM
  2. Cross Tab Column Headings...
    By mhoctober in forum Queries
    Replies: 3
    Last Post: 09-10-2010, 01:21 PM
  3. Button Command to Transfer Spreadsheet
    By diane802 in forum Programming
    Replies: 3
    Last Post: 02-10-2010, 09:26 AM
  4. Transfer SpreadSheet Command and Rename
    By fintan06 in forum Import/Export Data
    Replies: 2
    Last Post: 10-15-2009, 08:00 AM
  5. Export Command Button in Form
    By jjmartinson in forum Forms
    Replies: 3
    Last Post: 08-25-2009, 01:28 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums