Solved

Formatting and exported report or query to Execl

Posted on 2014-03-28
6
264 Views
Last Modified: 2014-04-07
I have a report in Access that I need to export to Excel. The report is formatted correctly like this.

Customer Name  Account1 Number, Charge
                              Account2 Number, Charge
                              Account3 Number, Charge,
and so on.

When the report or even the query its based on exports it looks like this.

Customer Name, Account 1 number, Charge, account 2 number, Charge, Account 3 number charge.

Is there any way to control the formatting of the export from Access or even in Excel?

Thanks,
0
Comment
Question by:Mwvarner
  • 2
  • 2
  • 2
6 Comments
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 39962769
The best way is to prepare an Excel template, formatted as you wish, and export the data to a new workbook made from the template, using VBA code.  There are several ways to do this -- you can use the CopyFromRecordset method of an Excel range if the data is in a block, or write each field to a cell if the layout is more complex.  Here is some code for using CopyFromRecordset (though in this code the workbook is not made from a template):
   Dim appExcel As New Excel.Application
   Dim cnn As ADODB.Connection
   Dim wkb As Excel.Workbook
   Dim sht As Excel.Worksheet
   Dim strWorkbook As String
   Dim strRange As String
   Dim lngLastRow As Long
   Dim rst As ADODB.Recordset
   Dim rng As Excel.Range
   Dim strWorkbookName As String
   Dim strDefault As String
   
   DoCmd.SetWarnings False
   strPrompt = "Enter workbook name (no extension)"
   strTitle = "Workbook name"
   strDefault = "New Access Data"
   strWorkbookName = InputBox(strPrompt, strTitle, strDefault)
   
   'Run make-table queries
   DoCmd.OpenQuery "qmakCAContacts"
   
   Set cnn = CurrentProject.Connection
   Set rst = New ADODB.Recordset
   
   'Create a recordset based on a select query.
   rst.Open Source:="qryContacts", _
      ActiveConnection:=cnn.ConnectionString, _
      CursorType:=adOpenForwardOnly
      
   'Export query
   Set wkb = appExcel.Workbooks.Add
   appExcel.Visible = True
   strWorkbook = Application.CurrentProject.Path & "\" & strWorkbookName
   wkb.SaveAs FileName:=strWorkbook
   Set sht = wkb.Sheets(1)
   strRange = "A1"
   Set rng = sht.Range(strRange)
   rng.CopyFromRecordset rst
   rst.Close

Open in new window

0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 39962777
Here is some code that works from Excel:
Public Sub GetDataFromAccessDAO()
'Created by Helen Feddema 23-Feb-2010
'Last modified by Helen Feddema 23-Feb-2010

On Error GoTo ErrorHandler

   Dim strDatabaseNameAndPath As String
   Dim strRecordSource As String
   Dim dbe As DAO.DBEngine
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim strSQL As String
   Dim lngCount As Long
   Dim rng As Excel.Range
   Dim strPrompt As String
   Dim strTitle As String
   Dim strSearch As String
   Dim strLastName As String
   
   strRecordSource = "qryNewContacts"
   strDatabaseNameAndPath = "G:\Documents\ExpertsExchange\ExpertsExchange Sample Code.mdb"
   Set dbe = DAO.DBEngine
   
   'Connect to database with password
   'Set dbs = dbe.OpenDatabase(Name:=strDatabaseNameAndPath, _
      Options:=False, _
      ReadOnly:=True, _
      Connect:=";pwd=xyxyxyx")
   
   'Connect to database without password
   Set dbs = dbe.OpenDatabase(Name:=strDatabaseNameAndPath, _
      Options:=False, _
      ReadOnly:=True)
   Set rst = dbs.OpenRecordset(strRecordSource, dbOpenDynaset)
   Set rng = Application.Sheets(1).Range("G1")
   rng.CopyFromRecordset rst
   
   rst.Close
   dbs.Close
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in GetDataFromAccessDAO procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window


For formatting after import, you can work with cell, range and row properties of the Excel object model.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39963211
It's hard to tell exactly what you are talking about since the formatting is not precise in this forum but it looks like you have one column that you have used with a concatenation function to populate with data from three records.  Are you asking how to put all the data into a single cell and retain the line feeds to make the cell three rows tall? or do you want the data exported as three separate rows?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Mwvarner
ID: 39963219
In the query each record consist of a customer name, multiple account numbers and an amount to be charged to each account.  When I export the report or the query its based on now I see each record displayed horizontally.   what I need is each account and the amounts to be charged on separate lines.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39963289
Rather than exporting this query, go back to the query before you concatenated the multiple records and export that one.
0
 

Author Closing Comment

by:Mwvarner
ID: 39984430
Thanks Helen,

I think I can work with this and get it working.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ms/access hyperlink/ftp 7 35
Change AD password via MS Access DB 2 17
Running sum query 6 30
How do I refer to a session variable in a query? 4 19
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

911 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

18 Experts available now in Live!

Get 1:1 Help Now