How to convert an Access Report to an Excel file using VBA code?

>I want to convert an MS Access report into an Excel file, while retaining the formatting.
Can't be done.  Exporting Access reports to anything other than a Snapshot (*.snp) file or an Adobe .pdf will not guarantee that the formatting stays in tact, and often it will look like {insert your metaphor for 'very bad' here}.

If you need data to move from Access to Excel, I recommend using VBA code to manually move the data (and only the data) into an Excel spreadsheet that you have pre-formatted.

Hope this helps.
-Jim

(clarification of above comment)  Exporting datasets based on a table or query from Access to Excel is no problem, but the similar functionality to handle formatting just isn't a part of Access Reports.

Thanks for your comments Jim,

I was afraid I would hear this. I need the report to be in Excel, so I am unable to use a Snapshot or anything similar.

Thanks anyway,
Susan

(I hope this makes sense) What do the Excel cells look like where you have to export data?  Are they continuous ranges, where you can create a Named Range in Excel, where an Access table/query can be pasted directly into that named range, or are they many single cells?

There are many single cells, so therefore the data does not suit being imported through a named range. The amount and contents of the data can change as well.

If it's many single cells, then the way I see you pulling this off is to...

(1)  In Excel, give them all named ranges (click on cell, then Insert menu, Name..., Define, give it a name)
(2)  In Access, create your query(ies) that have all the data you need.
(3)  Then, write VBA code (I can post some if you wish to go down this road) that in Access dumps the table/query into a recordset, copies your Excel spreadsheet to another one, gives it a new name, sets a reference to the new Excel spreadsheet, then copies single values of data from the recordset into your Excel named ranges.
(4)  Save the Excel spreadsheet via code, then close.

I am quite interested in this, the only thing that I would see as a problem is that my report has information about events, and specific actions that happen at these events. There can be more than one event in each report, and the data can vary. All of the information should be written on one excel sheet, but I will not be able to name all of the cells due to the unpredicatable nature of the data. I hope this makes sense! Do you see this to be a problem?

Thanks!

>I will not be able to name all of the cells due to the unpredicatable nature of the data.
Can you explain this in more detail, specifically 'and specific actions that happen at these events. '?  If it's just an issue of your data can have anywhere from 1 to {a really big number} of events, where the structure (i.e. cells) for each event are the same, then this is still do-able.  

I have a template that has at the top of it, information about the event, and at the bottom, all of the actions that take place at the event. You are correct in saying that the data can have any number of events, this is where I would see the main problem! What can you suggest?!

Some (selective) info on Access data -> Excel. It assumes some understanding of automation & ADO\ DAO as it stands:

http://www.dbforums.com/showthread.php?t=1208155

> You are correct in saying that the data can have any number of events
But do they have the same cells?  For example, is any one event different from another such that it would affect how it was displayed in your Excel spreadsheet?

Apologies for the delay for the response. The cells would appear the same for each event, so each event would have the same cells.

Hi pootle flump,

Can you tell me a bit more about the code that you use for this? I see in this link you have said that code is available? I would be interested to find out how much coding is required to produce a report from access in excel.

Thanks for your information.
Susan

>>I would be interested to find out how much coding is required to produce a report from access in excel.
Well - that is kind of like the length of a peice of string :-)

The class I use runs into several hundred lines of code but it has grown over time as more functionality is required. Getting data from Access into Excel is fairly easy - I find formatting the trickier part as you need to iterate your data.

I will post some sample code....

Hi Susan

been meaning to type up this sort of stuff for ages. All the techniques are there - you just need to adapt using code and suggestions from the other post for your specific needs. You will need to set a reference to DAO and Excel. You could, otherwise, use late binding of course. If you are more comfortable with ADO you can adapt the code - Excel accepts ADO recordsets as an arg too.

HTH

Option Compare Database
Option Explicit

Sub ExportData()
On Error GoTo ExportData_Error

'DAO objects to get the data
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

'Excel objects to manipulate Excel
    Dim exApp As Excel.Application
    Dim exBook As Excel.Workbook
    Dim exSheet As Excel.Worksheet

'variants to use for formatting loops
    Dim NoOfCols As Integer
    Dim NoOfRows As Integer

'Iterant for misc loops
    Dim i As Integer
   
    Set db = Application.CurrentDb

'Get the data
    Set rs = db.OpenRecordset("SELECT * FROM MyTable")

'Instantiate the excel objects
    Set exApp = New Excel.Application
   
    Set exBook = exApp.Workbooks.Add
   
    exApp.Visible = True
   
    exApp.Interactive = False
   
    Set exSheet = exBook.Worksheets(1)

    If Not rs.EOF Then rs.MoveLast: rs.MoveFirst
   
'Populate the variables
    NoOfCols = rs.Fields.Count
    NoOfRows = rs.RecordCount

'Pop the data into Excel
    exSheet.Range("A2").CopyFromRecordset rs

'Write in the column headings
    For i = 0 To NoOfCols - 1
       
        exSheet.Cells(1, i + 1).Value = rs.Fields(i).Name
   
    Next i

'Use our variables to format the data populated cells ONLY
    exSheet.Cells.Range("A1", ExcelCodes(NoOfCols) & 1).Interior.Color = vbYellow

'And again - using both this time
    exSheet.Cells.Range("A1", ExcelCodes(NoOfCols) & (NoOfRows + 1)).Borders.Color = RGB(0, 0, 0)

