Solved

Moving reports from Access to Excel with formatting

Posted on 2014-09-08
21
165 Views
Last Modified: 2014-09-15
This is just a general question.

I have a report in Access that I need to move into an Excel template.

I am not sure where to begin with the code, so I am hoping that some of you might be able to direct me to good places to learn about this. I am interested in books, articles, or anything that can help me figure out how to get started.

Thanks, in advance!
0
Comment
Question by:Megin
  • 10
  • 9
  • 2
21 Comments
 

Author Comment

by:Megin
Comment Utility
More specifically, is it possible to have excel import data from an access database into a particular cell in the spreadsheet based on conditions?
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I don't have any sample code that puts data into specific cells but here's a code snippet that gets data from a specific cell.  I define the variables in the form header because they are used in several procedures.
Option Compare Database
Option Explicit
Public appExcel As Excel.Application
Public wbk As Excel.Workbook
Public wks As Excel.Worksheet

........

    'open spreadsheet (Input Sheet) page, get data from specific cells
    Set appExcel = Excel.Application
    Set wbk = appExcel.Workbooks.Open(Me.txtFileName)
    strJobDesc = Range("'PM Misc Sheet'!E3").Value
    strDetWhere = Range("'M040133'!F1").Value
    strFabWhere = Range("'M040133'!F2").Value
    strEreWhere = Range("'M040133'!F3").Value
    strDetPerHr = Range("'M040133'!D1").Value
    strFabPerHr = Range("'M040133'!D2").Value
    strErePerHr = Range("'M040133'!D3").Value
    'close excel
    wbk.Close (False)   'close without saving changes
    appExcel.Quit
    Set wbk = Nothing
    Set appExcel = Nothing

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
"I have a report in Access that I need to move into an Excel template."
Is it truly an Access report?  Getting fidelity to the format of a report in Excel is very, very, very difficult, if not flat out impossible.
Getting data from Access to Excel can be as simple or as complex as your needs may require.  I do a ton of automating Excel from Access VBA -- the hard part is really getting a handle on Excel VBA.  Manipulating cells and ranges by getting the syntax right was, in my case, something I found very hard to learn.

Start simple.
Open a query in Access.
Select all records.
Copy.
Open Excel with the template you want.
Paste.

All of that can be automated from VBA.
But does that meet your needs?
0
 

Author Comment

by:Megin
Comment Utility
I wish it was that simple.

It doesn't have to be the report itself that is moved to Excel. I can use a query.

But the important part is the formatting in Excel. I have attached a sample of what I need it to look like.

I am thinking that I want Excel to go into Access, find any activities between two particular dates with a particular stoid number (all things that are related to the activities themselves), pull them into a particular cell in Excel, and then concatenate each item with a carriage return (so they will be in a line by line) with the proper formatting.

Is this possible? Is there a better way?

If it is in Excel and it looks like what is attached, that is fine, too. My manager just really wants this report to be in Excel (ugh).
Example.xlsx
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
"I want Excel to go into Access"

I'm not up for that, and in some ways your question would be posted in the wrong area because you would then be using Excel to automate Access
I am all over using Access to automate Excel.  That's the syntax I prefer to operate with.

Now,
Looking at your 'report' format, what you want can be  accomplished.
It's loops, and not much else -- loop with a loop, actually.
In VBA, you get Excel open and pointed at the sheet
You prep a recordset with the data that will be in column A & B
You start walking down that recordset with a Do Until rs.eof = true loop
You write column A & B
You fire up a second recordset that has the data for a cell in column C
You walk down that recordset with the same Do Until rs1.eof = true loop structure
For each record, you append it to the cell in column C plus a VBCrLf character.

When all the data is written you then apply formatting to the cells.
Sample mdb's are always good, you know :)
0
 

Author Comment

by:Megin
Comment Utility
I am going to look at this closer in a little bit (I have a couple of meetings I have to attend), but I can't tell you how relieved I am to hear that it isn't impossible!
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
The attached sample gets created, built and formatted entirely by Access VBA upon demand
The guts of it are fairly straightforward
...
Set db = CurrentDb
Set rs = db.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
'MsgBox rs.RecordCount
If rs.RecordCount = 0 Then
    CreateSubmittedSummary = ""
    Exit Function
    rs.Close
    Set rs = Nothing
