Solved

Column order changes when data is exported from Access reports to Excel using "Analyze It with Excel" option

Posted on 2004-08-13
10
954 Views
Last Modified: 2013-11-28
Hi Friends,

I have a problem related to MS-Access reports. In the report i have some 14 fields(columns) displayed . I am using the "Analyze It with Excel" option to transfer the Access data to Excel.  The data gets transferred correctly but the problem is the order of the Columns changes.e.g. In my Access Reports "Id" is the first column and "Name" is the 2nd column and so on. But when I see in the Excel sheet, the Name is displayed in 4th column , Id in 2nd column and so on. Why does this happen?

Also , in the Page Header of the Access reports I am displaying a date value in a text box which comes from one of the Access forms. I have set the Format value for this textbox to "dd/mm/yyyy"(Short Date) . But the date value dislayed in the Excel sheet is a number and not a Date. I have to explicitly change the date value in Excel using the "Format Cells" option.

In the Access Reports, page Header I have 3 different text boxes,say Report Name,Date and Organisation name , but when converted to Excel the order of these text boxes values change. eg. In front of the  Organisation Name the date gets displayed and so on.

It would really be great if anyone can help me out with these problems and it's kinda urgent.


Warm Regards,
Meenal:-)
0
Comment
Question by:MeenalReddy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
10 Comments
 

Expert Comment

by:DebabrataDas
ID: 11791509
Hi Meenal,
* In the Detail part of the Report, do check the top orientation of the Fields to be the same.
As during the Analyze with Excel, it automatically allocates the columns as per the top of the field first.
* Same with the Report Header too, anything on a higher position will be located to left of the others.
* Regarding the date Format, I believe you will have to bear with it. As I was unable to achieve the same.

Regards,
Dev
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 11798414
Meenal,

Automating Excel from Access would give you more control over the way the spreadsheet is generated including formatting.

Jim
0
 

Author Comment

by:MeenalReddy
ID: 11807018
Hi Dev,
The first two solutions related to setting the same top orientation  for all the columns work perfectly fine.
Thanks a lot for your valuable suggestion.

But the problem related to Date Format still exist :-(
If anyone can help me out with it, it would be really great.

Hi Jim,
Sorry, but i didn't get what you meant to say in your comments "Automating Excel from Access would give you more control over the way the spreadsheet is generated including formatting". Could you please elaborate on this?

Regards,
Meenal:-)
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 12776892
Please don't classify as abandoned, I don't want to use a solution provided and not give the points. I still haven't tried that solution.... I got slammed on another project. I will be able to get back to that item by the middle of next week.

Jim
0
 
LVL 18

Accepted Solution

by:
jmoss111 earned 100 total points
ID: 12803241
I'm too busy... I replied to the wrong question. I meantto reply to a question that I had asked and got an answer but had been unable to try.

Meenal,

An example of what I was talking about would be to open a spreadsheet, turn on macro recorder, and then format the sheet like you wanted it. Turn off the macro recorder then go  open the macro and copy the code generated by the formatting into an access module with a recordset populated by your table or query and a loop.

There is a function GetCOID() whose only purpose is to be used to pass a parameter to the recordset. The example builds a spreadsheet for each cost center in the table

Example:

Option Compare Database
Public pCOID As Long
Public pCOID2 As Long

Public Sub SetpCOID(lCOID As Long)
'Set the public variable to the value passed in via Rs0
pCOID = lCOID
End Sub
Public Function GetCOID()
'Return  the value of the variable
GetCOID = pCOID
End Function

Public Sub cmdOutputFilesToExcel()
   Dim MyDb As Database
   Dim MyRs0 As Recordset
   Dim MyRs1 As Recordset
   Dim MyRs2 As Recordset
   
   
   Dim MyExcel As New Excel.Application
   
   Set MyDb = CurrentDb
   Dim CurrentRow As Long
 
   
         Set Rs0 = MyDb.OpenRecordset("SELECT DISTINCT CC FROM qrsMonthlyReport;", dbOpenSnapshot)
         
         Rs0.MoveLast
         Rs0.MoveFirst
         
       
     Do While Not Rs0.EOF
         pCOID = Rs0!CC
         
       
         
         Set Rs1 = MyDb.OpenRecordset("SELECT CC, FullName, Memo, WeekEndDate, TranType, Hours, Rate, ExtPrice FROM qrsMonthlyReport WHERE CC = GetCOID();", dbOpenSnapshot)
         
          MyExcel.Workbooks.Add
         
   
          MyExcel.Worksheets.Add
          Range("A1:H1").Select
 '================================================