'Adjust column widths
    exSheet.Columns.EntireColumn.AutoFit

'Save it
    exBook.SaveAs "C:\Temp\Temp.xls"
   
ExportData_Exit:

'Very important - always account for in error trap
    exApp.Interactive = True

'Clean Up
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing

    Set exSheet = Nothing
    Set exBook = Nothing
    Set exApp = Nothing
   
    Exit Sub
   
ExportData_Error:
   
    MsgBox Err.Description
    Resume ExportData_Exit
End Sub

Function ExcelCodes(ByVal intColNo As Integer) As String
 
    Dim strCol As String
 
    Do While intColNo > -1
        If intColNo > 26 Then
            strCol = Chr(64 + ((intColNo - 1) \ 26))
            intColNo = intColNo - (26 * ((intColNo - 1) \ 26))
        Else
            strCol = strCol & Chr(64 + intColNo)
            Exit Do
        End If
    Loop
 
    ExcelCodes = strCol
 
End Function

You can use the macro recorder in Excel to record your keystrokes while formatting columns, cells etc. in a "test" spreadsheet, then copy the code from the macro into Access to simplify writing the formatting code in your database.

Thanks for this code pootle flump. I am trying to make some sense of it for my project. I'll let you know how I get on.

Jim,

Could you please provide me with more information on the code that you have?

Thanks for all your help.

So long as you change MyTable to a name of a query\ table in your db and have a C:\Temp dir you should be able to paste and run as is.

Happy playing!

(Bah.  I did not copy-paste the Excel VBA code last night like I thought I would get around to, so it will have to be tonight, unless another Access expert beats me to it.)

Hi Pootle,

I have inserted the code to my page, what would suggest is the best way to call this function. I have a button to press to start the process. How should I start to call the function?

Thanks,
Susan

Whatever your business requirements dictate\ whatever you feel comfortable with.
A "Run the report" button would be sufficent in most cases. Just pop:

Call ExportData

into the event handler. The Call bit is optional but I like it as it makes it explicit what is going on :-)

If you read up on Debugging, breakpoints and stepping through code (well, well worth the effort) then you can watch the code executre line by line - this would be very helpful to see on screen the effect of each line

HTH

Unfortunately when I run it I get an error message. I don't know if I am missing something. I get an error saying "Compile error: User-defined type not defined". The line that it highlights is the first line of function "Sub ExportData()". Not sure why it is bring this up. Any ideas?

Yup

Click (in the VB IDE):

Tools -> References

Check that (of the lines ticked) there include two entries a bit like:
Microsoft DAO 3.6
Microsoft Excel 1x.0

If not - scroll down, find them and tick them.

This sets you a reference to the type libraries - it informs access of the objects (and the properties and methods) you are going to use in the code.

Hi,

Thanks for your help, it works when I try to take information out of a table, but how do I get information out of a query?

Thanks again,
Susan

Same - just change the table name to the query name
:-)

I get an error when I do this. It says: "Run-time error '3061': Too few parameters. Expected 1." Any ideas?

Yep - that's a DAO error.

Please can you
a) Manually run the query and check it works ok
b) If so, please post the SQL

This is the SQL I am using:

Set rs = db.OpenRecordset("SELECT * FROM allreportinfomainmenu")

When I replace the name of the query with the name of a table it exports it.

Thanks!

Beg your pardon - I meant the SQL of the query :-)

Sorry!! It is quite long...

*****************************
SELECT Events.EventNumber, Events.Name, Events.EventDate, Car.CarNumber, Car.DateInserted, Car.Motor, Car.MotorTyp, Car.Getriebe, Car.Model, Car.numbercars, Findings.ID, Findings.IssueEnglish, Findings.Analysis, Findings.Rectification, Findings.dateD, Findings.Status, Findings.Issues, Findings.ratings, Findings.Path, Findings.Path2, Findings.IssueNr
FROM (Car LEFT JOIN Events ON Car.EventNumber = Events.EventNumber) LEFT JOIN Findings ON Car.CarNumber = Findings.CarNumber
WHERE (((Events.EventNumber) Like [Forms]![MainMenu]![txtpreview] & "*"));

*****************************

Sorry, I pasted it wrongly!!

*****************************
SELECT Events.EventNumber, Events.Name, Events.EventDate, Car.CarNumber, Car.DateInserted, Car.Motor, Car.MotorTyp, Car.Getriebe, Car.Model, Car.numbercars, Findings.ID, Findings.IssueEnglish, Findings.Analysis, Findings.Rectification, Findings.dateD, Findings.Status, Findings.Issues, Findings.ratings, Findings.Path, Findings.Path2, Findings.IssueNr, Findings.CarNumber
FROM (Car LEFT JOIN Events ON Car.EventNumber = Events.EventNumber) LEFT JOIN Findings ON Car.CarNumber = Findings.CarNumber
WHERE (((Events.EventNumber) Like [Forms]![MainMenu]![txtpreview] & "*"));

*****************************

Ok - no probs. As expected- it has a parameter. Hang on....

I think it is coming up with an error as I am trying to use this "where" statement. I really need to use it for the reports, is there anything that I can do?!

Fantastic!! Finally works now!! If I had not had your help I would have given up when that didn't work there. Thanks for your efforts!  :o)

Super - Glad it worked :-)

If you are a clever bunny you could work out a way of making the function generic. As it stands 1 query = 1 function however you could make this more flexible so that 1 function will work with all queries. Something for the future perhaps....

Good luck.