Excel VBA - UsingVBA创建一个新的格式化工作簿 [英] Excel VBA - UsingVBA to create a new, formatted workbook

问题描述

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

要开始我将把产品供应商的名称和每个供应商的名称一起制作一份工作表，然后我想通过每张表单循环并打印每个订购供应商的产品。

我真的很沮丧地围着如何打开一个新的工作簿，并在我的信息中打印。

解决方案

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

如果要从Access到Excel输出数据，则必须遵循以下步骤：

1. 创建（或打开）新的工作簿


2. 读取您的数据


3. 将数据写入工作簿


4. 格式化工作簿中的数据

我将专注于数据输出，并保留格式化（数据部分是复杂的一个...格式化很简单）

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

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

  public sub createExcelFile（）
 dim XL as Excel。应用程序，WB作为Excel.Workbook，WKS作为Excel.Worksheet 
 dim db as DAO.database，rec as DAO.recordset，f as DAO.field 
 dim i as integer，j as integer 
 
'准备你的Excel东西
设置XL =新的Excel.Application 
 XL.Visible = True 
设置WB = XL.Workbooks.Add 
 WB.Activate 
设置WKS = WB.ActiveSheet'默认值：新创建的书中的第一张图书
 
'在此读取您的数据
 set db = currentdb（）
 set rec = db.openrecordset（tblSampleData）
 
'将从rec 
'i和j中显示数据的简单表将是工作表中活动单元格的协调元素
用
 .movefirst 
 
'表标题
i = 1 
j = 1 
每个f中的.fields 
 WKS.cells（i，j）.value = f.name 
j = j + 1 
 next f 
 
'表数据
 do 
i = i + 1 
j = 1 
对于fields中的每个f 
 WKS.cells（i，j）.value = f.value 
j = j + 1 
 next f 
 .moveNext 
循环直到.EOF 
结束
 end sub 
  

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

看看我之前的链接（Siddarth Rout很善于移动评论）

我希望这有助于您

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.

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.

解决方案

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:

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

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 Library and activate the checkbox. Now you have the full Excel library at your service :-)

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.

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

I hope this helps you

这篇关于Excel VBA - UsingVBA创建一个新的格式化工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！