Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running sum query 6 34
Add a range in an Excel graph 5 36
Excel Hyperlink Question 4 32
IF ELSE Statement in Excel Macro VBA 16 35
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

862 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

25 Experts available now in Live!

Get 1:1 Help Now