访问表数据到Excel [英] Access table data to Excel

问题描述

我有一个问题我被困在了。

我想将Access表导出到Excel文件。目前，我使用 DoCmd.TransferSpreadsheet 这样做，但是我想要在导出的数据上进行某种格式化。我可以格式化我发送到Excel的数据，还是必须在Excel中编写一个宏，将其从Access中导出后格式化数据？

解决方案

我们有一个通用功能，将我们的连续表单直接导出到Excel。您需要在可用的工具中添加Microsoft Excel库，以使其在代码中起作用。该功能可以以所有形式提供给用户。如果你想直接从一个表导出，你可以很容易地适应它。

 公共功能ExportToExcel（x_frm作为窗体）
 
 Dim ctl As Control，_ 
 xlApp As Excel.Application，_ 
 xlBook As Excel.Workbook，_ 
 xlSheet As Excel.Worksheet，_ 
 columnName （）As String，_ 
 columnCount as Integer 
 
'假设Excel没有打开。你可以添加一个额外的控件'
 Set xlApp = CreateObject（Excel.Application）
 
'创建一个Excel工作簿，声明第一个工作表'
 Set xlBook = （1）
 xlApp.Visible = False 
 
 columnCount = fc（）。section（0）.Controls.Count 
 
'使用数组columnName（）以标签顺序收集详细信息部分的列名'
 ReDim columnName（columnCount）
 
对于每个ctl在fc（）中。 section（0）.Controls 
 columnName（ctl.TabIndex）= ctl.Name 
下一个ctl 
 
'这个函数将添加一个标题到excel表与我最喜欢的形成' 
'我可以例如决定这个标题是粗体/ Arial / 16在单元格（B2）'
 addTitleToExcelSheet xlSheet，x_frm.Name 
 
'此函数将添加列我的Excel表格的名称与我最喜欢的形式'
'例如列名将添加到行4，列B到B +列C $'
 addColumnNameToEx celSheet xlSheet，columnName（）
 
'这个函数会将列值添加到我的Excel表格中，具体格式（日期，数字等）'
'数据将被添加到定义的范围'
'行5到行5 + x_frm.recordset.recordcount''
'列B到B + columnCount'
'记录集值必须根据标签顺序被读取' 
'导出的数据可以依赖于记录集过滤器和orderBy属性：您的选择'
 addColumnValueToExcelSheet xlSheet，columnName（），x_frm.Recordset 
 
'Excel工作表可见并保存在当前文件夹下，表单名称为
 xlApp.Visible = True 
 xlBook.SaveAs x_frm.Name& .xls
 
设置xlBook =没有
设置xlSheet =没有
设置xlApp =没有
 
结束函数
  

这是结果的视图。左边是Access窗体，右边是Excel exportToExcel的结果。希望你喜欢。

I have a problem I have got stuck on.

I want to export my Access table to an Excel file. Currently, I do that using DoCmd.TransferSpreadsheet, but I want some kind of formatting to be done on the exported data. Can I format that data I am sending to Excel or do I have to write a macro in Excel that will format that data after it has been exported from Access?

解决方案

We have a generic function that will export our continuous forms directly to Excel. You'll need to add the Microsoft Excel Library in the available tools to have it work from your code. This function can be made available to users in all your forms. If you want to export directly from a table, you can easily adapt it.

Public Function ExportToExcel(x_frm as Form)

Dim ctl As Control, _
    xlApp As Excel.Application, _
    xlBook As Excel.Workbook, _
    xlSheet As Excel.Worksheet, _
    columnName() As String, _
    columnCount as Integer

'suppose Excel is not opened. You can add an extra control for that'
Set xlApp = CreateObject("Excel.Application")

'create an Excel workbook, declare the first sheet'
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
xlApp.Visible = False

columnCount = fc().section(0).Controls.Count

'use array columnName() to collect the column names of detail section in tabbed order'
ReDim columnName(columnCount)

For Each ctl In fc().section(0).Controls
    columnName(ctl.TabIndex) = ctl.Name
Next ctl

'This function will add a title to the excel sheet with my favorite formating'
'I can for example decide this title to be bold/Arial/16 on cell(B2)'
addTitleToExcelSheet xlSheet, x_frm.Name

'This function will add the column names to my Excel sheet with my favorite formating'
'for example column names will be added on row 4, columns B to B + columnCount'
addColumnNameToExcelSheet xlSheet, columnName()

'This function will add the column values to my Excel sheet with specific format (date, number, etc)'
'Data will be added to the range defined by '
'row 5 to row 5 + x_frm.recordset.recordcount, '
'columns B to B + columnCount.'
'Recordset values will have to be read according to tab order'
'exported data can depend on recordset filter and orderBy property: your choice'
addColumnValueToExcelSheet xlSheet, columnName(), x_frm.Recordset

'The Excel sheet is made visible and saved under the current folder with the forms name'
xlApp.Visible = True
xlBook.SaveAs x_frm.Name & ".xls"

Set xlBook = Nothing
Set xlSheet = Nothing
Set xlApp = Nothing

End Function

Here is a view of the result. Left is Access form, right is Excel exportToExcel result. Hope you like it.

这篇关于访问表数据到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！