Obsah
S nižšie uvedenými postupmi môžete použiť DAO na získanie sady záznamov z uzavretého zošita a čítanie/zápis údajov.
Zavolajte postup takto:
GetWorksheetData "C: \ Názov priečinka \ Názov súboru.xls", "VÝBER * Z [SheetName $]", ThisWorkbook.Worksheets (1) .Range ("A3")
Nahraďte SheetName názvom pracovného hárka, z ktorého chcete načítať údaje.
Sub GetWorksheetData (strSourceFile As String, strSQL As String, TargetCell As Range) Dim db As DAO.Database, rs As DAO.Recordset, f As Integer, r As Long if TargetCell is nothing then Exit Sub On Error Resume Next Set db = OpenDatabase (strSourceFile, False, True, "Excel 8.0; HDR = Yes;") 'iba na čítanie' Set db = OpenDatabase (strSourceFile, False, False, "Excel 8.0; HDR = Yes;") 'write' Set db = OpenDatabase ( "C: \ Foldername \ Filename.xls", False, True, _ "Excel 8.0; HDR = Yes;") 'iba na čítanie' Set db = OpenDatabase ("C: \ Foldername \ Filename.xls", False, False, _ "Excel 8.0; HDR = Áno;") 'napísať pri chybe GoTo 0 Ak db nie je nič, potom MsgBox „Nemôžem nájsť súbor!“, VbExclamation, ThisWorkbook.Name Ukončiť Sub End If' 'vypísať názvy hárkov' For f = 0 Do db.TableDefs.Count - 1 'Debug.Print db.TableDefs (f). Name' Next f 'open a recordset On Error Resume Next Set rs = db.OpenRecordset (strSQL)' Set rs = db.OpenRecordset ( "SELECT * FROM [SheetName $]") 'Set rs = db.OpenRecordset ("SELECT * FROM [SheetName $]" & _ "KDE [názov poľa] LIKE 'A*'") 'Set rs = db.OpenRecordset ("SELECT*FROM [SheetName $]" & _ "WHERE [Field Name] LIKE' A*'ORDER BY [Field Name]" ) On Error GoTo 0 If rs is nothing Then MsgBox "Cannot open the file!", VbExclamation, ThisWorkbook.Name db.Close Set db = Nothing Exit Sub End If RS2WS rs, TargetCell rs.Close Set rs = Nothing db. Close Set db = Nothing End Sub Sub RS2WS (rs As DAO.Recordset, TargetCell As Range) Dim f As Integer, r As Long, c As Long if rs is nothing then Exit Sub if TargetCell is nothing then Exit Sub with Application .Calculation = xlCalculationManual .ScreenUpdating = False .StatusBar = "Zápis dát zo sady záznamov …" Koniec s TargetCell.Cells (1, 1) r = .Row c = .Column Koniec s TargetCell.Parent .Range (.Cells (r, c ), .Cells (.Rows.Count, c + rs.Fields.Count - 1)). Clear the 'clear existing contents' write headers column For f = 0 To rs.Fields.Count - 1 On Error Resume Next .Cells ( r, c + f). Vzorec = rs.Pole (f). Meno pri chybe Prejsť 0 Ďalej f 'napísať rec ords On Error Resume Next rs.MoveFirst On Error GoTo 0 Do while Not rs.EOF r = r + 1 For f = 0 To rs.Fields.Count - 1 On Error Resume Next .Cells (r, c + f) .Formula = rs.Fields (f) .Value On Error GoTo 0 Next f rs.MoveNext Loop .Rows (TargetCell.Cells (1, 1) .Row) .Font.Bold = True .Columns ("A: IV"). AutoFit Koniec s aplikáciou .StatusBar = False .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub
Príklady makier predpokladajú, že váš projekt VBA pridal odkaz na knižnicu objektov DAO.
Môžete to urobiť z VBE tak, že vyberiete ponuku Nástroje, Referencie a vyberiete Objektovú knižnicu Microsoft DAO x.xx.
