CODEWITHSUNDEEP
vbaexcelms-accessexport-to-excel
Andy
Andy

Reputation: 49

VBA Excel - Saving column names to spreadsheet from MS Access

I am using VBA in Visual Studio Express. What I am trying to do is give the top row of my excel spreedsheet that i have created by exporting an MS Access DB through VB, column names, i.e. the names that i have in my DB.

There are 10 columns the 9th is skipped, i have also spaced the spreedsheet out to allow for the first row to have headers, how would i fill the first row of my spreedsheet with the column names of my DB? Also it is fine if to assign the names directly through the code rather than passing the column headers from the DB as well.

My Code:

Public Sub ExportEx()
    Dim strSQL_Query As String
    Dim oCN As ADODB.Connection
    Dim oCMD As ADODB.Command
    Dim oRecords As ADODB.Recordset
    Dim strDBPath As String
    Dim varValues As Object
    Dim lngRows As Long
    Dim lngCols As Long
    Dim strCN As String



    strDBPath = Application.StartupPath & "\SCO_Leaderboard.accdb"

    strCN = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDBPath & ";" & _
                    "Persist Security Info=False;"


    strSQL_Query = "Select top 10 Rank, Username, Time_Played, Lv, EXP, Floor, Col, Logins, Status FROM tblUsers ORDER BY Rank ASC"

    Dim oExcel As Object
    Dim oBook As Object
    Dim oSheet As Object
    oExcel = CreateObject("Excel.Application")
    oBook = oExcel.Workbooks.Add
    oSheet = oBook.Worksheets(1)

    oCN = New ADODB.Connection
    oCN.ConnectionString = strCN
    oCN.Open()


    oCMD = New ADODB.Command
    oCMD.ActiveConnection = oCN
    oCMD.CommandText = strSQL_Query
    oRecords = oCMD.Execute

    varValues = oRecords.GetRows


    lngCols = UBound(varValues, 2)
    lngRows = UBound(varValues, 1)
    oSheet.Range("A2", oSheet.Range("A2").Offset(lngRows, lngCols)) = varValues


    oBook.SaveAs(Application.StartupPath & "\Top_10_All_Time.xls")
    oExcel.Quit()

    MsgBox("An Excel spreadsheet has been created under:" & vbNewLine & vbNewLine & Application.StartupPath & "\Top_10_All_Time.xls")
    '' Clean up...

    oCMD = Nothing
    oCN.Close()
    oCN = Nothing

On another note how would I space the fields out in Excel so that all the data fit in the column?

Thanks for any help,

Andy

Upvotes: 1

Views: 2797

Answers (3)

Parfait
Parfait

Reputation: 107767

In VBA, there are two methods to exporting Access table/query data to an Excel spreadsheet:

1) TransferSpreadsheet method

This command will export all fields and records. So, save your VBA string as a stored query object and reference it in below command:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
               "yourtableorqueryname", "fullpathtoExcelFile", True

2) CopyFromRecordset method

This command will export only records. However, you can use the recordset's Fields property to fill in first row. Do note the code below assumes you create an ADO recordset named rst using your ADODB connection.

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

rst = "Select top 10 Rank, Username, Time_Played, Lv, EXP, Floor, Col, Logins, Status" _
  & " FROM tblUsers ORDER BY Rank ASC", oCN

oSheet.Range("A1").Select
For Each fld In rst.Fields
   oExcel.ActiveCell = fld.Name
   oExcel.ActiveCell.Offset(0, 1).Select
Next
'REMOVE BELOW IF YOU WANT ONLY COLUMN HEADERS NOT DATA
oSheet.Range("A2").CopyFromRecordset rst
'TO AUTO FIT (SPACE OUT) COLUMNS
osheet.Range("A1:I" & rst.RecordCount + 1).Columns.AutoFit

Upvotes: 2

Gustav
Gustav

Reputation: 56016

You would have to retrieve the fields Collection from the scheam of oRecords:

oRecords = oCMD.Execute

or just parse the field names from strSQL ...

or - very easy, as you define the field names and build your SQL from the field names - put these in an array and then build the first row of your range from this array.

Upvotes: 0

Edgar
Edgar

Reputation: 2787

This works in Access, I am not sure if it works in your case:

Select top 10 Rank As Header1, Username As Header2, Time_Played As Header3 ...

Upvotes: 0

Related Questions