Excel VBA - 使用 VBA 创建新的格式化工作簿

Excel VBA - UsingVBA to create a new, formatted workbook

提问by Reznor

I'm trying to write the last part of my program and I need to pull data from an Access document and print it into a new Workbook.

我正在尝试编写程序的最后一部分，我需要从 Access 文档中提取数据并将其打印到新的工作簿中。

To start, I will be taking the names of product Suppliers and creating a Worksheet with each suppliers name, then I want to be looping through each sheet and printing the products from each supplier that were ordered.

首先，我将获取产品供应商的名称并创建一个包含每个供应商名称的工作表，然后我想遍历每张表并打印来自每个订购的供应商的产品。

I'm really struggling with wrapping my head around how to open a new workbook and print in my info.

我真的很困惑如何打开新工作簿并打印我的信息。

回答by Barranka

As my previous answer was deleted (considered "insuficient"), I have to provide a better one.

由于我之前的答案被删除（被认为是“不足”），我必须提供一个更好的答案。

If you want to output data from Access to Excel, you have to follow this steps:

如果要将数据从 Access 输出到 Excel，则必须按照以下步骤操作：

1. Create (or open) a new workbook
2. Read your data
3. Write your data to the workbook
4. Format the data in the workbook

1. 创建（或打开）新工作簿
2. 读取您的数据
3. 将数据写入工作簿
4. 格式化工作簿中的数据

I will focus on the data output, and leave the formatting out (the data part is the complicated one... formatting is easy)

我将专注于数据输出，而不是格式化（数据部分是复杂的......格式化很容易）

First, you need to enable the Excel objects in your Access file: Tools Menu > References. Find the Microsoft Excel 12.0 Object Libraryand activate the checkbox. Now you have the full Excel library at your service :-)

首先，您需要在 Access 文件中启用 Excel 对象：工具菜单 > 引用。找到Microsoft Excel 12.0 对象库并激活复选框。现在，您拥有完整的 Excel 库：-)

Now is the time for the data crunching. I will asume that you need to create a new workbook:

现在是数据处理的时候了。我假设您需要创建一个新工作簿：

public sub createExcelFile()
    dim XL as Excel.Application, WB as Excel.Workbook, WKS as Excel.Worksheet
    dim db as DAO.database, rec as DAO.recordset, f as DAO.field
    dim i as integer, j as integer

    ' Prepare your Excel stuff
    Set XL = new Excel.Application
    XL.Visible = True
    Set WB = XL.Workbooks.Add 
    WB.Activate
    Set WKS = WB.ActiveSheet ' Default: The first sheet in the newly created book

    ' Read your data here
    set db = currentdb()
    set rec = db.openrecordset("tblSampleData")

    ' A simple table that will show the data from rec
    ' i and j will be the coordiantes of the active cell in your worksheet
    with rec
        .movefirst

        ' The table headers
        i = 1
        j = 1
        for each f in .fields
            WKS.cells(i,j).value = f.name
            j = j + 1
        next f

        ' The table data
        do
            i = i+1
            j = 1
            for each f in .Fields
                WKS.cells(i,j).value = f.value
                j = j+1
            next f     
            .moveNext     
        loop until .EOF
    end with
end sub

If you want to format the cells, you can use the WKS.cells(i,j)(or WKS.range(...)) properties.

如果要格式化单元格，可以使用WKS.cells(i,j)(或WKS.range(...)) 属性。

Take a look at the link I leaved before (which Siddarth Rout was kind to move to the comments).

看一看我之前留下的链接（Siddarth Rout 很乐意转到评论）。

I hope this helps you

我希望这可以帮助你

回答by user1934049

Option Compare Database
Public Function format(filepath, sheetname)


Set xls = CreateObject("EXCEL.APPLICATION")
xls.screenupdating = False
xls.displayalerts = False
xls.Visible = True
xls.workbooks.Open filepath
Set xlsdd = xls.ActiveWorkbook

'deleting headers

'删除标题

xls.Range("1:1").Select
xls.Selection.Delete Shift:=xlUp

'adding one column

'添加一列

    xls.Columns("A:A").Select
   xls.Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

'adding 5 rows

'ActiveWorkbook.Sheets("sheet1").Select

'ActiveWorkbook.Sheets("sheet1").Select

xls.Rows("1:5").Insert Shift:=xlDown

'fetching rows from access and putting them into excel

'从访问中获取行并将它们放入excel

