StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If you're willing to make use of a little vba rather than stick exclusively with macros, the following might help you. This module takes any sql you throw at it and exports it to a defined location in an excel worksheet. After the module are two examples of it's use, one to create a completely new workbook, one which opens an existing one. If you not confident with using SQL just create the query you want, save it and then supply "SELECT * FROM [YourQueryName]" to the Sub as the QueryString parameter.</p> <pre><code>Sub OutputQuery(ws As excel.Worksheet, CellRef As String, QueryString As String, Optional Transpose As Boolean = False) Dim q As New ADODB.Recordset Dim i, j As Integer i = 1 q.Open QueryString, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly If Transpose Then For j = 0 To q.Fields.Count - 1 ws.Range(CellRef).Offset(j, 0).Value = q(j).Name If InStr(1, q(j).Name, "Date") > 0 Or InStr(1, q(j).Name, "DOB") > 0 Then ws.Range(CellRef).Offset(j, 0).EntireRow.NumberFormat = "dd/mm/yyyy" End If Next Do Until q.EOF For j = 0 To q.Fields.Count - 1 ws.Range(CellRef).Offset(j, i).Value = q(j) Next i = i + 1 q.MoveNext Loop Else For j = 0 To q.Fields.Count - 1 ws.Range(CellRef).Offset(0, j).Value = q(j).Name If InStr(1, q(j).Name, "Date") > 0 Or InStr(1, q(j).Name, "DOB") > 0 Then ws.Range(CellRef).Offset(0, j).EntireColumn.NumberFormat = "dd/mm/yyyy" End If Next Do Until q.EOF For j = 0 To q.Fields.Count - 1 ws.Range(CellRef).Offset(i, j).Value = q(j) Next i = i + 1 q.MoveNext Loop End If q.Close End Sub </code></pre> <p>Example 1:</p> <pre><code>Sub Example1() Dim ex As excel.Application Dim wb As excel.Workbook Dim ws As excel.Worksheet 'Create workbook Set ex = CreateObject("Excel.Application") ex.Visible = True Set wb = ex.Workbooks.Add Set ws = wb.Sheets(1) OutputQuery ws, "A1", "Select * From [TestQuery]" End Sub </code></pre> <p>Example 2:</p> <pre><code>Sub Example2() Dim ex As excel.Application Dim wb As excel.Workbook Dim ws As excel.Worksheet 'Create workbook Set ex = CreateObject("Excel.Application") ex.Visible = True Set wb = ex.Workbooks.Open("H:\Book1.xls") Set ws = wb.Sheets("DataSheet") OutputQuery ws, "E11", "Select * From [TestQuery]" End Sub </code></pre> <p>Hope that's of some use to you.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload