将特定单元格数据导出至MS Access及VBA报错问题求助

Hey Mustafa, let's tackle this "Object Required" error and get your messy Excel data sorted out for that SQL query. First, let's break down what's going wrong and how to fix it.

常见原因与排查步骤 #

1. 工作表引用不明确 #

The most likely culprit here is how you're referencing Sheet1 and Sheet2. If your actual worksheet names have been changed (e.g., to Chinese labels like "数据源" or "整理结果"), VBA can't find the objects you're calling.

• Fix: Instead of relying on default sheet names, explicitly reference your worksheets using either their code name (check the (Name) field in the VBA editor's Properties window) or their display name with ThisWorkbook.Worksheets().

2. 未声明变量 #

Your code uses i without declaring it. While VBA allows implicit declarations, this can lead to typos or unexpected behavior. Adding Option Explicit at the top of your module forces variable declaration, which catches these issues early.

3. 循环范围不匹配 #

You mentioned your Excel has 13056 rows total, but your loop only goes up to row 5000. This will miss most of your data—adjust the loop to cover all rows.

修正后的VBA代码 #

Here's a revised version of your code that addresses these issues, plus some quality-of-life improvements:

Option Explicit

Sub fixData()
    Dim writeRow As Integer, i As Integer
    Dim wsSource As Worksheet, wsTarget As Worksheet
    
    ' Replace with your actual worksheet names if they're not Sheet1/Sheet2
    Set wsSource = ThisWorkbook.Worksheets("Sheet1")
    Set wsTarget = ThisWorkbook.Worksheets("Sheet2")
    
    writeRow = 1
    ' Turn off screen updating to speed up processing for large datasets
    Application.ScreenUpdating = False
    
    ' Loop through every 3 rows starting from row 9 (adjust if your data starts later/earlier)
    ' End at row 13056 to cover all your records
    For i = 9 To 13056 Step 3
        ' Map your fields correctly (note: fixed a potential typo in Daire No below)
        wsTarget.Cells(writeRow, 1).Value = wsSource.Cells(i + 1, 1).Value ' Streetname
        wsTarget.Cells(writeRow, 2).Value = wsSource.Cells(i + 1, 2).Value ' Building No
        wsTarget.Cells(writeRow, 3).Value = wsSource.Cells(i + 1, 3).Value ' Daire No (was same as Building No before)
        wsTarget.Cells(writeRow, 4).Value = wsSource.Cells(i, 3).Value ' Name
        wsTarget.Cells(writeRow, 5).Value = wsSource.Cells(i + 2, 3).Value ' Surname
        wsTarget.Cells(writeRow, 6).Value = wsSource.Cells(i, 5).Value ' Gender
        wsTarget.Cells(writeRow, 7).Value = wsSource.Cells(i, 6).Value ' Baba
        wsTarget.Cells(writeRow, 8).Value = wsSource.Cells(i + 2, 6).Value ' Anne
        wsTarget.Cells(writeRow, 9).Value = wsSource.Cells(i, 7).Value ' il (STATE/PROVINCE)
        wsTarget.Cells(writeRow, 10).Value = wsSource.Cells(i + 2, 7).Value ' ilce
        
        ' Add your remaining 10+ fields here following the same pattern
        
        writeRow = writeRow + 1
    Next i
    
    Application.ScreenUpdating = True
    MsgBox "Data cleaning complete! Processed " & writeRow - 1 & " records."
End Sub

额外注意事项 #

• Field Mapping Check: Double-check that the row/column indices match your actual data (I fixed a possible typo where Daire No was using the same cell as Building No—adjust this based on your screenshot).
• SQL Readiness: After running the macro, make sure your target sheet has clear column headers (e.g., name column 9 "STATE/PROVINCE") so your SQL query SELECT * FROM PEOPLE WHERE STATE/PROVINCE = "ŞANLIURFA" works as expected.
• Edge Cases: If your last group of data has fewer than 3 rows, add a check like If i + 2 <= wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row Then to avoid out-of-bounds errors.

内容的提问来源于stack exchange，提问作者Mustafa Yaşar