Access Query To Excel Sheet With Proper Column & Row Format

Question

I already have the query that retrieves me the data in a correct way, this is my code.

Sub Main()

Dim sDBPath As String
sDBPath = "C:\Users\ges\Documents\ExploWR.mdb"

Call Query_Access_to_excel(sDBPath, "Explo1", "SELECT eipl.MOD_CODE, eipl.BOM_KEY, eipl.DIF, eipl.PART_NO, eipl.PART_DESC, eipl.QTY_PER_CAR, eipl.INTERIOR_COLOUR, eipl.EXTERIOR_COLOUR, eipl.SOURCE_CODE, eipl.SHOP_CLASS," & _
   " eipl.PART_CLASS, eipl.PROCESS_CODE, eipl.OPERATION_NO, eipl.DESIGN_NOTE_NO, eipl.WIP, eipl.PART_ID_CODE, eipl.ADOPT_DATE_Y2K,eipl.ABOLISH_DATE_Y2K, ipo_Modelos.EIM, ipo_Modelos.DEST, ipo_Modelos.MY " & _
   " FROM eipl, explo, ipo_Modelos" & _
   " WHERE explo.MOD_CODE = eipl.MOD_CODE And explo.MY = ipo_Modelos.MY" & _
   " And explo.PLANT = ipo_Modelos.PLANT And eipl.ADOPT_DATE_Y2K <= explo.ADOP " & _
   " And explo.DEST = ipo_Modelos.DEST And explo.EIM = ipo_Modelos.EIM")

End Sub

Sub Query_Access_to_excel(sBd As String, sHoja As String, sSQL As String)

     On Error GoTo error_handler

     Dim rs As ADODB.Recordset
     Dim conn As String
     Dim Range_Destino As Range  

     Set Range_Destino = ActiveWorkbook.Sheets(sHoja).Cells(6, 1)     
     conn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source= " & sBd & ";"
     Set rs = New ADODB.Recordset
     Call rs.Open(sSQL, conn, adOpenForwardOnly, adLockReadOnly)

     If Not rs.EOF Then

          Range_Destino.Offset(1, 0).CopyFromRecordset rs
          DoEvents

          MsgBox "Import Complete", vbInformation
     Else
          MsgBox "No registers to import", vbInformation
     End If

     If Not rs Is Nothing Then
        If rs.State = adStateOpen Then
            rs.Close
        End If
        Set rs = Nothing
     End If

     If Not Range_Destino Is Nothing Then
        Set Range_Destino = Nothing
     End If

     Exit Sub
error_handler:
     MsgBox Err.Description, vbCritical
End Sub

What I want to do is to correctly place the data in the cells, something like this.

And what I have is something like this. I want to place the data in the correct cells, I'm talking about the last 3 fields to be properly placed in the columns like the first image. I have no idea how to do this without affecting my query.

Answer 1

So as far as exporting the data into excel you have several options:

SQL do command: https://msdn.microsoft.com/en-us/library/office/ff844793.aspx

Same command but in VBA: Using Excel VBA to export data to MS Access table

You could iterate through the table and create an array then print that array into a spreadsheet

Once you have the data in excel you're just looking at formatting -- adding some filters, changing the text align, ect.. you can use the "Record Macro" function to perform those tasks and clean the code.

So I guess for clarification - what do you mean 'affecting your query?'