Moving reports from Access to Excel with formatting

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!
MeginAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MeginAuthor Commented:
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
PatHartmanCommented:
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
Nick67Commented:
"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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MeginAuthor Commented:
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
Nick67Commented:
"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
MeginAuthor Commented:
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
Nick67Commented:
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
PatHartmanCommented:
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
MeginAuthor Commented:
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
Nick67Commented:
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
MeginAuthor Commented:
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
Nick67Commented:
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
MeginAuthor Commented:
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
Nick67Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MeginAuthor Commented:
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
Nick67Commented:
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
MeginAuthor Commented:
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
Nick67Commented:
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
MeginAuthor Commented:
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
Nick67Commented:
"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
MeginAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.