Solved

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

Posted on 2014-11-21
4
509 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

828 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