范围对象/从访问excel范围插入字段 [英] range objects / inserting a field(s) from access to excel range

问题描述

我在将字段放入excel范围时遇到问题。

I am having trouble putting a field into an excel range.

当我设置我的"xldata"时（这是范围对象），是否有一种方法我可以根据字段中的值来以某种方式动态设置我的结束范围点？ 如果还有其他方法可以执行此操作，那么 是范围
对象甚至是必要的？ 

Also when I set my "xldata" (which is the range object), is there a way I can somehow set my end range point dynamically depending on how many values are in the field? And if there is another way to do this, is the range object even necessary? 

但我主要担心的是"xldata.Range.Value = Forms（"NI - Days through form"）。RecordSource" ;代码行。我应该如何改变它以使其正常工作

But my main concern is the "xldata.Range.Value = Forms("NI - Days passed form").RecordSource" line of code. How should I change it to make this work properly

我的代码：

Sub excelcreation（）

'声明对象变量

Dim xlapp作为Excel.Application

Dim xlBook As Excel.Workbook

Dim xlSheet As Excel.Worksheet

Dim xlchart As Excel.Chart

Dim xldata作为Excel.Range

Sub excelcreation()
'Declare object variable
Dim xlapp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlchart As Excel.Chart
Dim xldata As Excel.Range

推荐答案

不是最优雅的代码，但我使用此过程将访问数据提供给Excel。  函数调用需要表/查询名称或SQL语句以及报告的标题。

Not the most elegant code, but I use this procedure to get access data to excel.  The function call requires a table/query name or a SQL Statement, and a title for the report.

Function GenericExcelReport(sSelect As String, sTitle As String) As Boolean
'On Error GoTo ErrGenericExcelReport

    GenericExcelReport = False

    Dim db As Database
    Dim rsGeneric As DAO.Recordset
    
    Set db = CurrentDb
    Set rsGeneric = db.OpenRecordset(sSelect, dbOpenDynaset, dbSeeChanges)
    
    Dim ColCount As Integer
    Dim col As Integer
    Dim row As Integer
        
    Dim oExcel As Excel.Application
    Dim oWB As Excel.Workbook
    Dim oWS As Excel.Worksheet
    
    'open the spreadsheet for editing
'On Error GoTo Excel_EH
    If oExcel Is Nothing Then Set oExcel = New Excel.Application
    oExcel.Visible = True
    Set oWB = oExcel.Workbooks.Add
    Set oWS = oExcel.ActiveSheet

    
'On Error GoTo ErrGenericExcelReport


    DoEvents
    
    ColCount = rsGeneric.Fields.Count
    row = 1
    col = 0
    With oWS
                     
        If (sTitle & "" <> "") Then row = row + 2       'set up for the title if there is one
        
        .Rows(row).Font.Bold = True
        
        'set up the Column Headings and
        Do While (col < ColCount)
            .Cells(row, col + 1).Value = rsGeneric.Fields(col).Name
            
            'check if this field type is Date/Time
            If rsGeneric.Fields(col).Type = 8 Then
                'next line requires more checking, the property may not exist for each date field
                'If (rsGeneric.Fields(col).Properties("Format") = "Short Date") then .Columns(col + 1).NumberFormat = "m/d/yyyy;@"
                .Columns(col + 1).NumberFormat = "

-409] yyyy-mm-dd"
结束如果

'检查此字段类型是否为货币
如果rsGeneric.Fields（col）.Type = 5则
.Columns（col + 1）。 NumberFormat ="

-409]yyyy-mm-dd" End If 'check if this field type is Currency If rsGeneric.Fields(col).Type = 5 Then .Columns(col + 1).NumberFormat = "

＃，## 0.00"
结束如果

col = col + 1
循环

'输出数据
如果rsGeneric.EOF则
row = row + 1
col = 0
.Cells（row，col + 1）.Value ="没有要显示的记录。
.Range（.Cells（row，col + 1）,. Cell（row，ColCount））。合并
结束如果

Do while not rsGeneric.EOF
row = row + 1
col = 0
Do While（col< ColCount）
.Cells（row，col + 1）.Value = rsGeneric.Fields（col）
col = col + 1
循环

rsGeneric.MoveNext
循环


.Cells.EntireColumn.AutoFit
。 Cells.EntireRow.AutoFit
.Cells.EntireRow.VerticalAlignment = xlTop

If（sTitle&""<>""）Then
row = 1
col = 0
。行（行）.Font.Bold = True
.Cells（row，col + 1）.Value = sTitle
.Cells（row，col + 1）.WrapText = False
.Cells（row，col + 1）.Font.Size = 14
End if

结束使用


GenericExcelReport = True

退出函数

Excel_EH：
DoEvents
DoEvents
MsgBox"发生错误。请关闭excel并尝试再次运行该过程。"，vbExclamation，"没有插入分页符"
退出函数

ErrGenericExcelReport：
MsgBox"尝试生成报告时发生错误。 &安培; vbCrLf& Err.number& "：" &安培; Err.Description
退出函数

结束函数

#,##0.00" End If col = col + 1 Loop 'output the data If rsGeneric.EOF Then row = row + 1 col = 0 .Cells(row, col + 1).Value = "There are no records to display." .Range(.Cells(row, col + 1), .Cells(row, ColCount)).Merge End If Do While Not rsGeneric.EOF row = row + 1 col = 0 Do While (col < ColCount) .Cells(row, col + 1).Value = rsGeneric.Fields(col) col = col + 1 Loop rsGeneric.MoveNext Loop .Cells.EntireColumn.AutoFit .Cells.EntireRow.AutoFit .Cells.EntireRow.VerticalAlignment = xlTop If (sTitle & "" <> "") Then row = 1 col = 0 .Rows(row).Font.Bold = True .Cells(row, col + 1).Value = sTitle .Cells(row, col + 1).WrapText = False .Cells(row, col + 1).Font.Size = 14 End If End With GenericExcelReport = True Exit Function Excel_EH: DoEvents DoEvents MsgBox "An error occurred. Please close excel and try running the process again.", vbExclamation, "No Page Break Inserted" Exit Function ErrGenericExcelReport: MsgBox "An error occured while attempting to generate the report." & vbCrLf & Err.number & ": " & Err.Description Exit Function End Function

 

 

这篇关于范围对象/从访问excel范围插入字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！