Piekļuve VBA - Excel importēšana / eksportēšana - vaicājums, pārskats, tabula un veidlapas

Šī apmācība aptvers veidus, kā importēt datus no Excel Access tabulā, un veidus, kā eksportēt Access objektus (vaicājumus, pārskatus, tabulas vai veidlapas) uz Excel.

Importējiet Excel failu piekļuvei

Lai importētu Excel failu programmā Access, izmantojiet acImport iespēja no DoCmd.TransferSheetsheet :

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

Vai arī jūs varat izmantot DoCmd.TransferText lai importētu CSV failu:

DoCmd.TransferText acLinkDelim, "Table1", "C: \ Temp \ Book1.xlsx", True

Importējiet Excel, lai piekļūtu funkcijai

Šo funkciju var izmantot, lai importētu Excel failu vai CSV failu piekļuves tabulā:

Publiskā funkcija ImportFile (faila nosaukums kā virkne, HasFieldNames kā Būla, Tabulas nosaukums kā virkne) Kā Būla 'Piemērs lietojumam: zvaniet ImportFile ("Atlasīt Excel failu", "Excel faili", "*.xlsx", "C: \", True , True, "ExcelImportTest", True, True, false, True) Par kļūdu GoTo err_handler If (Pa labi (faila nosaukums, 3) = "xls") Vai ((pa labi (faila nosaukums, 4) = "xlsx")) Tad DoCmd. TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, TableName, Filename, blnHasFieldNames End If If (Right (Filename, 3) = "csv") Tad DoCmd.TransferText acLinkDelim,, TableName, Filename, True End If Exit_Thing: 'Clean up' Excel tabula jau pastāv… un izdzēsiet to, ja tā ir. Skaitlis = 3073) Un errCount <3 Tad errCount = errCount + 1 ElseIf Err.Number = 3127 Tad MsgBox "Lauki visās cilnēs ir vienādi. Lūdzu, pārliecinieties, vai katra lapa ir precīzi kolonnu nosaukumi, ja vēlaties importēt vairākus ", vbCritical," MultiSheets not ident "ImportFile = False GoTo Exit_Thing Else MsgBox Err.Number &" - ​​"& Err.Description ImportFile = False GoTo Exit_Thing Resume End If End Function

Funkciju var izsaukt šādi:

Privāta apakšfaila ImportFile_Example () zvans VBA_Access_ImportExport.ImportFile ("C: \ Temp \ Book1.xlsx", True, "Imported_Table_1") beigu apakšdaļa

Piekļūstiet VBA eksportam uz jaunu Excel failu

Lai eksportētu Access objektu uz jaunu Excel failu, izmantojiet DoCmd.OutputTo metode vai DoCmd.TransferSpreadsheet metode:

Eksportēt vaicājumu uz Excel

Šī VBA koda rinda eksportēs vaicājumu uz Excel, izmantojot DoCmd.OutputTo:

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

Vai arī varat izmantot DoCmd.TransferSpreadsheet metodi:

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

Piezīme: Šis kods tiek eksportēts uz XLSX formātu. Tā vietā varat atjaunināt argumentus, lai tos eksportētu uz CSV vai XLS faila formātu (piem. acFormatXLSX uz acFormatXLS).

Eksportēt pārskatu uz Excel

Šī koda rinda eksportēs atskaiti uz Excel, izmantojot DoCmd.OutputTo:

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

Vai arī varat izmantot DoCmd.TransferSpreadsheet metodi:

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

Eksportēt tabulu uz Excel

Šī koda rinda eksportēs tabulu uz Excel, izmantojot DoCmd.OutputTo:

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

Vai arī varat izmantot DoCmd.TransferSpreadsheet metodi:

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

Eksportēt veidlapu uz Excel

Šī koda rinda eksportēs veidlapu uz Excel, izmantojot DoCmd.OutputTo:

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

Vai arī varat izmantot DoCmd.TransferSpreadsheet metodi:

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

Eksportēt uz Excel funkcijām

Šīs vienas rindas komandas lieliski darbojas, lai eksportētu uz jaunu Excel failu. Tomēr tās nevarēs eksportēt uz esošu darbgrāmatu. Zemāk esošajā sadaļā mēs iepazīstinām ar funkcijām, kas ļauj eksportu pievienot esošam Excel failam.

Zemāk mēs esam iekļāvuši dažas papildu funkcijas eksportēšanai uz jauniem Excel failiem, tostarp kļūdu apstrādi un daudz ko citu.

Eksportēt uz esošo Excel failu

Iepriekš minētie koda piemēri lieliski palīdz eksportēt Access objektus uz jaunu Excel failu. Tomēr tās nevarēs eksportēt uz esošu darbgrāmatu.