End If
rs.MoveLast
rs.MoveFirst
'MsgBox rs.RecordCount
'Start a new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRange As Excel.Range

Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(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

'Add the data starting at cell A2
oSheet.Range("A2").CopyFromRecordset rs
...

Open in new window


A lot of fun stuff has to go on to create hyperlinks
and then there's code to format it
iNumCols = IIf(W > 0, W, iNumCols)
'Format the header row as bold and autofit the columns
With oSheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
.EntireColumn.AutoFit
End With

oSheet.Range("A:A").NumberFormat = "dd-mmm-yy "

'oSheet.Range(Cells(1, 1), Cells(iNumCols, rs.RecordCount + 1)).Copy

With oSheet.Range("A1:" & Chr(iNumCols + 64) & rs.RecordCount + 1)
    .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


But I need a sample mdb from you to really knock it together.
151810-Summary.xls
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
The reports that users typically insist be in Excel are there primarily because the user wants to do additional processing.  This doesn't strike me as that type of data so I don't understand why the user is insisting on Excel.  You can probably accomplish something acceptable with subreports and then output to a .pdf if the user wants to be able to send around a "file".

PS, you can sometimes get Excel to generate the code you need by creating a macro.  Turn on the macro recorder, perform the action you need to happen.  Stop the recorder and examine the code.  You can copy it directly into Access although you sometimes have to change the object references.  Just keep in mind that Excel takes certain shortcuts because the code is running WITHIN the Excel object whereas once you move the code to Access, the code is running outside the Excel object so references need to be explicit.
0
 

Author Comment

by:Megin
Comment Utility
I don't know why my question is marked as Neglected. I promise I am not neglecting it! I am going through the response very carefully so I can really understand it.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
EE has been tuning up the system, but maybe something is too tuned up.
After a time period of x with an Expert response and no Author response, a question gets marked neglected.

X used to be 3 days.  Clearly something different is ongoing!
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:Megin
Comment Utility
Going through this now, I am not sure it is doing what I will need it to do. My access data has to go to specific cells in the excel spreadsheet.

In the attached example, I need to get the Meetings and Activities into the cells associated with their subtask orders. I also need to put the names of the people doing these things in column B, where the Names are shown.

So, I need all meetings and activities with subtask order BA01 - SubTask1 and between x date and x date to go into cell C2.  Because the activities and meetings are individual records, I need them to loop through and concatenate into the single cell.

I am not sure that is the best way to do that, but that is what I have in my head, theoretically, as working.

Right now my database has information I can't put online. Give me a little bit and I will have one that has dummy information in it.

Or would a single query with the information I need help?  Or the tables?
Example.xlsx
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
A dummy mdb with the tables with fake data in them is good.
It's all loops
So if you need one query for the data in column A, and another for B, and a third for C, so be it.
You write to column A, then to B, then to C and move down a row
You keep that up until you run out of data.

"I need them to loop through and concatenate into the single cell."
That's an ugly stupid requirement for Excel, but if that's what the PHB wants, will give it to him.
Much nicer is if each database field value can occupy a single cell.

And then there's Excel's upper end limit on how much of a cell's data it is willing to show.
Try putting 1025 characters in a cell, and then see what ACTUALLY shows in the cell as opposed to the address bar :)
0
 

Author Comment

by:Megin
Comment Utility
I am attaching a copy of my database with dummy information.

The data that I will be using to fill in the spreadsheet essentially comes from the table Act_SubTO_Date. However, it needs to be queried with other tables so that the names of things, and not just the ID numbers, are shown. Rpt_Combined_Report shows everything I need in the Excel spreadsheet and it almost looks right. It is based on Qry_Report.
Example-Database.accdb
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
Comment Utility
Ok, this is a start.
Your report form has a new button
It's not complete, but it's time to snore
Export-v1.accdb
0
 

Author Comment

by:Megin
Comment Utility
I am blown away by the work you are putting into helping me! Thank you!!!

I am looking at the code for that button now. That will be my study project for this morning.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
The guts of the approach are there.
Its the nuances that are tough.
Basically
oSheet.Cells(SomeRow, SomeColumn).Value = SomeValue
will put SomeValue into the cell located at SomeRow and SomeColumn.

The rest is just the logic of walking down the recordset and looking at each field value and deciding if and to what cell you'd like to write it.  And keeping track of what cells you've already used.
When it comes to the cell where you need to concatenate it, it'll be
oSheet.Cells(SomeRow, SomeColumn).Value = oSheet.Cells(SomeRow, SomeColumn).Value  & vbcrlf & SomeValue

When the data is all in, then it's just formatting.
You can figure that out by recording macros in Excel as you format the results, and then translating the macro code into Access.

I've allowed Excel to be visible and in your control, so that you can breakpoint the logic as it runs and see what cells are being written to as the logic progresses.

Let me know how it goes.
0
 

Author Comment

by:Megin
Comment Utility
Great!

I won't be able to look at this more until Monday. Things got busy at the office today.

Just want you to know I am not abandoning the question.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
I hope you haven't forgotten everything that was put into
http://www.experts-exchange.com/Database/MS_Access/Q_28421862.html

You never did get back to me or finalize that Q

Nick67
0
 

Author Comment

by:Megin
Comment Utility
I am getting an error message when I try to run this code in my copy of the database.

"Run-time error '3061': Too few parameters. Expected 2."

This is highlighted:
Set rs = db.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
"Run-time error '3061': Too few parameters. Expected 2."
So, the query you are trying to use has two parameters in it.
When you open an object, the parameters can be entered by hand, or can come from a control on a form.

But when you do it in code YOU have to supply them a little more explicitly

Dim db as database
Dim rs as recordset
Dim qdf as querydef

set db = currentdb
set qdf = db.QueryDefs(TheNameOfSomeQuery)
qdf.parameters(0) = SomeValidAndDesiredValueForYourFirstParameter  'likely Forms!SomeForm!SomeControl.Value
qdf.parameters(1) = SomeValidAndDesiredValueForYourSecondtParameter  'likely Forms!SomeForm!SomeOtherControl.Value

set rs = qdf.OpenRecordset(dbOpenDynaset,dbSeeChanges)

Open in new window


And then off to the races
0
 

Author Closing Comment

by:Megin
Comment Utility
As soon as I fixed that parameters thing, it worked like it is supposed to!

Thank you for your clear explanations of this. I really, really appreciate it.

I am closing this because you gave me the answer I am looking for. I will start working with this code to make it bend to my will.  

I am sure I will have many more questions at that point.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

763 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

8 Experts available now in Live!

Get 1:1 Help Now