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
951 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

914 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

19 Experts available now in Live!

Get 1:1 Help Now