Bu öğretici, Excel'den bir Access Tablosuna veri aktarmanın yollarını ve Access nesnelerini (Sorgular, Raporlar, Tablolar veya Formlar) Excel'e aktarmanın yollarını kapsayacaktır.
Excel Dosyasını Access'e Aktarın
Bir Excel dosyasını Access'e aktarmak için, acImport seçeneği DoCmd.TransferE-tablo :
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Table1", "C:\Temp\Book1.xlsx", True
Veya kullanabilirsiniz DoCmd.TransferText bir CSV dosyasını içe aktarmak için:
DoCmd.TransferText acLinkDelim, , "Table1", "C:\Temp\Book1.xlsx", True
Excel'i İşleve Erişmek için İçe Aktarın
Bu işlev, bir Excel dosyasını veya CSV dosyasını bir Access Tablosuna aktarmak için kullanılabilir:
Public Function ImportFile(Filename As String, HasFieldNames As Boolean, TableName As String) As Boolean ' Örnek kullanım: ImportFile'ı çağırın ("Bir Excel Dosyası Seçin", "Excel Dosyaları", "*.xlsx", "C:\" , True ,True, "ExcelImportTest", True, True,false,True) Hatada GoTo err_handler If (Right(Filename, 3) = "xls") Veya ((Right(Filename, 4) = "xlsx")) Sonra DoCmd. TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, TableName, Filename, blnHasFieldNames End If (Right(Filename, 3) = "csv") Sonra DoCmd.TransferText acLinkDelim, , TableName, Filename, True End If Exit_Thing: 'Clean up link' Excel tablosu zaten var… ve varsa silin ObjectExists("Table", TableName) = True Then DropTable (TableName) Set colWorksheets = Nothing Exit Function err_handler: If (Err.Number = 3086 Veya Err.Number = 3274 Veya Err. Number = 3073) Ve errCount < 3 Sonra errCount = errCount + 1 ElseIf Err.Number = 3127 Sonra MsgBox "Bütün sekmelerdeki alanlar aynıdır. Lütfen her sayfanın birden fazla içe aktarmak istiyorsanız tam sütun adlarına sahip", vbCritical, "Çoklu Sayfalar aynı değil" ImportFile = False GoTo Exit_Thing Else MsgBox Err.Number & " - " & Err.Description ImportFile = False GoTo Exit_Thing Devam Et End If End Function
Fonksiyonu şu şekilde çağırabilirsiniz:
Private Sub ImportFile_Example() Çağrı VBA_Access_ImportExport.ImportFile("C:\Temp\Book1.xlsx", True, "Imported_Table_1") End Sub
Yeni Excel Dosyasına VBA Dışa Aktarma Erişimi
Bir Access nesnesini yeni bir Excel dosyasına aktarmak için DoCmd.OutputTo yöntem veya DoCmd.TransferSpreadsheet yöntemi:
Sorguyu Excel'e Aktar
Bu VBA kodu satırı, DoCmd.OutputTo kullanarak bir Sorguyu Excel'e aktaracaktır:
DoCmd.OutputTo acOutputQuery, "Sorgu1", acFormatXLSX, "c:\temp\ExportedQuery.xls"
Veya bunun yerine DoCmd.TransferSpreadsheet yöntemini kullanabilirsiniz:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Sorgu1", "c:\temp\ExportedQuery.xls", True
Not: Bu kod XLSX formatına aktarılır. Bunun yerine argümanları CSV veya XLS dosya formatına dışa aktaracak şekilde güncelleyebilirsiniz (ör. acFormatXLSX ile acFormatXLS).
Raporu Excel'e Aktar
Bu kod satırı, DoCmd.OutputTo kullanarak bir Raporu Excel'e aktaracaktır:
DoCmd.OutputTo acOutputReport, "Rapor1", acFormatXLSX, "c:\temp\ExportedReport.xls"
Veya bunun yerine DoCmd.TransferSpreadsheet yöntemini kullanabilirsiniz:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Rapor1", "c:\temp\ExportedReport.xls", True
Tabloyu Excel'e Aktar
Bu kod satırı, DoCmd.OutputTo kullanarak bir Tabloyu Excel'e aktaracaktır:
DoCmd.OutputTo acOutputTable, "Table1", acFormatXLSX, "c:\temp\ExportedTable.xls"
Veya bunun yerine DoCmd.TransferSpreadsheet yöntemini kullanabilirsiniz:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Table1", "c:\temp\ExportedTable.xls", True
Formu Excel'e Aktar
Bu kod satırı, bir Formu DoCmd.OutputTo kullanarak Excel'e aktaracaktır:
DoCmd.OutputTo acOutputForm, "Form1", acFormatXLSX, "c:\temp\ExportedForm.xls"
Veya bunun yerine DoCmd.TransferSpreadsheet yöntemini kullanabilirsiniz:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Form1", "c:\temp\ExportedForm.xls", True
Excel İşlevlerine Aktar
Bu tek satırlık komutlar, yeni bir Excel dosyasına dışa aktarmak için harika çalışır. Ancak, mevcut bir çalışma kitabına dışa aktaramazlar. Aşağıdaki bölümde, dışa aktarımınızı mevcut bir Excel dosyasına eklemenize izin veren işlevleri tanıtıyoruz.
Bunun altına, hata işleme ve daha fazlası dahil olmak üzere yeni Excel dosyalarına aktarmak için bazı ek işlevler ekledik.
Mevcut Excel Dosyasına Aktar
Yukarıdaki kod örnekleri, Access nesnelerini yeni bir Excel dosyasına dışa aktarmak için harika çalışır. Ancak, mevcut bir çalışma kitabına dışa aktaramazlar.
Access nesnelerini mevcut bir Excel çalışma kitabına aktarmak için aşağıdaki işlevi oluşturduk:
Public Function AppendToExcel(strObjectType As String, strObjectName As String, strSheetName As String, strFileName As String) Dim rst As DAO.Recordset Dim ApXL As Excel.Uygulama Dim xlWBk As Excel.Workbook Dim xlWSh As Excel.Worksheet Dim in ConstCount xlToger As Integer 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) Case "Form" Set rst = Forms(strObjectName).RecordsetClone Case "Report" Set rst = CurrentDb.OpenRecordset(Reports(strObjectName).RecordSource, dbOpenDynaset, dbSeeChanges) End Select rst.RecordCount = 0 ise MsgBox "Verilecek kayıt yok" .", vbInformation, GetDBTitle Else On Error Resume Next Set ApXL = GetObject(, "Excel.Application") Err.Number 0 ise, ApXL Ayarla = CreateObject("Excel.Application") Err.Clear ApXL.Visible = False ise Sonlandır xlWBk = ApXL.Workbooks.Open(strFil) olarak ayarlayın eName) xlWSh = xlWBk.Sheets.Add xlWSh.Name = Left(strSheetName, 31) xlWSh.Range("A1") olarak ayarlayın. intCount = rst.fields.Count ApXL.ActiveCell = rst.fields(intCount)'a Kadar Yap'ı seçin. Ad ApXL.ActiveCell.Offset(0, 1).Select intCount = intCount + 1 Loop ilk.MoveFirst xlWSh.Range("A2").CopyFromRecordset ilk 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.S = 0 .Selection.Interior.PatternColorIndex xlNone .Selection.AutoFilter .Cells.EntireColumn.AutoFit .Cells.EntireRow.AutoFit .Range("B2").Select .ActiveWindow.FreezePanes = True .ActiveSheet.Cells.Select .ActiveSheet.Cells.Cells. .EntireColumn.AutoFit xlWSh.Range("A1").Select .Visible = True End with 'xlWB.Close True 'Set xlWB = Nothing 'ApXL.Quit 'ApXL Ayarla = Bitirmeden Hiçbir Şey Bitmez
Fonksiyonu şu şekilde kullanabilirsiniz:
Private Sub AppendToExcel_Example() Call VBA_Access_ImportExport.ExportToExcel("Table", "Table1", "VBASheet", "C:\Temp\Test.xlsx") End Sub
Tanımlamanız istendiğine dikkat edin:
- Ne Çıktı? Tablo, Rapor, Sorgu veya Form
- Nesne adı
- Çıktı Sayfası Adı
- Çıktı Dosyası Yolu ve Adı.
SQL Sorgusunu Excel'e Aktarın
Bunun yerine, benzer bir işlevi kullanarak bir SQL sorgusunu Excel'e aktarabilirsiniz:
Public Function AppendToExcelSQLStatemet(strsql As String, strSheetName As String, strFileName As String) Dim strQueryName As String Dim ApXL As Excel.Uygulama Dim xlWBk As Excel.Workbook Dim xlWSh As Excel.Worksheet Dim intCount As Integer Const xl-4Center As xlBottom As Long = -4107 Const xlVAlignCenter = -4108 Const xlContinuous As Long = 1 Dim qdf As DAO.QueryDef Dim İlk As DAO.Recordset strQueryName = "tmpQueryToExportToExcel" If ObjectExists("Query", strQueryDb) Sonra CurrentDb.DefQueryName. End If Set qdf = CurrentDb.CreateQueryDef(strQueryName, strsql) Set rst = CurrentDb.OpenRecordset(strQueryName, dbOpenDynaset) rst.RecordCount = 0 ise MsgBox "Dışa aktarılacak kayıt yok.", vbInformation, GetDBTitle Else On Set Hatası ApXL = GetObject(, "Excel.Application") Err.Number 0 ise Set ApXL = CreateObject("Excel.Application") End If Err.Clear ApXL.Visible = False Set xlWBk = ApXL.Workbooks.Open(strFileName) Set xlWSh = xlWBk.Sayfa s.Add xlWSh.Name = Sol(strSheetName, 31) xlWSh.Range("A1"). Şuna Kadar Yap'ı seçin intCount = rst.fields.Count ApXL.ActiveCell = rst.fields(intCount).Name ApXL.ActiveCell.Offset( 0, 1).Select intCount = intCount + 1 Loop rst.MoveFirst xlWSh.Range("A2").CopyFromRecordset with ApXL .Range("A1").Select .Range(.Selection, .Selection.End(xlToRight) .Selection.Interior.Pattern = xlSolid.Selection.Interior.PatternColorIndex = xlAutomatic .Selection.Interior.TintAndShade = -0.25 .Selection.Interior.PatternTintAndShade. .EntireColumn.AutoFit .Cells.EntireRow.AutoFit .Range("B2").Select .ActiveWindow.FreezePanes = True .ActiveSheet.Cells.Select .ActiveSheet.Cells.WrapText = False .ActiveSheet.CoellnWS.Entir ("A1").Seçin .Visible = 'xlWB.Close True' İle Gerçek Son
Böyle çağrılır:
Private Sub AppendToExcelSQLStatemet_Example() Çağrı VBA_Access_ImportExport.ExportToExcel("SELECT * FROM Table1", "VBASheet", "C:\Temp\Test.xlsx") End Sub
Girmeniz istendiği yer:
- SQL Sorgusu
- Çıktı Sayfası Adı
- Çıktı Dosyası Yolu ve Adı.
Yeni Excel Dosyasına Aktarma İşlevi
Bu işlevler, Access nesnelerini yeni bir Excel çalışma kitabına aktarmanıza olanak tanır. Bunları, belgenin üst kısmındaki basit tek satırlardan daha kullanışlı bulabilirsiniz.
Public Function ExportToExcel(strObjectType As String, strObjectName As String, Opsiyonel strSheetName As String, Opsiyonel strFileName As String) Dim rst As DAO.Recordset Dim ApXL As Object Dim xlWBk As Object Dim xlWSh As Object Dim intCount As Object Dim intCount As Integer Const xlToR As Uzun 4161 Const xlCenter As Long = -4108 Const xlBottom As Long = -4107 Const xlContinuous As Long = 1 Hatada GoTo ExportToExcel_Err DoCmd.Hourglass True Select Case strObjectType Case "Table", "Query" Set rst = CurrentDb.OpenRecordset(strOpenD, d , dbSeeChanges) Case "Form" Set rst = Forms(strObjectName).RecordsetClone Case "Report" Set rst = CurrentDb.OpenRecordset(Reports(strObjectName).RecordSource, dbOpenDynaset, dbSeeChanges) End Then Select If rst = 0.RecordsCount "No MRecordsCount" dışa aktarılacak kayıtlar.", vbInformation, GetDBTitle DoCmd.Hourglass False Else On Error Resume Next Set ApXL = GetObject(, "Excel.Application") Eğer Err.Number 0 Sonra Set ApXL = CreateObject("Excel.Application") End If Hata. Hatayı Temizle ExportToExcel_Err'a Git xlWBk = ApXL.Workbooks.Add ApXL.Visible = False Set xlWSh = xlWBk.Worksheets("Sheet1") Len(strSheetName) > 0 ise xlWSh.Name = Left(strSheetName, 31) End If xlWSh .Range("A1"). Şuna Kadar Yap'ı seçin intCount = rst.fields.Count ApXL.ActiveCell = rst.fields(intCount).Name ApXL.ActiveCell.Offset(0, 1).Select intCount = intCount + 1 Loop rst. MoveFirst xlWSh.Range("A2").CopyFromRecordset ilk ApXL ile .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 .Cells.EntireColumnell.AutoFit.EntireColumnell. B2").Select .ActiveWindow.FreezePanes = True .ActiveSheet.Cells.Select .ActiveSheet.Cells.WrapText = False .ActiveSheet.Cells.EntireColumn.AutoFit xlWSh.Range("A1").Select .Visible = True End Wi th yeniden deneme: Eğer FileExists(strFileName) O zaman Kill strFileName Eğer strFileName "" ise End If strFileName "" O zaman xlWBk.SaveAs strFileName, FileFormat:=56 End If rst.Close Set rst = Hiçbir Şey DoCmd.Hourglass False End If ExportToExcel_Exit: DoCmd Ex.Hourglass ExportToExcel_Err: DoCmd.SetWarnings True MsgBox Err.Description, vbExclamation, Err.Number DoCmd.Hourglass False Resume ExportToExcel_Exit End Function
İşlev şu şekilde çağrılabilir:
Private Sub ExportToExcel_Example() Çağrı VBA_Access_ImportExport.ExportToExcel("Table", "Table1", "VBASheet") End Sub
