Prístup k VBA - Import / Export Excel - Dotaz, Zostava, Tabuľka a Formuláre

Tento tutoriál sa bude zaoberať spôsobmi importu údajov z Excelu do prístupovej tabuľky a spôsobmi exportu objektov Accessu (dotazov, zostáv, tabuliek alebo formulárov) do Excelu.

Importujte súbor programu Excel do programu Access

Ak chcete importovať súbor programu Excel do programu Access, použite príponu dovážať možnosť Tabuľka DoCmd.TransferSpreadsheet :

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Table1", "C: \ Temp \ Book1.xlsx", True

Alebo môžete použiť DoCmd.TransferText import súboru CSV:

DoCmd.TransferText acLinkDelim,, "Tabuľka1", "C: \ Temp \ Book1.xlsx", True

Importujte funkciu Excel do programu Access

Túto funkciu je možné použiť na importovanie súboru Excel alebo CSV do prístupovej tabuľky:

Verejná funkcia ImportFile (názov súboru ako reťazec, HasFieldNames ako logická hodnota, názov tabuľky ako reťazec) Ako booleovské príklad použitia: zavolajte ImportFile („Vyberte súbor programu Excel“, „súbory programu Excel“, „*.xlsx“, „C: \“, True , Pravda, „ExcelImportTest“, True, True, false, True) Pri chybe GoTo err_handler If (Right (Názov súboru, 3) = "xls") Alebo ((Vpravo (Názov súboru, 4) = "xlsx")) Potom DoCmd. TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, TableName, Filename, blnHasFieldNames End If If (Right (Filename, 3) = "csv") Then DoCmd.TransferText acLinkDelim,, TableName, Filename, True End If Exit_Thing: in Clean up Tabuľka programu Excel už existuje … a ak je to možné, odstráňte ju. Ak ObjectExists ("Table", TableName) = True Then DropTable (TableName) Set colWorksheets = Nothing Exit Function err_handler: If (Err.Number = 3086 Or Err.Number = 3274 Or Err. Number = 3073) And errCount <3 Then errCount = errCount + 1 ElseIf Err.Number = 3127 Then MsgBox "Polia na všetkých kartách sú rovnaké. Uistite sa, že každý list má presné názvy stĺpcov, ak chcete importovať viacnásobné ", vbCritical," viac listov nie je totožné "ImportFile = False GoTo Exit_Thing Else MsgBox Err.Number &" - ​​"& Err.Description ImportFile = False GoTo Exit_Thing Resume End If End Function

Túto funkciu môžete zavolať takto:

Súkromný čiastkový importný súbor_príkladu () Zavolajte VBA_Access_ImportExport.ImportFile ("C: \ Temp \ Book1.xlsx", True, "Imported_Table_1") End Sub

Prístup k exportu VBA do nového súboru Excel

Ak chcete exportovať objekt Accessu do nového súboru programu Excel, použite príponu DoCmd.OutputTo metóda alebo Metóda DoCmd.TransferSpreadsheet:

Exportujte dotaz do Excelu

Tento riadok kódu VBA exportuje dotaz do programu Excel pomocou DoCmd.OutputTo:

DoCmd.OutputTo acOutputQuery, "Query1", acFormatXLSX, "c: \ temp \ ExportedQuery.xls"

Alebo môžete namiesto toho použiť metódu DoCmd.TransferSpreadsheet:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Query1", "c: \ temp \ ExportedQuery.xls", True

Poznámka: Tento kód sa exportuje do formátu XLSX. Namiesto toho môžete aktualizovať argumenty a namiesto toho ich exportovať do formátu CSV alebo XLS (napr. acFormatXLSX do acFormatXLS).

Export správy do Excelu

Tento riadok kódu exportuje zostavu do programu Excel pomocou DoCmd.OutputTo:

DoCmd.OutputTo acOutputReport, "Report1", acFormatXLSX, "c: \ temp \ ExportedReport.xls"

Alebo môžete namiesto toho použiť metódu DoCmd.TransferSpreadsheet:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Report1", "c: \ temp \ ExportedReport.xls", True

Exportovať tabuľku do Excelu

Tento riadok kódu exportuje tabuľku do programu Excel pomocou DoCmd.OutputTo:

DoCmd.OutputTo acOutputTable, "Table1", acFormatXLSX, "c: \ temp \ ExportedTable.xls"

Alebo môžete namiesto toho použiť metódu DoCmd.TransferSpreadsheet:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Table1", "c: \ temp \ ExportedTable.xls", True

Exportujte formulár do Excelu

Tento riadok kódu exportuje formulár do programu Excel pomocou DoCmd.OutputTo:

DoCmd.OutputTo acOutputForm, "Form1", acFormatXLSX, "c: \ temp \ ExportedForm.xls"

Alebo môžete namiesto toho použiť metódu DoCmd.TransferSpreadsheet:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Form1", "c: \ temp \ ExportedForm.xls", True

Export do funkcií programu Excel

Tieto príkazy v jednom riadku fungujú skvele na export do nového súboru programu Excel. Nebudú však môcť exportovať do existujúceho zošita. V nižšie uvedenej časti uvádzame funkcie, ktoré vám umožňujú pripojiť export k existujúcemu súboru programu Excel.