'formatting code from Excel macro
'=================================================        
          Selection.Borders(xlDiagonalDown).LineStyle = xlNone
          Selection.Borders(xlDiagonalUp).LineStyle = xlNone
          With Selection.Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
          End With
          With Selection.Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
          End With
          With Selection.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
          End With
          With Selection.Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
          End With
          With Selection.Interior
                .ColorIndex = 19
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
          End With
          MyExcel.Selection.Font.Name = "Arial"
          MyExcel.Selection.Font.Size = 9
          MyExcel.Selection.Font.Bold = False
          MyExcel.Selection.Font.Italic = False
         
       
         
         
          CurrentRow = 1
          MyExcel.Cells(CurrentRow, 1).Select
          MyExcel.ActiveCell.FormulaR1C1 = "CostCenter"
         
          MyExcel.Cells(CurrentRow, 2).Select
          MyExcel.ActiveCell.FormulaR1C1 = "Full Name"
          MyExcel.Cells(CurrentRow, 3).Select
          MyExcel.ActiveCell.FormulaR1C1 = "Memo"
          MyExcel.Cells(CurrentRow, 4).Select
          MyExcel.ActiveCell.FormulaR1C1 = "Week End Date"
          MyExcel.Cells(CurrentRow, 5).Select
          MyExcel.ActiveCell.FormulaR1C1 = "Trans Type"
          MyExcel.Cells(CurrentRow, 6).Select
          MyExcel.ActiveCell.FormulaR1C1 = "Hours"
          MyExcel.Cells(CurrentRow, 7).Select
          MyExcel.ActiveCell.FormulaR1C1 = "Rate"
          MyExcel.Cells(CurrentRow, 8).Select
          MyExcel.ActiveCell.FormulaR1C1 = "Ext Price"
         
         
          Do Until Rs1.EOF
            CurrentRow = CurrentRow + 1
           
           
           
           
         
           
            MyExcel.Cells(CurrentRow, 1).Select
            MyExcel.ActiveCell.FormulaR1C1 = Rs1("CC")
            MyExcel.Cells(CurrentRow, 2).Select
            MyExcel.ActiveCell.FormulaR1C1 = Rs1("FullName")
            MyExcel.Cells(CurrentRow, 3).Select
            MyExcel.ActiveCell.FormulaR1C1 = Rs1("Memo")
            MyExcel.Cells(CurrentRow, 4).Select
            MyExcel.ActiveCell.NumberFormat = "mm/dd/yy;@"
            MyExcel.ActiveCell.FormulaR1C1 = Rs1("WeekEndDate")
            MyExcel.Cells(CurrentRow, 5).Select
            MyExcel.ActiveCell.FormulaR1C1 = Rs1("TranType")
            MyExcel.Cells(CurrentRow, 6).Select
            MyExcel.ActiveCell.FormulaR1C1 = Rs1("Hours")
            MyExcel.Cells(CurrentRow, 7).Select
            MyExcel.ActiveCell.FormulaR1C1 = Rs1("Rate")
            MyExcel.Cells(CurrentRow, 8).Select
            MyExcel.ActiveCell.Style = "Currency"
            MyExcel.ActiveCell.FormulaR1C1 = Rs1("ExtPrice")
           
            '
            Rs1.MoveNext
          Loop
            Set Rs2 = MyDb.OpenRecordset("SELECT CC, ExtPrice FROM qrsCCTotals WHERE CC = GetCOID();", dbOpenSnapshot)
            CurrentRow = CurrentRow + 1
           
            MyExcel.Cells(CurrentRow, 8).Select
            MyExcel.ActiveCell.Style = "Currency"
            MyExcel.ActiveCell.FormulaR1C1 = Rs2("ExtPrice")
           
         MyExcel.Sheets("Sheet4").Select
         MyExcel.Sheets("Sheet4").Name = GetCOID()
         MyExcel.Sheets("Sheet1").Delete
         MyExcel.Sheets("Sheet2").Delete
         MyExcel.Sheets("Sheet3").Delete
         MyExcel.ActiveWorkbook.SaveAs ("200411 MonthlyPay " & GetCOID())
         MyExcel.ActiveWorkbook.Close
         
         
      Rs0.MoveNext
    Loop
     
      Set MySheet = Nothing
      Set MyBook = Nothing
      Set MyExcel = Nothing
     
End Sub
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 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