Dostop do VBA - Uvoz / izvoz Excel - Poizvedbe, poročila, tabele in obrazci

Ta vadnica bo zajemala načine za uvoz podatkov iz Excela v Accessovo tabelo in načine za izvoz Accessovih predmetov (poizvedbe, poročila, tabele ali obrazci) v Excel.

Uvozite Excelovo datoteko v Access

Za uvoz datoteke Excel v Access uporabite datoteko acImport možnost DoCmd.TransferSpreadsheet :

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

Ali pa lahko uporabite DoCmd.TransferText za uvoz datoteke CSV:

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

Uvozite Excel za dostop do funkcije

To funkcijo lahko uporabite za uvoz datoteke Excel ali datoteke CSV v tabelo dostopa:

Javna funkcija ImportFile (Ime datoteke kot niz, ime polja kot logično polje, ime tabele kot niz) Kot logična "Primer uporabe: pokličite ImportFile (" Izberite datoteko Excel "," Datoteke Excel ","*.xlsx "," C: \ ", True , True, "ExcelImportTest", True, True, false, True) On Error GoTo err_handler If (Right (Ime datoteke, 3) = "xls") Ali ((Desno (Ime datoteke, 4) = "xlsx")) Nato naredite DoCmd. TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, TableName, Filename, blnHasFieldNames End If If (Desno (Ime datoteke, 3) = "csv") Nato DoCmd.TransferText acLinkDelim,, TableName, File name, True End if Exit_Thing: " Tabela Excel že obstaja … in jo odstranite, če je tako. Če ObjectExists ("Tabela", Ime tabele) = True Potem DropTable (Ime tabele) Nastavite colWorksheets = Nič Izhod Funkcija err_handler: Če (Err.Št. = 3086 Ali Err.Number = 3274 Ali Err. Number = 3073) In errCount <3 Potem errCount = errCount + 1 ElseIf Err.Number = 3127 Potem MsgBox "Polja na vseh zavihkih so enaka. Prepričajte se, da je vsak list ima natančna imena stolpcev, če želite uvoziti več elementov ", vbCritical," Več listov ni enako "ImportFile = False GoTo Exit_Thing Else MsgBox Err.Number &" - ​​"& Err.Description ImportFile = False GoTo Exit_Thing Nadaljuj Končaj, če funkcija preneha

Funkcijo lahko pokličete tako:

Zasebna podvloga ImportFile_Example () Pokličite VBA_Access_ImportExport.ImportFile ("C: \ Temp \ Book1.xlsx", True, "Imported_Table_1") End Sub

Dostop do VBA Export v novo datoteko Excel

Če želite izvoziti objekt Access v novo datoteko Excel, uporabite datoteko DoCmd.OutputTo metodo ali Metoda DoCmd.TransferSpreadsheet:

Izvozi poizvedbo v Excel

Ta vrstica kode VBA bo izvozila poizvedbo v Excel z uporabo DoCmd.OutputTo:

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

Lahko pa uporabite tudi metodo DoCmd.TransferSpreadsheet:

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

Opomba: Ta koda se izvozi v format XLSX. Namesto tega lahko posodobite argumente za izvoz v obliko datoteke CSV ali XLS (npr. acFormatXLSX do acFormatXLS).

Izvozi poročilo v Excel

Ta vrstica kode bo izvozila poročilo v Excel z uporabo DoCmd.OutputTo:

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

Lahko pa uporabite tudi metodo DoCmd.TransferSpreadsheet:

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

Izvozi tabelo v Excel

Ta vrstica kode bo izvozila tabelo v Excel z uporabo DoCmd.OutputTo:

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

Lahko pa uporabite tudi metodo DoCmd.TransferSpreadsheet:

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

Izvozi obrazec v Excel

Ta vrstica kode bo izvozila obrazec v Excel z uporabo DoCmd.OutputTo:

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

Lahko pa uporabite tudi metodo DoCmd.TransferSpreadsheet:

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

Izvozi v Excelove funkcije

Ti ukazi v eni vrstici odlično delujejo pri izvozu v novo datoteko Excel. Vendar pa ne bodo mogli izvoziti v obstoječi delovni zvezek. V spodnjem razdelku predstavljamo funkcije, ki vam omogočajo, da izvoz dodate v obstoječo datoteko Excel.

Spodaj smo vključili nekaj dodatnih funkcij za izvoz v nove datoteke Excel, vključno z obravnavo napak in drugimi.

Izvozi v obstoječo datoteko Excel