Ďalej sme zahrnuli niektoré ďalšie funkcie na export do nových súborov programu Excel vrátane spracovania chýb a ďalších.

Exportovať do existujúceho súboru programu Excel

Vyššie uvedené príklady kódu fungujú dobre na export objektov Accessu do nového súboru programu Excel. Nebudú však môcť exportovať do existujúceho zošita.

Na export objektov Access do existujúceho zošita programu Excel sme vytvorili nasledujúcu funkciu:

Verejná funkcia AppendToExcel (strObjectType As String, strObjectName As String, strSheetName As String, strFileName As String) Dim rst As DAO.Recordset Dim ApXL As Excel.Application Dim xlWBk As Excel.Workbook Dim xlWSh As Excel.Worksheet Dim intCount As Long = -4161 Const xlCenter As Long = -4108 Const xlBottom As Long = -4107 Const xlContinuous As Long = 1 Select Case strObjectType Case "Table", "Query" Set rst = CurrentDb.OpenRecordset (strObjectName, dbOpenDynaset, dbSeeChanges) „Formulár“ Set rst = Forms (strObjectName) .RecordsetClone Case „Report“ Set rst = CurrentDb.OpenRecordset (Reports (strObjectName) .RecordSource, dbOpenDynaset, dbSeeChanges) End Select If rst.RecordCount = 0 Then MsgBox "Žiadne záznamy na export . ", vbInformation, GetDBTitle Jinak pri chybe Pokračovať Ďalej nastaviť ApXL = GetObject (," Excel.Application "), ak Err.Number 0, potom nastaviť ApXL = CreateObject (" Excel.Application ") Koniec Ak Err.Clear ApXL.Visible = False Nastaviť xlWBk = ApXL.Workbooks.Open (strFil eName) Nastaviť xlWSh = xlWBk.Sheets.Add xlWSh.Name = vľavo (strSheetName, 31) xlWSh.Range ("A1"). Vyberte Do Do intCount = rst.fields.Count ApXL.ActiveCell = rst.fields (intCount). Názov ApXL.ActiveCell.Offset (0, 1). Vyberte intCount = intCount + 1 Loop rst.MoveFirst xlWSh.Range ("A2"). CopyFromRecordset prvé s ApXL .Range ("A1"). Vyberte .Range (.Selection, .Selection.End (xlToRight)). Vyberte .Selection.Interior.Pattern = xlSolid .Selection.Interior.PatternColorIndex = xlAutomatic .Selection.Interior.TintAndShade = -0,25 .Selection.Interior.PatternTintAndlection.Barvy = 0. xlNone .Selection.AutoFilter .Cells.EntireColumn.AutoFit .Cells.EntireRow.AutoFit .Range ("B2"). Vyberte .ActiveWindow.FreezePanes = True .ActiveSheet.Cells.Select .ActiveSheet.Cells.WrapText.Celkový. .EntireColumn.AutoFit xlWSh.Range ("A1"). Vyberte .Visible = True End s 'xlWB.Close True' Set xlWB = Nič 'ApXL.Quit' Nastaviť ApXL = Nič Končiť Ak Koniec Funkcia

Túto funkciu môžete použiť takto:

Private Sub AppendToExcel_Example () Volanie VBA_Access_ImportExport.ExportToExcel ("Table", "Table1", "VBASheet", "C: \ Temp \ Test.xlsx") End Sub

Všimnite si, že musíte definovať:

• Čo na výstup? Tabuľka, správa, dotaz alebo formulár
• Názov objektu
• Názov výstupného listu
• Cesta a názov výstupného súboru.

Exportujte dotaz SQL do programu Excel

Namiesto toho môžete dotaz SQL exportovať do Excelu pomocou podobnej funkcie:

Verejná funkcia AppendToExcelSQLStatemet (strsql As String, strSheetName As String, strFileName As String) Dim strQueryName As String Dim ApXL As Excel. Application Dim xlWBk As Excel. Workbook Dim xlWSh As Excel.Worksheet Dim intCount As Integer Const xlCenter xlBottom As Long = -4107 Const xlVAlignCenter = -4108 Const xlContinuous As Long = 1 Dim qdf As DAO.QueryDef Dim first as DAO.Recordset strQueryName = "tmpQueryToExportToExcel" If ObjectExists ("Query") Then End If Set qdf = CurrentDb.CreateQueryDef (strQueryName, strsql) Set rst = CurrentDb.OpenRecordset (strQueryName, dbOpenDynaset) Ak rst.RecordCount = 0, potom MsgBox "Žiadne záznamy na export.", VbInformitease, GetDB ApXL = GetObject (, "Excel.Application"), ak Err.Number 0, potom nastavte ApXL = CreateObject ("Excel.Application") Koniec, ak Err.Clear ApXL.Visible = False Set xlWBk = ApXL.Workbooks.Open (strFileName) Set xlWSh = xlWBk. List s.Add xlWSh.Name = Left (strSheetName, 31) xlWSh.Range ("A1"). Vyberte Do Do intCount = rst.fields.Count ApXL.ActiveCell = rst.fields (intCount) .Name ApXL.ActiveCell.Offset ( 0, 1). Vyberte intCount = intCount + 1 slučku najskôr.MoveFirst xlWSh.Range ("A2"). CopyFromRecordset najskôr s ApXL .Range ("A1"). Vyberte .Range (.Selection, .Selection.End (xlToRight) ) .Vyberte .Selection.Interior.Pattern = xlSolid .Selection.Interior.PatternColorIndex = xlAutomatic .Selection.Interior.TintAndShade = -0,25 .Selection.Interior.PatternTintAndShade = 0 .Selection.Borders.NineStyle .EntireColumn.AutoFit .Cells.EntireRow.AutoFit .Range („B2“). Vyberte .ActiveWindow.FreezePanes = True .ActiveSheet.Cells.Select .ActiveSheet.Cells.WrapText = False .ActiveSheet.Cells.EntireColumn.uto ("A1"). Vyberte .Visible = True End s 'xlWB.Close True' Set xlWB = Nothing 'ApXL.Quit' Set ApXL = Nič sa nekončí, ak je ukončená funkcia

