Code to Exclude Column Headings from Transferring to Excel 2007 from Access 2007

2
votes

Here's the code which I am using.

It works fine, but I need to know what additional code is required to exclude the column headings.

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, "EmailList"

    Shell "Excel.exe " & xlfile, vbNormalFocus

Command104ContrDonatWeekly_Click_Exit:
    Exit Sub

Command104ContrDonatWeekly_Click_Err:
    MsgBox Error$
    Resume Command104ContrDonatWeekly_Click_Exit

End Sub

Here's some code from

How to write VBA code to hide all the column and row headings in Excel?

which I don't quite know where it needs to be placed in with the code I am already using.

Private Sub hideHeadings()
  Dim wrkbk As Workbook
  Dim wrksh As Worksheet
  Dim prev As Window

  Set prev = ActiveWindow

  For Each wrkbk In Workbooks
    For Each wrksh In wrkbk.Worksheets
        wrksh.Activate
        ActiveWindow.DisplayHeadings = False
    Next wrksh
  Next wrkbk

  prev.Activate

End Sub
2
excelms-accessms-access-2007vba
Unless I am missing something, why not just create a new query that only has the output you want? - Pynner
How do you hide an Access query's column heading in datasheet view? - MichaelN
Ha, Sorry... I misread Column Heading as Column. - Pynner

2 Answers

1
votes

From Access:

Sub XLTrans()
''Reference: Microsoft ActiveX Data Object x.x Library
Dim rs As New ADODB.Recordset
Dim xl As Object ''Excel.Application
Dim wb As Object ''Workbook

Set xl = CreateObject("Excel.Application")

Set wb = xl.Workbooks.Add

''Connection relevant for 2007 or 2010
rs.Open "MyTableOrQuery", CurrentProject.AccessConnection

wb.Sheets(1).Cells(1, 1).CopyFromRecordset rs

xl.Visible = True

End Sub
0
votes

If you insist on using the transferspreadsheet;

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tbl_one", xlfile, True

Dim xl As Object
Dim wb As Object

Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Open(xlfile)

xl.DisplayAlerts = False
With wb.Worksheets(1)
    .Rows(1).Delete
End With
wb.Save
xl.Visible = True