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
953 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

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

820 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