strsql = "select top 5 " & sheetname & ".* into top5_records from " & sheetname
DoCmd.RunSQL strsql
outputFileName = "C:\Users\hp\Desktop\top5_records.xls"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "top5_records",    outputFileName, True

'then open that excel and copy the rows

'然后打开那个excel并复制行

Set xls2 = CreateObject("EXCEL.APPLICATION")
xls2.screenupdating = False
xls2.displayalerts = False
xls2.Visible = True
xls2.workbooks.Open outputFileName
Set xlsdd2 = xls.ActiveWorkbook
 xls2.Rows("1:5").Select
 xls2.Selection.Copy
  xls.Cells(1, 1).Select
 xls.activesheet.Paste



     '  Dim currdb As DAO.Database
'  Dim rst As DAO.Recordset
'
'  Set currdb = CurrentDb
'  Set rst = currdb.OpenRecordset(strsql) '<<<Opens query recordset via DAO
'  rst.MoveLast
'  rowsToReturn = rst.RecordCount
'  Set rng = xls.Cells(1, 1)
'  'copy specified number of records to worksheet
'
'rng.CopyFromRecordset rst, rowsToReturn '<<<Gets all records in recordset

'making first 6th row to be bold

'使第 6 行变粗

 xls.Rows("6:6").Select
  With xls.Selection.Font
  .Bold = True
  .Name = "Arial"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
End With

'autofit the data

'自动调整数据

xls.Sheets(sheetname).Cells.Columns.autofit
xls.CutCopyMode = False
With xlsdd
.Save
.Close
End With
xls.Visible = False

Set xlsdd = Nothing
Set xls = Nothing

End Function

回答by Andrew

You can define column/row widths to a static pixel amount or auto-fit, things like bold are a pre-defined

您可以将列/行宽度定义为静态像素数量或自动调整，例如粗体是预定义的

Example

例子

Selection.Font.Bold = True

You can also make a template spreadsheet, copy the contents into the template and save as. Your post does not indicate how much formatting actually needs to be done.

您还可以制作模板电子表格，将内容复制到模板中并另存为。您的帖子并未说明实际需要进行多少格式化。

回答by Dick Kusleika

You don't give a lot of details, so I can't give you a lot of details in return. But here's how I would do it:

你不给很多细节，所以我不能给你很多细节作为回报。但这是我将如何做到的：

1. Create a new workbook manually with two sheets
2. On one sheet, add an External Data table that returns a list of supplier's name like SELECT SupplierName FROM tblSuppliers WHERE Active=True; or something like that.
3. Create a workbook-level named range that dynamically expands with that query table
4. On the second sheet, add an External Data table like SELECT * FROM Orders WHERE SupplierName=? (This will be a parameter query). Start that external data table in row 3
5. I row, put a combobox box that points back to the supplier list.

1. 用两张工作表手动创建一个新工作簿
2. 在一张纸上，添加一个外部数据表，该表返回供应商名称列表，如 SELECT SupplierName FROM tblSuppliers WHERE Active=True; 或类似的东西。
3. 创建一个工作簿级别的命名范围，该范围随该查询表动态扩展
4. 在第二张纸上，添加一个外部数据表，如 SELECT * FROM Orders WHERE SupplierName=? （这将是一个参数查询）。在第 3 行开始该外部数据表
5. 我划船，放了一个指向供应商列表的组合框。

Now the VBA is simple

现在VBA很简单

ThisWorkbook.RefreshAll

Instead of one sheet per supplier, you'll have one sheet on which you can change the supplier. Here are the skills you'll need

您将拥有一张纸，您可以在其中更改供应商，而不是每个供应商一张纸。这是你需要的技能

• Create an external data table
• Create a parameter query (old reference http://www.dicks-clicks.com/excel/ExternalData6.htm)
• Create dynamically expanding range name
• Add a combobox or data validation that points to a range on a different sheet
• That SQL above is obviously not right, but I assume you can write the correct SQL statement

• 创建外部数据表
• 创建参数查询（旧参考http://www.dicks-clicks.com/excel/ExternalData6.htm）
• 创建动态扩展范围名称
• 添加指向不同工作表上的范围的组合框或数据验证
• 上面那个SQL显然不对，但我假设你可以写出正确的SQL语句

You should be able to find details on all that, but if not, post another question.

您应该能够找到所有这些的详细信息，但如果没有，请发布另一个问题。