Avatar of Mwvarner
Mwvarner
Flag for United States of America asked on

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

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
Microsoft AccessMicrosoft ExcelVisual Basic Classic

Avatar of undefined
Last Comment
Nick67

8/22/2022 - Mon
Rey Obrero (Capricorn1)

you will need VBA codes to do that...
here is a guide you can use

Using Automation to Transfer Data to Microsoft Excel
ASKER CERTIFIED SOLUTION
Nick67

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Mwvarner

ASKER
I think this will be perfect.  I've been looking everywhere for this.

Thanks.
Nick67

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
Your help has saved me hundreds of hours of internet surfing.
fblack61