Lai eksportētu Access objektus uz esošu Excel darbgrāmatu, esam izveidojuši šādu funkciju:

Publiskā funkcija AppendToExcel (strObjectType kā virkne, strObjectName kā virkne, strSheetName kā virkne, strFileName kā virkne) Dim rst kā DAO.Recordset Dim ApXL kā Excel.Application Dim xlWBk Kā Excel.Darbgrāmatas izmērs xlWSh Kā Excel.Worksger Int. 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) Lieta "Form" Set rst = Forms (strObjectName) .RecordsetClone Case "Report" Set rst = CurrentDb.OpenRecordset (Reports (strObjectName) .RecordSource, dbOpenDynaset, dbSeeChanges) Beigas Atlasiet Ja rst.RecordCount = 0 Tad Msg bexed " . ", vbInformation, GetDBTitle Else On Error Resume Next Set Iestatiet xlWBk = ApXL.Darbbooks.Open (strFil eName) Iestatiet xlWSh = xlWBk.Sheets.Add xlWSh.Name = Pa kreisi (strSheetName, 31) xlWSh.Range ("A1"). Atlasiet Do Do intCount = rst.fields.Count ApXL.ActiveCell = rst.fields (intCount). Nosaukums ApXL.ActiveCell.Offset (0, 1). Atlasiet intCount = intCount + 1 Loop rst.MoveFirst xlWSh.Range ("A2"). CopyFromRecordset rst Ar ApXL .Range ("A1"). Atlasiet .Range (.Selection, .Selection.End (xlToRight)). Atlasiet .Selection.Interior.Pattern = xlSolid .Selection.Interior.PatternColorIndex = xlAutomatic .Selection.Interior.TintAndShade = -0.25 .Selection.Interior.PatternTintAndSelection xlNone .Selection.AutoFilter .Cells.EntireColumn.AutoFit .Cells.EntireRow.AutoFit .Range ("B2"). Atlasiet .ActiveWindow.FreezePanes = True .ActiveSheet.Cells.Select .ActiveSheet.Cells.WrapText = FalseCella. .EntireColumn.AutoFit xlWSh.Range ("A1"). Atlasiet .Visible = True End ar 'xlWB.Close True' Set xlWB = Nekas 'ApXL.Quit' Set ApXL = Nekas nebeidzas, ja funkcija beidzas

Jūs varat izmantot šo funkciju:

Private Sub AppendToExcel_Example () Zvans VBA_Access_ImportExport.ExportToExcel ("Tabula", "Tabula1", "VBASheet", "C: \ Temp \ Test.xlsx") Beigu apakšnodaļa

Ņemiet vērā, ka jums tiek lūgts definēt:

• Ko izvadīt? Tabula, pārskats, vaicājums vai veidlapa
• Objekta nosaukums
• Izejas lapas nosaukums
• Izejas faila ceļš un nosaukums.

Eksportējiet SQL vaicājumu uz Excel

Tā vietā varat eksportēt SQL vaicājumu uz Excel, izmantojot līdzīgu funkciju:

