?
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
Medium Priority
?
957 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 400 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

777 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