Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

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
0
Mwvarner
Asked:
Mwvarner
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
you will need VBA codes to do that...
here is a guide you can use

Using Automation to Transfer Data to Microsoft Excel
0
 
Nick67Commented:
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
 
MwvarnerAuthor Commented:
I think this will be perfect.  I've been looking everywhere for this.

Thanks.
0
 
Nick67Commented:
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now