Using For loop for selecting specific Fields in Access and pasting them to excel

Question

I made a specific Access Form that has many field with same names only the number at end changes. I have fields named "Code1", "Code2", "Code3" etc. I would like to paste the fields from Form view to Excel Cells. There is about 150 fields and I don't want to add them one by one.

I made a button that opens Excel template, and made a For loop but I am stuck. This is the idea:

Set MyXL = CreateObject("Excel.Application")

        With MyXL

        .Application.Visible = True
        .Workbooks.Open "F:\0. Main\01.Templates\Order.xltx"


        Dim broj As Variant
        broj = UCase(ID)

        Dim Kod As Variant
        Dim Tip As Variant
        Dim Kolic As Variant

        For i = 1 To 30

   -------> Kod = Code(i).Value
            .Worksheets("Sheet1").Cells(11 + i, 2).Value = Kod

   -------> Tip = Type(i).Value
            .Worksheets("Sheet1").Cells(11 + i, 3).Value = Tip

   -------> Tip = Qty(i).Value
            .Worksheets("Sheet1").Cells(11 + i, 3).Value = Kolic

        Next i

I don't know how to include FieldName + (number) in For loop

Why don't just export the access table directly to an excel file? see https://stackoverflow.com/questions/1849580/export-ms-access-tables-through-vba-to-an-excel-spreadsheet-in-same-directory — simple-solution, Jan 25 '19 at 13:16
Do you mean `rs.Fields("Code" & i).Value` where rs is the recordset you loop through? — Asger, Jan 25 '19 at 13:32

Asger · Accepted Answer · 2019-01-25T16:28:44.077

Please check if a simple paste of the whole recordset directly to Excel's range will work (will overwrite starting at L2 as much as needed):

.Worksheets("Sheet1").Cells(12, 2).CopyFromRecordset

If this works, you may want to clear the range before pasting on it:

.Worksheets("Sheet1").Range("L2:N100000").ClearContent

If that doesn't help, you may want to loop over the recordset within Access e. g. like this:

Set rs = CurrentDb.OpenRecordset("WhatEver", dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    i = 1
    Do
        ... = rs.Fields("Code" & i).Value
        ...
        rs.MoveNext
        i = i + 1
    Loop Until i > 30 ' or Until rs.EOF
End If

I used my old For loop but added Set rs = CurrentDb.OpenRecordset("WhatEver", dbOpenDynaset) and rs.Fields("Code" & i).Value And now it works :) Thank you — Dragan, Jan 25 '19 at 14:46

score 0 · Answer 2 · answered Jan 25 '19 at 14:46

Option Compare Database
Sub Export()
Dim tableName As String

    tableName = InputBox("What is the name of the table you want to export?")
    Dim outputFileName As String
    outputFileName = CurrentProject.Path & "\Export_" & Format(Date, "yyyyMMdd") & ".xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, tableName, outputFileName, True

    Set myXl = CreateObject("Excel.Application")
    myXl.Visible = True
    myXl.workbooks.Open outputFileName

End Sub

Using For loop for selecting specific Fields in Access and pasting them to excel

2 Answers2