Solved

Sending Access Selected values from a Report or Query to Excel.

Posted on 2014-11-21
4
497 Views
Last Modified: 2014-11-21
I had this question after watching Using Automation to Control Excel from Access.

I've been looking for a solution to do this and this video looks like a fantastic start on what I need.   What I really need to do is take selected values from a report or query and add them to a specific location in an Excel workbook.  Can you tell me how to do this?

Marshall
0
Comment
Question by:Mwvarner
  • 2
4 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40458255
you will need VBA codes to do that...
here is a guide you can use

Using Automation to Transfer Data to Microsoft Excel
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40458320
How familiar are you with Access and Excel VBA?

In a nutshell, you fire up Excel from Access VBA
'Start a new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

oApp.Visible = True
oApp.UserControl = True

oSheet.Cells(1, 1).Value = "Some Column Header"
oSheet.Cells(1, 2).Value = "Some Other Column Header"

Open in new window

After that, you can add data to cells via

oSheet.Cells(x, y).Value = SomeData

If you get a recordset on the go, then something like this

dim fld as field
x = 1
y = 1
do until rs.EOF
    for each fld in rs.fields
        oSheet.Cells(x, y).Value = rs(fld.name)  
        y = y +1
   next fld
    y = 1
    x = x + 1
    rs.movenext
loop

will programmatically throw each field value into a new column and work down the sheet.
You can adapt it as you need to, skipping fields, formatting what comes out of a field, conditionals, whatever.

But that's the guts of it.
0
 

Author Comment

by:Mwvarner
ID: 40458432
I think this will be perfect.  I've been looking everywhere for this.

Thanks.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40458456
It takes a while to wrap your head around the Excel syntax if that isn't your specialty.
Once you do though, it is all just loops within loops to move around the grid

Other handy things if you have a recordset on the go
Taking your fieldnames and putting them in row 1
'Add the field names in row 1
Dim i As Integer
Dim iNumCols As Integer
'iNumCols = 9
iNumCols = rs.Fields.count
For i = 1 To iNumCols
oSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next

Open in new window


Making those headers bold and autofitting the columns
'Format the header row as bold and autofit the columns
With oSheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
.EntireColumn.AutoFit
End With

Open in new window


Applying number formats to columns -- in this example Date and money formats
oSheet.Range("D:D").NumberFormat = "dd-mmm-yy"
oSheet.Range("G:G").NumberFormat = "dd-mmm-yy"
oSheet.Range("H:T").NumberFormat = "$#,##0.00"

Open in new window


Setting Print areas -- in this example for a1 to G and the number of rows I've blown in
oSheet.PageSetup.PrintArea = "$A$1:$G$" & i + 7

Open in new window


More Page Setup and Sizing code
oSheet.PageSetup.FitToPagesWide = 1
oSheet.PageSetup.FitToPagesTall = 1
oApp.ActiveWindow.View = xlPageBreakPreview
oApp.ActiveWindow.Zoom = 100

Open in new window


Coloring in the borders to the cells that have been written to
With oSheet.Range(Cells(1, 1), Cells(i + 2, 7))
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeTop).Weight = xlThin
    .Borders(xlEdgeTop).ColorIndex = xlAutomatic
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).Weight = xlThin
    .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlEdgeRight).Weight = xlThin
    .Borders(xlEdgeRight).ColorIndex = xlAutomatic
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeLeft).Weight = xlThin
    .Borders(xlEdgeLeft).ColorIndex = xlAutomatic
    .Borders(xlInsideVertical).LineStyle = xlContinuous
    .Borders(xlInsideVertical).Weight = xlThin
    .Borders(xlInsideVertical).ColorIndex = xlAutomatic
    .Borders(xlInsideHorizontal).LineStyle = xlContinuous
    .Borders(xlInsideHorizontal).Weight = xlThin
    .Borders(xlInsideHorizontal).ColorIndex = xlAutomatic
End With

Open in new window


Saving the resulting xls file:
Dim myfilename As String
myfilename = "c:\temp\" & SomeVariable & " .xls"
oBook.SaveAs myfilename

Open in new window


Closing out and cleaning up, which can come back to be a PITA if the code crashes, leaving hidden Excel Instances running
oBook.Close
oApp.Quit

Set oSheet = Nothing
Set oBook = Nothing
Set oApp = Nothing

Open in new window


Anything else you might find handy, create a new Q and drop me a message.

Nick67
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now