export data to excel, first column to a others different

[benjamin.grimm]

Hello togehter,

i export a query from access to excel by the following code:

Set xlSheet = xlBook.Worksheets(1)
Set rst = CurrentDb.OpenRecordset("Vorratseinplannung_MB_Gesamt")
xlSheet.Cells(7, 13 + Month(Date)).CopyFromRecordset rst
rst.Close

How can i do it that the first column gets imported in the column A and the rest of the query gets imported to:

xlSheet.Cells(7, 13 + Month(Date)).CopyFromRecordset rst

Greetz Benjamin

 

[Rx_]

There are basically three approaches. The copyrecordset is a built in function without much wiggle room to do what you need. Here are three approaches.
1. Set up multiple recordsets in Access, then copy them to the respected starting points.
2. step through a recordset a row at a time, reference the Excel Row and move the data over with your formula. Then move to next row in your recordset and the next row in Excel reference.
3. Move the record set all at once. Then use Excel automation to evauate each row one at a time with your formula and move the cell (range) data to its respective location.

Here is a code fragement for method 2 - take the Recordset header value and move it in one column at a time, a Case statement could be stuck into the loop to implement your formula.

560         Set rsDataSundries = CurrentDb.OpenRecordset(strSQLBattery, dbOpenSnapshot, dbReadOnly) ' suggestion was this could be faster but it is not
            'Set rsDataSundries = CurrentDb.OpenRecordset(strSQLBattery, dbOpenSnapshot)
570         intRowPos = 6                                                                                 ' Sets starting Row for data in Excel - reference fields to this
580         DoEvents
590         ObjXL.DisplayAlerts = False                                                       ' Turn off Display Alerts
600         ObjXL.Worksheets(intWorksheetNum).Cells(intRowPos, 1).CopyFromRecordset rsDataSundries
610         DoEvents
620         intMaxRecordCount = rsDataSundries.RecordCount - 1                                                      ' - use for max rows returned in formatting later
           'Debug.Print "max record count is " & intMaxRecordCount
                                                        ' ------- Create Header in new Excel based on Query
630       intMaxheaderColCount = rsDataSundries.Fields.count - 1 ' get a field count
640       For intHeaderColCount = 0 To intMaxheaderColCount
650           If Left(rsDataSundries.Fields(intMaxheaderColCount).Name, 3) <> "xxx" Then  ' Future use - adding xxx in cross tab queries for fields to exclude
660               ObjXL.Worksheets(intWorksheetNum).Cells(intRowPos - 1, intHeaderColCount + 1) = rsDataSundries.Fields(intHeaderColCount).Name    ' Relative to intRowPos
670           End If
680       Next intHeaderColCount
          'Debug.Print "Columns created count is " & intHeaderColCount
690       ObjXL.Rows((intRowPos - 1) & ":" & (intRowPos - 1)).Select                                    ' Selection for Bold header column

For 3, here is a simple loop that evaluates the cell in row B above and determines if it is repeated, then it simpley bolds the row above. This gives a nice visual appearance of a change in sorted value.

      Dim c                       As Excel.Range
      Dim ObjXL                 As Excel.Application
     Dim i                       As Integer ' just a local counter
' set up the object variable assignments not shown
' intRowPos   All my Excel reports start data at row 5 instead of row 1
'  intMaxRecordCount  get the record count from recordset to use later
1520      Set c = Nothing
            With ObjXL.ActiveWorkbook.ActiveSheet
                For i = intRowPos To intMaxRecordCount + intRowPos
                 If .Cells(i, "B").Value <> .Cells(i - 1, "B").Value Then
                       '.Range(.Cells(i, "B"), .Cells(i, "H")).Font.FontStyle = "Bold"
                       '.Cells(i, 33).Value = .Cells(i, 3).Value
                 Else
                       '.Range(.Cells(i, "B"), .Cells(i, "C")).Font.ColorIndex = 16 'metalic gray
                 End If
               Next i
            End With

Just a easy, you could take a value in row B, cut it, and past it to the right per your formula.
Note: it was a good thing to put the recordset count into a variable to know how many times to run this loop.
Useing Excel objects, this loop and method will actually run very fast.
'.Range(.Cells(i, "B"), .Cells(i, "H")).Font.FontStyle = "Bold"
See that the row(i) column B value can reach out and modify column H.
In this case it is simply a bold. However, it can change the value, delete the value in column H or whatever your forumula wants to do.

Engineers, management, and planners love to see verbose Excel data formulated into a pattern they can sort or otherwise manage.

Method 3 might be a little of a learning curve, but would be worth learning.