How to control the output from MS Access to Excel

Question

Does any body know how to control the layout of the output from a table in MS Access to Excel.

For example:

My MS Access table looks like so

--------------------------------------------------
|ID      | Field 2 | Field 3| Field 4 | ETC
--------------------------------------------------
|A       | 1       | 2      | 3       |
|A       | 4       | 5      | 6       |
|B       | 7       | 8      | 9       |
|C       | 10      | 11     | 12      |
|A       | 13      | 14     | 15      |

Although I would like to output the data into excel like:

--------------------------------------------------
|ID      | Field 2 | Field 3| Field 4 | ETC
--------------------------------------------------
|A       | 1       | 2      | 3       |
|        | 4       | 5      | 6       |
|        | 13      | 14     | 15      |
|B       | 7       | 8      | 9       |
|C       | 10      | 11     | 12      |

So I can group all the records by the 'ID' field.

Any ideas?

Answer 1

Re: row order

Of course, you could sort the rows in Excel, but if you wanted to do it in Access you would create a Select query that does the sorting, save it, and then export the query instead of the table.

Re: suppressing duplicate values

You probably don't want to omit them entirely, but you can hide them in Excel using the Conditional Formatting feature. In your case you would

• select the ID values in column A (starting with row 2)
• invoke Conditional Formatting
• choose "Use a formula to determine which cells to format"
• set the formula to =A2=A1
• set the font colour to be the same as the background colour

(ref: here)

Edit: In response to comment below, sample Excel VBA code to apply the formatting (as captured by "Record Macro" in Excel):

Range("A2:A6").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=A2=A1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Answer 2

Try something like this.

First a few useful functions:

1) Get Recordset from Access based on SELECT statement (passed via argument).

Option Explicit
Public Function Rst_From_Access(sSQL_Select As String) As ADODB.Recordset

Dim oConn                           As ADODB.Connection
Dim oRst                            As ADODB.Recordset
Dim sPath_DB                        As String
Dim sFile_DB                        As String

Dim sConn                           As String


'Instantiate the ADO-objects.

Set oConn = New ADODB.Connection
Set oRst = New ADODB.Recordset

'Set Path and File
sPath_DB = ThisWorkbook.Names("PARAM_PATH_DB").RefersToRange.value
sFile_DB = ThisWorkbook.Names("PARAM_FILE_DB").RefersToRange.value

 'Create the connectionstring.
sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sPath_DB & sFile_DB & ";"

With oConn
    .Open (sConn) 'Open the connection.
    .CursorLocation = adUseClient 'Necessary to disconnect the recordset.
End With

With oRst
    .Open sSQL_Select, oConn 'Create the recordset.
    Set .ActiveConnection = Nothing 'Disconnect the recordset.
End With

Set Rst_From_Access = oRst

End Function

2) ADO Connection:

Public Sub open_ADODB_Connection()

Dim oConn                           As ADODB.Connection
Dim sPath_DB                        As String
Dim sFile_DB                        As String

Dim sConn                           As String


'Instantiate the ADO-objects.

Set oConn = New ADODB.Connection

'Set Path and File
sPath_DB = ThisWorkbook.Names("PARAM_PATH_DB").RefersToRange.value
sFile_DB = ThisWorkbook.Names("PARAM_FILE_DB").RefersToRange.value

 'Create the connectionstring.
sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sPath_DB & sFile_DB & ";"

With oConn
    .Open (sConn) 'Open the connection.
    .CursorLocation = adUseClient 'Necessary to disconnect the recordset.
End With

End Sub

3) Call the Recordset:

Option Explicit

Sub Connect_To_DB()

Dim oSheet                      As Excel.Worksheet

Dim sSQL_Select                 As String
Dim oRst                        As ADODB.Recordset
Dim iMax_Col                    As Integer
Dim lMax_Row                    as long


Set oSheet = ThisWorkbook.Sheets("Main")

'Get recordset
sSQL_Select = "SELECT * FROM T_TABLE ORDER BY ID;"

Set oRst = Rst_From_Access(sSQL_Select)

iMax_Col = oRst.Fields.Count
oRst.MoveLast
iMax_Row = oRst.RecordCount

With oSheet
   .Range(.Cells(1, 1), .Cells(iMax_Row, _
        lMax_Col)).CopyFromRecordset oRst
End With

End Sub

Of course, you will have a repetition of the ID, will that cause a problem? In general, I think that it is better to keep all data for every row. If it is for printing purposes, then I would immediately print it from Access.