Publiskā funkcija AppendToExcelSQLStatemet (strsql kā virkne, strSheetName kā virkne, strFileName kā virkne) Dim strQueryName kā virkne Dim ApXL kā Excel. Lietojumprogrammas izmērs xlWBk kā Excel. Darbgrāmatas izmērs xlWSh kā Excel. xlBottom As Long = -4107 Const xlVAlignCenter = -4108 Const xlContinuous As Long = 1 Dim qdf Kā DAO.QueryDef Dim rst Kā DAO.Recordset strQueryName = "tmpQueryToExportToExcel" Ja ObjectExists ("QueryNe.Quue Strue" Beigt, ja iestatīta qdf = CurrentDb.CreateQueryDef (strQueryName, strsql) Iestatīt rst = CurrentDb.OpenRecordset (strQueryName, dbOpenDynaset) Ja rst.RecordCount = 0 Tad MsgBox "Nav eksportējamu ierakstu.", VbInformation, Get On Error ApXL = GetObject (, "Excel.Application") Ja Err.Number 0 Pēc tam iestatiet ApXL = CreateObject ("Excel.Application") Beigu Ja Err.Clear ApXL.Visible = False Set xlWBk = ApXL.Workbooks.Open (strFileName) Set xlWSh = xlWBk. lapa s.Add xlWSh.Name = Pa kreisi (strSheetName, 31) xlWSh.Range ("A1"). Atlasiet Do Do intCount = rst.fields.Count ApXL.ActiveCell = rst.fields (intCount) .Nosaukums ApXL.ActiveCell.Offset ( 0, 1) .Izvēlieties intCount = intCount + 1 Loop rst.MoveFirst xlWSh.Range ("A2"). CopyFromRecordset rst Ar ApXL .Range ("A1"). Atlasiet .Range (.Selection, .Selection.End (xlToRight) ) .Izvēlieties .Selection.Interior.Pattern = xlSolid .Selection.Interior.PatternColorIndex = xlAutomatic .Selection.Interior.TintAndShade = -0,25 .Selection.Interior.PatternTintAndShade = 0 .Selection.Belter.Line.Line .EntireColumn.AutoFit .Cells. ("A1"). Atlasiet .Visible = True End ar 'xlWB.Close True' Set xlWB = Nothing 'ApXL.Quit' Set

Sauc šādi:

Private Sub AppendToExcelSQLStatemet_Example () Zvanīt VBA_Access_ImportExport.ExportToExcel ("SELECT * FROM Table1", "VBASheet", "C: \ Temp \ Test.xlsx") Beigu apakšdaļa

Kur jums tiek lūgts ievadīt:

• SQL vaicājums
• Izejas lapas nosaukums
• Izejas faila ceļš un nosaukums.

Funkcija eksportēšanai uz jaunu Excel failu

Šīs funkcijas ļauj eksportēt Access objektus uz jaunu Excel darbgrāmatu. Tie varētu šķist noderīgāki par vienkāršām atsevišķām rindiņām dokumenta augšpusē.

Publiskā funkcija ExportToExcel (strObjectType kā virkne, strObjectName kā virkne, neobligāta strSheetName kā virkne, pēc izvēles strFileName kā virkne) Dim rst kā DAO.Recordset Dim ApXL kā objekts Dim xlWBk kā objekts Dim xlWSh kā objekts Dim intCl = Integer Const 4161 Const xlCenter As Long = -4108 Const xlBottom As Long = -4107 Const xlContinuous As Long = 1 Kļūda GoTo ExportToExcel_Err DoCmd.Hourglass True Select Case strObjectType Case "Table", "Query" Set rst = CurrentDb.OpenRecordName (strObp , dbSeeChanges) Lieta "Veidlapa" Set rst = Forms (strObjectName) .RecordsetClone Case "Report" Set ieraksti, kas jāeksportē. ", vbInformation, GetDBTitle DoCmd.Hourglass False Else On Error Resume Next Set ApXL = GetObject (," Excel.Application ") Ja Err.Number 0, tad Set Kļūda. Notīrīt kļūdu GoTo ExportToExcel_Err Set xlWBk = ApXL.Workbooks.Add ApXL.Visible = False Set xlWSh = xlWBk.Worksheets ("Sheet1") Ja Len (strSheetName)> 0 Tad xlWSh.Name = Left (strSheet xN . Diapazons ("A1"). Atlasiet Darīt, līdz intCount = rst.fields.Count ApXL.ActiveCell = pirmie lauki (intCount) .Nosaukums ApXL.ActiveCell.Offset (0, 1). Atlasiet intCount = intCount + 1 Loop rst. MoveFirst xlWSh.Range ("A2"). CopyFromRecordset pirmais ar ApXL .Range ("A1"). Atlasiet .Range (.Selection, .Selection.End (xlToRight)). Atlasiet .Selection.Interior.Pattern = xlSolid .Selection. Interior.PatternColorIndex = xlAutomatic .Selection.Interior.TintAndShade = -0,25 .Selection.Interior.PatternTintAndShade = 0 .Selection.Borders.LineStyle = xlNone .Selection.AutoFilter .Cells.EntireColow.AnteroFutline. B2 "). Atlasiet .ActiveWindow.FreezePanes = True .ActiveSheet.Cells.Select .ActiveSheet.Cells.WrapText = False .ActiveSheet.Cells.EntireColumn.AutoFit xlWSh.Range (" A1 "). Atlasiet .Visible = True End Wi otrais mēģinājums: ja FileExists (strFileName) Tad nogalināt strFileName End If if strFileName "" Tad xlWBk.SaveAs strFileName, FileFormat: = 56 End If rst.Close Set rst = Nothing DoCmd.Hourglass False End If ExportToExcel_Exit Exxit ExportToExcel_Err: DoCmd.SetWarnings True MsgBox Err.Description, vbExclamation, Err.Number DoCmd.Hourglass False Resume ExportToExcel_Exit Beigu funkcija

Funkciju var saukt šādi:

Privāta apakšeksporta izveides piemērs () Zvans VBA_Access_ImportExport.ExportToExcel ("Tabula", "1. tabula", "VBASheet") Beigu apakšdaļa