Export query results to existing Excel worksheet

utzja1

Registered User.
Local time
Today, 02:22
Joined
Oct 18, 2012
Messages
97
I have a report in my database that has a Select query as its record source. I would like to create a button on the report that launches an existing Excel worksheet On_Click. The worksheet will be a Cost/Benefit analysis, and I'm choosing to do this in Excel in order to to give the user the option to evaluate their options and get instant results. When the user closes the worksheet, I will have a macro that gives them the opportunity to Save As...

Of the 22 fields in the query, only about 9 have any relevance to the Cost/Benefit worksheet. I would like to pass specific fields from Access to specific cells in Excel. I'm new to this type of operation, so I don't have any code put together, although I have seen some elaborate snippets of code on this forum. Most are too involved for what I am trying to do, so it's tough for me to edit down to only what I need when I'm not sure what that is.

Can anybody point me in the right direction? DoCmd.TransferSpreadsheet seems a little heavy-handed for what I'm trying to do. All comments welcomed.
 
No, at the user's request I am pushing data from the database into an existing Excel template. These values will constitute the baseline or as-is condition of an asset. The user will have the option to tweak values in the spreadsheet to do a cost-benefit analysis (i.e., amount of money needed to improve the condition rating of an asset versus the increased value of an asset). I need to put specific data from the database into specific cells so that the formulas in the spreadsheet will work properly. That's what makes TransferSpreadsheet a little heavy-handed; I don't need it all.

I hope that clarifies my objectives. Thanks for your consideration.
 
Tell me more about the template, is the data going in seqential rows just different columns for each data point, or is the template more custom then that?
 
The template is more custom than that. I suppose one option would be to import the data in table form into another spreadsheet in that workbook and then just echo it back into the end-product worksheet.
 
Are you able to attach the template so I can mock something up for ya? I know that company privacy is concerned, just the template, you can strip any formuals and such that may be sensitive.
 
I've attached the template; the blue-fill cells are the ones where I'd like to place the data from the query-based report. Thanks for your help.
 

Attachments

So the query Ouputs a single line of data? the sheet from my looking at it is a single data analysis, right?
 
Here is something that I wrote on the fly, but it iterates through mutiple records. It may give you an idea though:

Code:
Sub WriteToExcel()
Dim oExcel As Excel.Application
Dim oWkBk As Excel.Workbook
Dim oWksheet As Excel.Worksheet
Dim strFile As String
Dim lngCounter As Integer
Dim rs As Recordset
'these are my output fields in the query
Dim MyTicket As Long
Dim MyStatus As Long
Dim MyPriority As Long
Dim MyBU As String
strFile = "C:\MyDB\Test.xlsx"
Set oExcel = Excel.Application
Set oWkBk = oExcel.Workbooks.Open(strFile)
Set oWksheet = oWkBk.Sheets(1)
'this is the first tab of the sheet to go to a different tab,navigate to it
'if the sheet has headers set the lngcounter to 2
lngCounter = 2
 Set rs = CurrentDb.OpenRecordset("qryExcelWriting")
rs.MoveFirst
Do Until rs.EOF = True
    MyTicket = rs!TicketID
    MyStatus = Nz(rs!StatusID, 0)
    MyPriority = Nz(rs!PriortyID, 0)
    MyBU = Nz(rs!BU, "")
    oExcel.Visible = True
    'define the column for the variable
    oExcel.Range("A" & lngCounter).Select
    oExcel.ActiveCell.Value = MyTicket
    oExcel.Range("C" & lngCounter).Select
    oExcel.ActiveCell.Value = MyStatus
    oExcel.Range("D" & lngCounter).Select
    oExcel.ActiveCell.Value = MyPriority
    oExcel.Range("G" & lngCounter).Select
    oExcel.ActiveCell.Value = MyBU
    lngCounter = lngCounter + 1
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing
'place a save for the worksheet here
Set oWksheet = Nothing
oWkBk.Close
Set oWkBk = Nothing
oExcel.Quit
Set oExcel = Nothing
End Sub

Let me know if this start your brain working and if you can modify to taste.
 
Thanks! I'll start looking at this now and let you know if I punch it across the goal line.
 
If the query return 1 result every time, then use this. I modified for your cell reference in the template just change the query name and field references. I didn't look at the second tab, but I added navigation code to it at the end.