Vola sa takto:

Private Sub AppendToExcelSQLStatemet_Example () Volanie VBA_Access_ImportExport.ExportToExcel ("SELECT * FROM Table1", "VBASheet", "C: \ Temp \ Test.xlsx") End Sub

Kde sa zobrazí výzva na zadanie:

• SQL dotaz
• Názov výstupného listu
• Cesta a názov výstupného súboru.

Funkcia na export do nového súboru programu Excel

Tieto funkcie vám umožňujú exportovať objekty programu Access do nového zošita programu Excel. Možno vám prídu užitočnejšie než jednoduché jednotlivé riadky v hornej časti dokumentu.

Verejná funkcia ExportToExcel (strObjectType ako reťazec, strObjectName ako reťazec, voliteľný strSheetName ako reťazec, voliteľný strFileName ako reťazec) Dim prvý ako DAO.Recordset Dim ApXL ako objekt Dim xlWBk ako objekt Dim xlWSh As Object Dim intCount As Integer Const xlToR 4161 Const xlCenter ako dlhé = -4108 Const xlBottom ako dlhé = -4107 Const xlSpojité ako dlhé = 1 pri chybe GoTo ExportToExcel_Err DoCmd.Hourglass True Select Case strObjectType Case "Table", "Query" Set rst = CurrentDb.OpenRjectD (strO , dbSeeChanges) Sada „Formulár“ rst = Forms (strObjectName) .RecordsetClone Case „Report“ Set rst = CurrentDb.OpenRecordset (Reports (strObjectName) .RecordSource, dbOpenDynaset, dbSeeChanges) End Select If rst.RecordCount = 0 Then Ms záznamy, ktoré sa majú exportovať. ", vbInformation, GetDBTitle DoCmd.Hourglass False Else On Error Resume Next Set ApXL = GetObject (," Excel.Application ") If Err.Number 0 Then Set ApXL = CreateObject (" Excel.Application ") End If Err. Vymazať pri chybe GoTo ExportToExcel_Err Set xlWBk = ApXL.Workbooks.Add ApXL.Visible = False Set xlWSh = xlWBk.Worksheets ("Sheet1") If Len (strSheetName)> 0 Then xlWSh.Name = Left (strSheetName, 31) .Range ("A1"). Vyberte Do Do IntCount = prvé pole.Count ApXL.ActiveCell = prvé pole (intCount) .Názov ApXL.ActiveCell.Offset (0, 1). Vyberte intCount = intCount + 1 slučku prvý. MoveFirst xlWSh.Range ("A2"). CopyFromRecordset prvé s ApXL .Range ("A1"). Vyberte .Range (.Selection, .Selection.End (xlToRight)). Vyberte .Selection.Interior.Pattern = xlSolid .Selection. Interior.PatternColorIndex = xlAutomatic .Selection.Interior.TintAndShade = -0,25 .Selection.Interior.PatternTintAndShade = 0 .Selection.Borders.LineStyle = xlNone .Selection.AutoFilter .Cells.EntireColumn.AutoFit. B2 "). Vyberte .ActiveWindow.FreezePanes = True .ActiveSheet.Cells.Select .ActiveSheet.Cells.WrapText = False .ActiveSheet.Cells.EntireColumn.AutoFit xlWSh.Range (" A1 "). Vyberte .Visible = True End Wi skúste to znova: If FileExists (strFileName) Then Kill strFileName End If If strFileName "" Then xlWBk.SaveAs strFileName, FileFormat: = 56 End If rst.Close Set rst = Nothing DoCmd.Hourglass False End If ExportToExcel_Exit: DoCm ExportToExcel_Err: DoCmd.SetWarnings True MsgBox Err.Description, vbExclamation, Err.Number DoCmd.Hourglass False Resume ExportToExcel_Exit End Function

Túto funkciu je možné nazvať takto:

Private Sub ExportToExcel_Example () Zavolajte VBA_Access_ImportExport.ExportToExcel ("Tabuľka", "Tabuľka1", "VBASheet") Koniec Sub