General discussion

Locked

Exporting an Access Report to Excel

By charles_r_conic ·
I need to take a report that I have created in Microsoft Access and send it to Excel. The problem that I have is that some of the data on the report is created using VBA and there are groupings and totals on the report that don't come out like I want them. Is there a way to capture all of these things and format them to an Excel Spreadsheet.

This conversation is currently closed to new comments.

10 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Exporting an Access Report to Excel

by fenaikh In reply to Exporting an Access Repor ...

Hi
please look at these Utilities and articles:

I Utilities:

1- 4TOPS Export Wizard for MS Access 97 v1.0

http://www.zdnet.com/downloads/stories/info/0,,001ALG,.html


2- 4TOPS Chart in Excel Wizard for MS Access 97 v1.0

http://www.zdnet.com/downloads/stories/info/0,,001ALI,.html


3- Easy Report for MS Excel 95 or 97 v2.0

http://www.zdnet.com/downloads/stories/info/0,,0012F2,.html


4- Access Archon - Exporting Access Reports

http://www.zdnet.com/zdhelp/stories/main/0,5594,2214036,00.html

II articles:

1- Exporting Access Data

http://www.zdnet.com/zdhelp/stories/main/0,5594,2626207,00.html


2- Exporting Data to a Text File

http://www.zdnet.com/zdhelp/stories/main/0,5594,2626207-3,00.html

3-Create a custom AutoFormat style
http://www.woodyswatch.com/access/archtemplate.asp?3-01


3- Exporting Data to Another Database

http://www.zdnet.com/zdhelp/stories/main/0,5594,2626207-2,00.html

I hope you like it

good luckMohamed

Collapse -

Exporting an Access Report to Excel

by charles_r_conic In reply to Exporting an Access Repor ...

I cannot get to any of the ZDnet sites.

Collapse -

Exporting an Access Report to Excel

by Peyison In reply to Exporting an Access Repor ...

Here are a few options you can try:

1) You can send the report as is to an Excel workbook. If you're not happy with the groupings and totals in the Access report, you can remove them and send the basic data to Excel. You can do this in code usingDoCmd.OutputTo:

DoCmd.OutputTo acOutputReport, sReportName, acFormatXLS, sFileName, False

2) You can write VBA code to send the output of your queries to a CSV (comma separated values) file. A CSV file can be opened by Excel (there is no formatting though). You'd write VBA code to send your output to a text file, separating each field with a comma.

3) You can use Access VBA to open (or create) an Excel workbook and send the data directly to it. This would provide the most control of how the end product would look. You can enter data and format cells all from code. This is also the most complex route, but if you're familiar with VBA, it's not too bad.

If you're interested in more info on any of these options, let me know.

Hope this helps.

Collapse -

Exporting an Access Report to Excel

by charles_r_conic In reply to Exporting an Access Repor ...

Donna, I am trying to use automation to create an excel spreadsheet and I can't seem to get the examples to work. I keep getting an error when I try to use the "xl.cells(1,1).value = "hello world". The error says "Object does not support thisproperty or method. Can you give me some further guidence?

Collapse -

Exporting an Access Report to Excel

by fenaikh In reply to Exporting an Access Repor ...

Hi
please make sure no spaces when you copy the
URL's into your browser this is what maybe happend to you. I sent you the first 2 pages thru e-mail.

good luck


Mohamed

Collapse -

Exporting an Access Report to Excel

by charles_r_conic In reply to Exporting an Access Repor ...

Poster rated this answer

Collapse -

Exporting an Access Report to Excel

by Peyison In reply to Exporting an Access Repor ...

First off - make sure you have a reference in your VB app to the Excel library.

Project -> References -> Check Microsoft Excel x Object Library

Here are some code samples that will start Excel, put a value in a cell, put a value in a range, save the workbook, and quit without saving: (These should all go in the same module)

'--global declarations of the module/class
Private moExcel As Object
Private moWorkbook As Workbook
Private msWorkBook As String
Private moApplication As Application


Public Sub StartExcel(asWorkbook As String, Optional asPassword As String = "", Optional asPath As String = "", Optional abVisible As Boolean = True)
Dim sPath As String
Dim sFileName As String

If Len(asPath) < 1 Then
sPath = App.Path
Else
sPath = asPath
End If

Set moExcel = CreateObject("Excel.Application")
If Len(asWorkbook) > 0 Then
sFileName = sPath & asWorkbook
If Len(asPassword) > 0 Then
moExcel.Workbooks.Open sFileName, , , , asPassword
Else
moExcel.Workbooks.Open sFileName
End If
Else
moExcel.Workbooks.Add
End If

msWorkBook = moExcel.ActiveWorkbook.Name


Set moApplication = moExcel.Application

If abVisible Then
moApplication.Visible = True
moExcel.Parent.Windows(msWorkBook).Visible = True
End If

Set moWorkbook = moApplication.Workbooks(msWorkBook)

End Sub

more to follow....

Collapse -

Exporting an Access Report to Excel

by Peyison In reply to Exporting an Access Repor ...

Public Sub PutValueInCell(asSheetName As String, alRow As Long, alCol As Long, avValue As Variant)

Select Case TypeName(avValue)
Case "Integer"
moApplication.Cells(alRow, alCol).Value = CInt(avValue)
Case "Long"
moApplication.Cells(alRow, alCol).Value = CLng(avValue)
Case "Double", "Decimal", "Currency"
moApplication.Cells(alRow, alCol).Value = CDbl(avValue)
Case "String", "ComboBox", "MaskEdBox"
moApplication.Cells(alRow, alCol).Value= CStr(avValue)
Case Else
MsgBox "Unable to write the value '" & avValue & "' to the workbook.", vbExclamation
End Select

End Sub

Public Sub PutValueInRange(asSheetName As String, asRangeName As String, avValue As Variant)Dim oRng As Range
Dim oName As Name


Set oName = moWorkbook.Names(asRangeName)
Set oRng = oName.RefersToRange
Select Case TypeName(avValue)
Case "Integer"
oRng.Value = CInt(avValue)
Case "Long"
oRng.Value = CLng(avValue)
Case "Double", "Decimal", "Currency"
oRng.Value = CDbl(avValue)
Case "String", "ComboBox", "MaskEdBox"
oRng.Value = CStr(avValue)
Case Else
MsgBox "Unable to write the value '" & avValue & "' to the workbook.", vbExclamation
End Select


Set oName = Nothing
Set oRng = Nothing

End Sub

Public Sub QuitExcel()

moWorkbook.Saved = True
moApplication.Quit

Set moWorkbook = Nothing
Set moApplication = Nothing
Set moExcel = Nothing

End Sub

Public Sub SaveAs(asFileName As String)

On Error Resume Next
Kill asFileName
On Error GoTo 0

moWorkbook.SaveAs asFileName

End Sub

Collapse -

Exporting an Access Report to Excel

by charles_r_conic In reply to Exporting an Access Repor ...

Poster rated this answer

Collapse -

Exporting an Access Report to Excel

by charles_r_conic In reply to Exporting an Access Repor ...

This question was closed by the author

Back to Windows Forum
10 total posts (Page 1 of 1)  

Related Discussions

Related Forums