Cara menggunakan VBA untuk mengekspor data buku kerja Excel dari lembar data kueri Access dan memformat lembar kerja setelah data tiba Show
Terakhir diperbarui pada 27-10-2022 oleh David Wallis Kata pengantarUntuk banyak database yang saya kembangkan, saya menyertakan kemampuan ekspor-ke-Excel. Ini untuk klien yang menginginkan cara membuang data sehingga mereka dapat melakukan hal mereka sendiri tanpa risiko terhadap data primer Artikel ini menjelaskan prosedur VBA untuk membuang data dari Access ke Excel, memanfaatkan DoCmd. Metode TransferSpreadsheet untuk membawa Anda melampaui penjelasan cara kerja metode ini yang Anda baca di banyak situs web. Pertimbangan Awal Digabungkan dari persyaratan klien selama bertahun-tahun, saya telah mengidentifikasi bahwa ini adalah fitur utama dari kemampuan ekspor-ke-Excel
Semua database DMW yang disediakan untuk klien terbagi — Front End (FE) dan Back End (BE). FE berisi kueri, formulir, laporan, makro, dan modul, dan, jika sesuai, satu atau dua tabel, seperti yang akan saya jelaskan di bawah BE hanya berisi tabel Dari sudut pandang pengembang, ini adalah pertimbangan
Jika pengguna diizinkan untuk mengotak-atik, itu akan menjadi tugas yang tidak praktis dan memakan waktu bagi saya untuk memberikan peningkatan dan dukungan terus-menerus kepada klien. Dan, klien tidak akan senang dengan tagihan tersebut Metode Spreadsheet Transfer VBAProsedur kami sExportToExcel untuk mengekspor data menggunakan TransferSpreadsheet method to export the contents of a table, or of a query datasheet, as a named Excel file to a named folder: Sub sExportToExcel( query$ , path$ ) DoCmd. TransferSpreadsheet _ Jenis Transfer. =acEkspor, _ Jenis lembar kerja. =acSpreadsheetTypeExcel12Xml, _ NamaTabel. = kueri$ ,_ Nama Berkas. = jalur$ , _ HasFieldNames. = Benar Akhir Sub Sebagai contoh Pemrograman untuk Membuka Buku Kerja yang DieksporSejauh ini prosedur kami membuat buku kerja dan menyimpannya. Pengalaman menunjukkan bahwa sebagian besar pengguna ingin melihat hasil ekspor segera setelah selesai Jadi, proses selanjutnya di ExportToExcel adalah menampilkan workbook yang telah selesai. Sub sExportToExcel(kueri$, jalur$) Dim xlApp Sebagai Objek, wkbk Sebagai Objek< DoCmd. TransferSpreadsheet _ Jenis Transfer. =acEkspor, _ Jenis lembar kerja. =acSpreadsheetTypeExcel12Xml, _ TableName. =permintaan$, _ Nama file. =jalur$, _ HasFieldNames. = Benar Set xlApp = CreateObject("Excel. Aplikasi") Dengan xlApp Terlihat = Benar Atur wkbk =. Buku kerja. Buka(jalur$) Berakhir dengan Akhir Sub Penanganan KesalahanPada titik ini kami akan menyertakan penanganan kesalahan dan memastikan bahwa prosedur melepas koneksi apa pun dengan Excel setelah menyajikan buku kerja. Sub sExportToExcel(kueri$, jalur$) Pada Kesalahan Goto errHandler Redupkan xlApp Sebagai Objek, wkkk Sebagai Objek Redupkan pesan$, ikon&, judul$ DoCmd. TransferSpreadsheet _ Jenis Transfer. =acEkspor, _ Jenis lembar kerja. =acSpreadsheetTypeExcel12Xml, _ TableName. =permintaan$, _ Nama file. =jalur$, _ HasFieldNames. = Benar Atur xlApp = CreateObject("Excel. Aplikasi") Dengan xlApp Terlihat = Benar Tetapkan wkwk =. Buku kerja. Buka(jalur$) Berakhir dengan procDone Tetapkan wkwk = Tidak ada Atur xlApp = Tidak ada Keluar Sub errHandler judul$ = "KESALAHAN EKSPOR DATA" ikon& = vbOKOnly + vbCritical pesan $ = _ "Tolong ambil cuplikan layar dari pesan ini. " & _ vbNewline & vbNewLine & _ "Jika tidak, catat detail berikut. " & _ vbNewline & vbNewLine & _ "Memanggil prok. Ekspor ke Excel" & _ vbNewLine & _ "Nomor Kesalahan. " & Er. Nomor & _ vbNewLine & _ "Keterangan. " & Er. Keterangan MsgBox msg$, ikon&, judul$" Lanjutkan proses Akhir Sub Penamaan Jalur dan Buku KerjaAnda harus berhati-hati saat memberikan nilai ke argumen path$ dari sExportToExcel(query$ . Pertimbangkan nilai-nilai ini. . Consider these values: "S. \Laporan\Hasil. XLSX" Ini berfungsi dengan memuaskan — Buku Kerja bernama Hasil. XLSX diarahkan ke S. folder \Laporan\ "S. \Reports\Results" Ini bekerja dengan memuaskan juga — prosedur melampirkan. Ekstensi XLSX ke nama buku kerja agar Results. XLSX diarahkan ke S. folder \Laporan\ "S. \Reports\Results\" Di sini muncul masalah bahwa \ final menyebabkan prosedur untuk menangani S. \Reports\Results\ sebagai folder, tanpa menentukan buku kerja sama sekali, dengan kondisi kesalahan yang dihasilkan, e. g. — Dalam program ekspor lengkap yang dijelaskan di bawah ini, terdapat kode untuk menghadapi masalah ini Penyempurnaan Program — Menyediakan untuk Perubahan FolderDalam pengalaman saya, klien suka menentukan sendiri ke folder mana proses ekspor mengarahkan buku kerja yang dibuatnya Ini adalah persyaratan khusus ketika TI klien menginginkan kebebasan untuk mengubah lokasi file di jaringan dan memetakan ulang drive Satu metode untuk menyediakan ini menggunakan tambahan pada dua file FE dan BE utama. File ketiga ini saya beri nama KEY. ini , yang merupakan file teks sederhana, yang isinya adalah ini. Penting '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ File ini ditempatkan di folder yang sama dengan file USER Edit ExportPath agar sesuai dengan struktur folder Anda '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ BAWAAN] ExportPath = "S. \Laporan\" Sekarang kita perlu mengakomodasi KEY. ini ke dalam proses ekspor. Proses harus memilih jalur ekspor dari KEY. ini . Selaras dengan praktik yang baik, kita akan menyusun pemrograman kita dengan memisahkan kode kita menjadi sejumlah subrutin. Masing-masing akan melakukan operasi yang berbeda, salah satunya akan memilih dari KEY. ini . Prosedur PengendalianIni adalah urutan rutin yang akan dipanggil oleh program pengontrol, bernama sExportData ,. fnGetPathFromKey Subrutin fungsi ini akan mencari KEY. ini dan dapatkan jalur ekspor darinya. Jika fnGetPathFromKey tidak dapat menemukan KEY. ini , atau tidak dapat menemukan informasi tentang jalur ekspor, maka akan mengembalikan pesan kesalahan ke sExportData . fnCheckPath Subrutin fungsi ini akan mencari konfirmasi keberadaan folder yang akan diarahkan ke dump. Jika fnCheckPath tidak dapat menemukan folder tersebut, folder tersebut akan dilaporkan hilang ke sExportData. fnExportToExcel Ini akan menyelesaikan program ekspor yang meneruskan data dari Access dan ke Excel, pembukaan Excel untuk menampilkan data, dan pemformatan lembar kerja. Dapatkan Path dari Subrutin File KUNCITugas subrutin fnGetPathFromKey adalah mengambil jalur back-end DATA< . ini file from KEY.ini . Fungsi fnGetPathFromKey( pathINI$ , element$ ) On Error GoTo errHandler Redupkan i&, lenElement& Redupkan fstChar34%, lstChar34% Redupkan barisINI$, jalur$ Jika Len(Dir(pathINI$)) > 0 Dan Len(element$) > 0 Lalu lenElement& = Len(elemen$) i& = FreeFile() Buka pathINI$ Untuk Input As #i& Lakukan Sementara Tidak EOF(i&) Masukan Baris #i&, barisINI$ Jika Left(lineINI$, lenElement&) = element$ Kemudian path$ = Mid(barisINI$, lenElement& + 1) Keluar Lakukan Berakhir jika Lingkaran Tutup #i& fstChar34% = InStr(path$, Chr(34)) + 1 lstChar34% = InStrRev(path$, Chr(34)) jalur$ = Pertengahan(jalur$, fstChar34%, lstChar34% - fstChar34%) Kalau tidak jalur$ = "Kesalahan" Berakhir jika procDone fnGetPathFromKey = jalur$ Keluar Fungsi errHandler jalur$ = "Kesalahan" Lanjutkan proses Fungsi Akhir Jika tidak dapat mengembalikan keberadaan DATA , maka fnGetPathFromKey warnings from which sExportData composes messages to the user. Periksa Jalur SubrutinTugas subrutin kedua, fnCheckPath , dalam program ekspor adalah mengonfirmasi bahwa folder yang ditentukan dalam KEY.ini benar-benar ada. Fungsi fnCheckPath( path$ ) Sebagai String On Error GoTo errHandler Pesan redup $ Jika Dir( path$ , vbDirectory) = ". " Lalu msg$ = vbNullString Kalau tidak pesan $ = _ "Tidak ada folder yang cocok dengan entri di file KEY" Berakhir jika procDone fnCheckPath = msg$ Keluar Fungsi errHandler pesan $ = Err. Keterangan Lanjutkan proses Fungsi Akhir fnCheckPath mengembalikan string nol jika folder ada di tempat, atau pesan jika folder itu hilang atau program tidak dapat menemukannya di lokasi yang diharapkan. Ekspor ke Subrutin ExcelKlien saya paling suka melihat tindakan ini dilakukan oleh program ekspor
Subrutin ketiga dan terakhir dalam program ini adalah fnExportToWorkbook . Rutin ini berisi baris-baris kode yang ditujukan untuk mempercantik isi lembar kerja yang dibuat oleh ekspor. Fungsi fnExportToWorkbook( _ kueri$, jalur$, _ namafile$, wksName$, _ colsCurrency$, colsDate$ _ ) Sebagai Tali On Error GoTo errHandler Dim xlApp Sebagai Objek, wks Sebagai Objek, wks Sebagai Objek File redup$ Dim formatCur$, formatDate$, intColor& Dim arrayCols() Sebagai String, col$, n%, i%, w Sel redup Sebagai Jangkauan Pesan redup $ ' Format lembar kerja formatCur$ = "£#,##0. 00" formatDate$ = "yyyy-mm-dd" intWarna& = RGB(100, 200, 200) 'Buat buku kerja file$ = jalur$ & namafile$ DoCmd. TransferSpreadsheet _ Jenis Transfer. =acEkspor, _ Jenis lembar kerja. =acSpreadsheetTypeExcel12Xml, _ TableName. =permintaan$, _ Nama file. = berkas$, _ HasFieldNames. = Benar 'Buka buku kerja Atur xlApp = CreateObject("Excel. Aplikasi") Dengan xlApp Terlihat = Benar Tetapkan wkwk =. Buku kerja. Buka(berkas$) Berakhir dengan ' Memformat lembar kerja Tetapkan wks = wkwk. lembar kerja(1) Dengan wks Nama = wksNama$ ' Kolom mata uang arrayCols = Pisahkan(colsCurrency$, ","") Untuk i = LBound(arrayCols) Ke UBound(arrayCols) Dengan. Kolom(arrayCols(i)) NumberFormat = formatCur$ Berakhir dengan Selanjutnya saya ' Tanggal kolom arrayCols = Pisahkan(colsDate$, ","") Untuk i = LBound(arrayCols) Ke UBound(arrayCols) Dengan. Kolom(arrayCols(i)) NumberFormat = formatTanggal$ Berakhir dengan Selanjutnya saya ' Filter Dengan. Rentang("A1") Pilih filter otomatis Berakhir dengan ' Penyesuaian lebar kolom Dengan. Sel Pilih Seluruh kolom. Paskan Otomatis Berakhir dengan n% =. Sel(1, 1). Akhir(xlToRight). Kolom Untuk i% = 1 Ke n% Dengan. Sel (1, i%) w. =. Seluruh kolom. Lebar kolom Seluruh kolom. Lebar Kolom = w. + 4 Perataan Horizontal = xlTengah Pedalaman. Warna = intWarna& Fon. Tebal = Benar Berakhir dengan i% berikutnya Berakhir dengan Dengan xlApp. Jendela Aktif Kolom Terpisah = 0 SplitRow = 1 FreezePanes = Benar Berakhir dengan msg$ = vbNullString procDone Tetapkan wks = Tidak ada Tetapkan wkwk = Tidak ada Atur xlApp = Tidak ada fnExportToWorkbook = msg$ Keluar Fungsi errHandler pesan $ = _ Berbuat salah. Nomor & ". " & Er. Keterangan Lanjutkan proses Fungsi Akhir Selesai Ekspor ke Program ExcelIni adalah prosedur pengontrolan yang menyatukan subrutin untuk melakukan ekspor ke Excel Sub sExportData(query$, fileName$, wksName$, _ colsCurrency$, colsDate$) On Error GoTo errHandler Dim bln As Boolean Jalan redup$ Pesan redup $ path$ = Left(CurrentProject. Nama lengkap, _ InStrRev(Proyek Saat Ini. Nama lengkap, "\")) jalur$ = jalur$ & "KUNCI. ini" jalur$ = fnGetPathFromKey(jalur$, "ExportPath") Pilih Jalur kasus$ Kasus "Kesalahan" msg$ = "Kesalahan tak terduga menemukan KUNCI" bln = Salah Kasus Lain bln = Benar Akhiri Pilih Jika bln Lalu msg$ = fnCheckPath(path$) Jika msg$ = vbNullString Kemudian msg$ = fnExportToWorkbook( _ kueri$, jalur$, _ namafile$, wksName$, _ colsCurrency$, colsDate$) Kalau tidak pesan $ = _ "Folder untuk ekspor tidak dapat ditemukan. " & pesan$ Berakhir jika procDone Jika msg$ <> vbNullString Kemudian MsgBox msg$, vbExclamation, "EKSPOR DATA KE EXCEL" Berakhir jika Keluar Fungsi errHandler pesan $ = _ "Tolong ambil cuplikan layar dari pesan ini. " & _ vbNewline & vbNewLine & _ "Jika tidak, catat detail berikut. " & _ vbNewline & vbNewLine & _ "Proses. sExportData>" & _ vbNewLine & _ "Nomor Kesalahan. " & Er. Nomor & _ vbNewLine & _ "Keterangan. " & Er. Keterangan Lanjutkan proses Fungsi Akhir Beginilah cara Anda memberikan nilai untuk argumen sExportData . query$ as “qsExportSalesByMonth” — kueri database Anda fileName$ as “Export Sales” — nama file buku kerja. wksName$ as “Feb 2020 ” — nama lembar kerja colsCurrency$ sebagai “F. F” — Format mata uang Kolom F colsDate$ as “C. CD. D” — Format tanggal Kolom C dan D. Setiap nilai argumen harus dilampirkan dalam tanda kutip seperti yang ditunjukkan di atas Periksa Referensi AndaTerima kasih kepada Marek, yang pada tanggal 14-10-2020 berkomentar dengan bijak tentang penghilangan artikel saya saat itu “Kalau-kalau ini bisa membantu. Saya melihat prosedur Ekspor ke Excel Anda di [halaman web ini] “Saya tidak yakin apakah menambahkan kebutuhan untuk menambahkan referensi ke Perpustakaan Objek Microsoft Excel akan membantu karena versi Access saya tidak ada “Salam dan terima kasih telah membagikan kode Anda. ” Dalam kode Anda, Access membuat panggilan ke Excel. Agar berfungsi, Anda perlu memberi tahu Access bahwa Anda menggunakan Excel. Ini adalah bagaimana Anda memberi tahu Access
Tindakan kelima memeriksa apakah Access telah memahami hal-hal tersebut atau tidak SumbanganHarap dukung situs web ini dengan memberikan donasi untuk membantunya tetap bebas dari iklan dan untuk membantu biaya waktu yang dihabiskan untuk menambahkan konten baru Bagaimana cara mengekstrak data dari database Access ke Excel menggunakan VBA?Cara Sederhana . Buat Prosedur VBA. Microsoft Access menyediakan metode DoCmd yang sangat berguna. TransferSpreadsheet untuk mengekspor, mengimpor, atau menautkan data Excel. . Jalankan Prosedur. Jalankan prosedur dan periksa apakah file dibuat di jalur yang tercantum dalam variabel excel_file_name di prosedur Anda Bisakah Anda menarik data dari Access ke Excel?Menggunakan Panduan Ekspor, Anda dapat mengekspor data dari database Access ke dalam format file yang dapat dibaca oleh Excel .
Bagaimana cara mendapatkan data dari tabel Access di VBA?Menjawab. Untuk mengakses nilai langsung dari tabel menggunakan kode VBA, Anda harus membuat Modul baru terlebih dahulu. Untuk melakukan ini, buka jendela Database, pilih tab Modul dan klik tombol Baru. Saat jendela Microsoft Visual Basic muncul, Anda dapat membuat fungsi baru untuk mengambil nilai dari tabel
Bagaimana cara mengekspor data dari MS Access ke Excel menggunakan makro?Cara Membuat Makro Microsoft Access untuk Mengekspor Informasi ke Excel atau Word. . Klik opsi "Makro" di menu "Objek". Klik "Baru" di panel atas jendela database untuk membuat makro baru Klik di bidang "Tindakan". Klik panah drop-down dan pergi ke "MsgBox" |
Dapatkan data dari akses ke excel vba
Pos Terkait
Copyright © 2024 kemunculan Inc.