Code:
Sub WriteToExcel()
Dim oExcel As Excel.Application
Dim oWkBk As Excel.Workbook
Dim oWksheet As Excel.Worksheet
Dim strFile As String
Dim rs As Recordset
 
strFile = "C:\MyDB\Test.xlsx"
Set oExcel = Excel.Application
Set oWkBk = oExcel.Workbooks.Open(strFile)
Set oWksheet = oWkBk.Sheets(1)
 
oExcel.visible = True
'this is the first tab of the sheet to go to a different tab,navigate to it
        'Chnge the query Name to yours
Set rs = CurrentDb.OpenRecordset("qryExcelWriting")
    oWksheet.Select
    With oWksheet
        'set the filedNames for the query to yours after the exclamation point
        .Range("C3").Value = rs!structureNum
        .Range("C4").Value = rs!DeckLen
        .Range("C5").Value = rs!DeckWidth
        .Range("H3").Value = rs!FacilityCarried
        .Range("H4").Value = rs!FacilityCrossed
        .Range("H5").Value = rs!userSqFt
        .Range("F8").Value = rs!NBIDeckRating
        .Range("F18").Value = rs!SuperStructureRating
        .Range("F28").Value = rs!subStructureRating
    End With
'if you need to get to the other sheet and insert values
'    Set owrksheet = oWkBk.Sheets(2)
'    oWksheet.Select
 
'repeat inserts
rs.Close
Set rs = Nothing
'place a save for the worksheet here
Set oWksheet = Nothing
oWkBk.Close
Set oWkBk = Nothing
oExcel.Quit
Set oExcel = Nothing
End Sub

Let me know if this is helpful. :)
 
Yes, this particular query returns data for a single record. I think this will do just fine . Thank you so much for your time!
 
yw. refresh this post. I made an edit a minute ago becuase there was an error in the there. :( don't want bad code floating out there!
 
Sorry to bother, I got the code in and got all the object names updated. When I tried to run it, it gave me "User-defined type not defined" and highlighted the first line (Dim oExcel as Excel.Application). Am I missing a library reference? I've seen this declaration in other posts,so was just curious what your thoughts were.
 
OK, adding the Microsoft Excel Object Library took care of that error. Now I'm seeing that the OpenRecordset command needs two parameters. Hmmm....
CurrentDb.OpenRecordset("qryExcelWriting")
 
Ahhh yes. Forgot about that part. In the VBA editor, go to tools>refernces and look for Micrsoft Excel xx.0 Library. If the references oprion is greyed out. Sotp the code that is running and try to go to references again.
 
you have to place the name of the query that you are using there. In each of the rs!fieldname statements in the code, make sure that the fieldnames match the ones in your query.
 
What is the query name that you are using and what are the field names that you are outputting to excel in the query?
 
Finally got it up and running, thanks to everyone for your help.


Private Sub CBR_Click()

Dim oExcel As Excel.Application
Dim oWkBk As Excel.Workbook
Dim oWksheet As Excel.Worksheet
Dim strFile As String
Dim qSource As String
Dim rs As Recordset

strFile = "P:\Transportation\60277443\400 Technical Information\408 Structural\Cost Benefit Worksheets\CBR Worksheet.xlsm"
Set oExcel = Excel.Application
Set oWkBk = oExcel.Workbooks.Open(strFile)
Set oWksheet = oWkBk.Sheets(1)
oExcel.Visible = True

Set Db = CurrentDb
Set qrydef = Db.QueryDefs("SingleBridgeQ")
qrydef.Parameters("Square Ft Bridge Cost $125 -$250 ") = Me.Est
qrydef.Parameters("For State Structure Number:") = Me.StateStrNum

Set rs = qrydef.OpenRecordset(dbOpenDynaset)

oWksheet.Select
With oWksheet
.Range("C3").Value = rs!StateStrNum
.Range("C4").Value = rs!TotBridgeLength
.Range("C5").Value = rs!BridgeWidth
.Range("H3").Value = rs!FacilityOver
.Range("H4").Value = rs!FacilityUnder
.Range("H5").Value = rs!Est
.Range("F8").Value = rs!CurrNbiDeck
.Range("F18").Value = rs!CurrNbiSuper
.Range("F28").Value = rs!CurrNbiSub
End With

rs.Close
Set rs = Nothing

Set oWksheet = Nothing
'oWkBk.Close
'Set oWkBk = Nothing
'oExcel.Quit
'Set oExcel = Nothing

End Sub
 

Users who are viewing this thread

Back
Top Bottom