formulier exporteren naar Excel en gegevens transponeren

[Bospeen]

Beste forumleden,

Ik wil de gegevens van een formulier exporteren naar Excel en deze daar in kolomvorm zien, dus onder elkaar (2 kolommen: veldnaam en gegevens).
Het (standaard) macrootje dat het formulier nu exporteert zet de gegevens achter elkaar.
Iemand een suggestie om dit voor elkaar te krijgen?

NB. het formulier toont altijd maar één record.

 

[OctaFish]

?? Dan heb je dus maar één record, en lijkt het mij logisch dat de data naast elkaar in kolommen staat en niet onder elkaar. Je zult dat record moeten kunnen splitsen in meerdere records, anders zul je het transponeren in Excel moeten doen (waar het overigens heel erg simpel is).

 

[Bospeen]

Het is één record met ca. 100 velden (verschillende formaten), die wil ik voor de overzichtelijkheid in Excel onder elkaar zien.
Is daar niet een eenvoudige programmacode voor te schrijven, dat zoiets bevat als transpone = true?

En hoe zou ik anders dat ene record moeten splitsen?

 

[OctaFish]

Niet in Access. Je kunt uiteraard een procedure schrijven die alle velden uitleest in een nieuwe recordset en daar wat mee gaat knoeien (nieuwe tabel maken en zo) maar in Excel is het één handeling; ik zou het vanuit Excel doen.

 

[Bospeen]

Ok, in ieder geval bedankt voor het meedenken.

 

[OctaFish]

Ik weet nog steeds niet of het überhaupt kan in Access wat je wilt;

Het (standaard) macrootje dat het formulier nu exporteert zet de gegevens achter elkaar.

Dit is de enige clou die je tot nu toe hebt gegeven. Als je een bruikbare tabel hebt, kan het misschien met een draaitabel. Maar we weten dus gewoon te weinig. Anders dat je een macro hebt (gemaakt) die alles in één record plempt, wat mij overigens niet als heel erg handig voorkomt, maar dit terzijde.

 

[Bospeen]

Het formulier toont het gevonden record op basis van een query op meerdere tabellen (a.h.v. door de gebruiker op te geven criteria). Het formulier wordt vanuit een ander formulier geopend, ik heb dit middels een knop met macro gedaan, maar dat kan natuurlijk ook anders. De gegevens van het formulier (eigenlijk de query!) moeten naar Excel, maar dan wel in overzichtelijke layout. Dus onder elkaar, want ik wil niet dat de gebruiker met één rij geconfronteerd wordt die pakweg honderd kolommen breed is. Aan de knop voor export naar Excel kan ook programmacode gehangen worden, mijn visual basic is echter niet zo goed dat ik weet hoe ik de gegevens dan getransponeerd krijg in Excel. En ja, in Excel zelf is dit een fluitje van een cent, echter weer niet voor alle gebruikers. Vandaar mijn vraag, maar ik begrijp dat jouw visual basic ook niet helemaal top is(?).

 

[OctaFish]

maar ik begrijp dat jouw visual basic ook niet helemaal top is(?).

Waar lees je dat? Als er over mij geroddeld wordt, moet je dat vooral even doorgeven .
Ik had al aangegeven dat het wel kan met VBA. En dan bedoel ik uiteraard dat zo'n klusje voor mij makkelijk te doen is. Kwestie van een recordset maken en daar wat mee doen, desnoods gelijk in Excel zetten.

 

[Bospeen]

Ik had al aangegeven dat het wel kan met VBA. En dan bedoel ik uiteraard dat zo'n klusje voor mij makkelijk te doen is.

Help me daar dan even mee, misschien met een voorbeeldje.

 

[OctaFish]

Dan krijg je bijvoorbeeld zo'n functie:

Function Export2XLS()
Dim oExcel As Object
Dim oExcelWrkBk As Object
Dim oExcelWrSht As Object
Dim bExcelOpened As Boolean
Dim iCol As Integer, iRow As Integer
Dim iTel As Integer, i As Integer
Const xlCenter = -4108
 
Dim strSQL As String
Dim db As DAO.Database
Dim rst As ADODB.Recordset

    'Start Excel
    On Error Resume Next
    Set oExcel = GetObject(, "Excel.Application")    'Late Binding met Excel
    If Err.Number <> 0 Then    'Could not get instance of Excel, so create a new one
        Err.Clear
        On Error GoTo Error_Handler
        Set oExcel = CreateObject("Excel.Application")
        bExcelOpened = False
    Else    'Excel was already running
        bExcelOpened = True
    End If
    
    oExcel.ScreenUpdating = False
    oExcel.Visible = False      'Keep Excel hidden until we are done with our manipulation
    Set oExcelWrkBk = oExcel.Workbooks.Add()    'Start a new workbook
    Set oExcelWrSht = oExcelWrkBk.Sheets(1)
 
    Set rst = New ADODB.Recordset
    rst.Fields.Append "Testnr", adInteger
    rst.Fields.Append "Waardeveld", adVariant
    rst.Fields.Append "Waarde", adVariant
    rst.Open
    With CurrentDb.OpenRecordset("Tdatarangeinvoer", dbOpenSnapshot)
        Do While Not .EOF
            For i = 1 To .Fields.Count - 1
                iTel = iTel + 1
                rst.AddNew
                rst!Testnr = iTel
                rst!Waardeveld = .Fields(i).Name
                rst!Waarde = .Fields(i).Value
                rst.Update
            Next i
            .MoveNext
        Loop
    End With

        With rst
            If .RecordCount <> 0 Then
                .MoveFirst
                'Build our Header
                For iCol = 0 To .Fields.Count - 1
                    oExcelWrSht.Cells(1, iCol + 1).Value = .Fields(iCol).Name
                Next
                With oExcelWrSht.Range(oExcelWrSht.Cells(1, 1), oExcelWrSht.Cells(1, .Fields.Count))
                    .Font.Bold = True
                    .Font.ColorIndex = 2
                    .Interior.ColorIndex = 1
                    .HorizontalAlignment = xlCenter
                End With
                'Resize our Columns based on the headings
                oExcelWrSht.Range(oExcelWrSht.Cells(1, 1), oExcelWrSht.Cells(1, .Fields.Count)).Columns.AutoFit
                        
                Do While Not .EOF
                    iRow = iRow + 1
                    For iCol = 0 To .Fields.Count - 1
                        oExcelWrSht.Cells(iRow + 1, iCol + 1).Value = .Fields(iCol).Value
                    Next iCol
                    .MoveNext
                 Loop
                oExcelWrSht.Range("A1").Select
            Else
                MsgBox "There are no records returned by the specified queries/SQL statement.", vbCritical + vbOKOnly, _
                    "No data to generate an Excel spreadsheet with"
            End If
        End With
    
    rst.Close
    Set rst = Nothing

End Function