Zgornji primeri kode odlično delujejo pri izvozu predmetov Access v novo datoteko Excel. Vendar pa ne bodo mogli izvoziti v obstoječi delovni zvezek.

Za izvoz predmetov Access v obstoječi Excelov delovni zvezek smo ustvarili naslednjo funkcijo:

Javna funkcija 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 xlWShT As Excel As Long = -4161 Const xlCenter As Long = -4108 Const xlBottom As Long = -4107 Const xlContinuous As Long = 1 Izberite primer strObjectType Primer "Tabela", "Poizvedba" Set rst = CurrentDb.OpenRecordset (strObjectName, dbOpenDynaCeta, dbOpenDynaCee, db "Form" Set rst = Forms (strObjectName) .RecordsetClone Case "Report" Set rst = CurrentDb.OpenRecordset (Reports (strObjectName). " Nastavite xlWBk = ApXL.Workbooks.Open (strFil eName) Nastavite xlWSh = xlWBk.Sheets.Add xlWSh.Name = Left (strSheetName, 31) xlWSh.Range ("A1"). Izberite Do Do intCount = rst.fields.Count ApXL.ActiveCell = rst.fields (intCount) Ime ApXL.ActiveCell.Offset (0, 1) .Izberite intCount = intCount + 1 zanka rst.MoveFirst xlWSh.Range ("A2"). CopyFromRecordset rst z ApXL .Range ("A1"). Izberite .Range (.Selection, .Selection.End (xlToRight)). Izberite .Selection.Interior.Pattern = xlSolid .Selection.Interior.PatternColorIndex = xlAutomatic .Selection.Interior. xlNone .Selection.AutoFilter .Cells.EntireColumn.AutoFit .Cells.EntireRow.AutoFit .Range ("B2"). Izberite .ActiveWindow.FreezePanes = True .ActiveSheet.Cells. Izberite .ActiveSheet.Cells. .EntireColumn.AutoFit xlWSh.Range ("A1"). Izberite .Visible = True End z "xlWB.Close True" Nastavi xlWB = Nič

Funkcijo lahko uporabite tako:

Zasebni dodatek AppendToExcel_Example () Pokličite VBA_Access_ImportExport.ExportToExcel ("Tabela", "Tabela1", "VBASheet", "C: \ Temp \ Test.xlsx") Končna pod

Upoštevajte, da morate določiti:

• Kaj natisniti? Tabela, poročilo, poizvedba ali obrazec
• Ime predmeta
• Ime izhodnega lista
• Pot in ime izhodne datoteke.

Izvozi poizvedbo SQL v Excel

Namesto tega lahko izvozite poizvedbo SQL v Excel s podobno funkcijo:

Javna funkcija AppendToExcelSQLStatemet (strsql kot niz, strSheetName kot niz, strFileName kot niz) Zatemni strQueryName kot niz Dim ApXL kot Excel.Aplikacija Dim xlWBk kot Excel.Dejavnik Dim xlWSh kot Excel.Worksheet Dim xlBottom dokler = -4107 Const xlVAlignCenter = -4108 Const xlContinuous dokler = 1 dim qdf Kot DAO.QueryDef dim tirna vozila As DAO.Recordset strQueryName = "tmpQueryToExportToExcel" Če ObjectExists ( "poizvedba", strQueryName), potem CurrentDb.QueryDefs.Delete strQueryName Konec Če nastavite qdf = CurrentDb.CreateQueryDef (strQueryName, strsql) Nastavite rst = CurrentDb.OpenRecordset (strQueryName, dbOpenDynaset) Če je rst.RecordCount = 0 Potem MsgBox "Ni zapisov za izvoz." ApXL = GetObject (, "Excel.Application") Če je Err.Number 0 Nato nastavite ApXL = CreateObject ("Excel.Application") Konec Če je Err.Clear ApXL.Visible = False Set xlWBk = ApXL.Workbooks.Open (strFileName) Set xlWSh = xlWBk.Sheet s.Add xlWSh.Name = Levo (strSheetName, 31) xlWSh.Range ("A1"). Izberite Do Do intCount = rst.fields.Count ApXL.ActiveCell = rst.fields (intCount). Ime ApXL.ActiveCell.Offset ( 0, 1). Izberite intCount = intCount + 1 zanka rst.MoveFirst xlWSh.Range ("A2"). CopyFromRecordset rst z ApXL .Range ("A1"). Izberite .Range (.Selection, .Selection.End (xlToRight) ) .Izberi .Izbira. Notranjost.Vzorec = xlTrdno .Izbor. Notranjost. Vzorec barveIndeks = xlAvtomatski .Izbor. Notranjost.TintAndShade = -0.25 .Izbor.Interij.VzorecTintAndShade = 0 .SelectionSele. .EntireColumn.AutoFit .Cells.EntireRow.AutoFit .Range ("B2"). Izberite .ActiveWindow.FreezePanes = True .ActiveSheet.Cells. Izberite .ActiveSheet.Cells.WrapText = False. ("A1"). Izberite .Visible = True End z "xlWB. Zapri True" Set xlWB = Nič "ApXL.Quit" Nastavi ApXL = Nič se ne konča, če funkcija End

Tako imenovano:

Zasebni dodatek AppendToExcelSQLStatemet_Example () Pokličite VBA_Access_ImportExport.ExportToExcel ("SELECT * FROM Table1", "VBASheet", "C: \ Temp \ Test.xlsx") End Sub

Kjer morate vnesti:

• Poizvedba SQL
• Ime izhodnega lista
• Pot in ime izhodne datoteke.

Funkcija za izvoz v novo datoteko Excel

Te funkcije vam omogočajo izvoz objektov Access v nov Excelov delovni zvezek. Morda se vam zdijo bolj uporabne kot enostavne vrstice na vrhu dokumenta.

Javna funkcija ExportToExcel (strObjectType As String, strObjectName As String, Izbirno strSheetName As String, Neobvezno strFileName As String) Dim rst As DAO.Recordset Dim ApXL As Dim Object xlWBk As Object Dim xlWSh As 4161 Const xlCenter As Long = -4108 Const xlBottom As Long = -4107 Const xlContinuous As Long = 1 On Error GoTo ExportToExcel_Err DoCmd.Hourglass True Select Case strObjectType Primer "Tabela", "Poizvedba" Nastavi rst = CurrentDb.OpenDeset , dbSeeChanges) Primer "Form" Set rst = Forms (strObjectName) .RecordsetClone Case "Report" Set rst = CurrentDb.OpenRecordset (Reports (strObjectName) .RecordSource, dbOpenDynaset, dbSeeChanges) zapisov, ki jih je treba izvoziti. ", vbInformation, GetDBTitle DoCmd.Hourglass False Else On Error Nadaljuj Naslednji Niz Nastavi ApXL = GetObject (," Excel.Application ") Če je Err.Number 0 Potem nastavite ApXL = CreateObject (" Excel.Application ") Konec Če Err. Počisti pri napaki Pojdi na ExportToExcel_Err Set xlWBk = ApXL.Workbooks.Add ApXL.Visible = False Set xlWSh = xlWBk.Worksheets ("Sheet1") If Len (strSheetName)> 0 then xlWSh.NemeSeme .Range ("A1"). Izberite Do Do intCount = rst.fields.Count ApXL.ActiveCell = rst.fields (intCount). Ime ApXL.ActiveCell.Offset (0, 1). Izberite intCount = intCount + 1 Loop rst. MoveFirst xlWSh.Range ("A2"). CopyFromRecordset rst With ApXL .Range ("A1"). Select .Range (.Selection, .Selection.End (xlToRight)). Select .Selection.Interior.Pattern = xlSolid .Selection. Interior.PatternColorIndex = xlAutomatic .Selection.Interior.TintAndShade = -0,25 .Selection.Interior.PatternTintAndShade = 0 .Selection.Borders.LineStyle = xlNone .Selection.AutoFilter. B2 "). Izberite .ActiveWindow.FreezePanes = True .ActiveSheet.Cells. Izberite .ActiveSheet.Cells.WrapText = False. Ponovni poskus: Če FileExists (strFileName) Nato ubijte strFileName Konec Če Če strFileName "" Potem xlWBk.SaveAs strFileName, FileFormat: = 56 Konec Če je rst.Zapri Zapri rst = Nič DoCmd.Hourglass False End Če ExportToExssCitx False ExportToExcelCexExcit False ExitToExcelCexExcit False End ExportToExcel_Err: DoCmd.SetWarnings True MsgBox Err.Description, vbExclamation, Err.Number DoCmd.Hourglass False Resume ExportToExcel_Exit End Funkcija

Funkcijo lahko pokličete tako:

Zasebna podreza ExportToExcel_Example () Pokliči VBA_Access_ImportExport.ExportToExcel ("Tabela", "Tabela1", "VBASheet") Končna pod