控制在msaccess中将查询导出到的电子表格 [英] control what spreadsheet a query is exported to in msaccess

查看：57
本文介绍了控制在msaccess中将查询导出到的电子表格的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个查询，该查询会根据我设置的条件进行更改.将查询导出到excel电子表格后，查询结果将更改.

这是查询工作方式的一个示例

类别= A，B，C

FILTER = D，E，F

结果将如下所示:

AxF <​​/p>

将其导出到电子表格后，我将再次运行相同的查询，但是使用不同的参数集，例如:

CxD

(不是将结果发送到不同的查询，而是在vba中重写了输出查询的sql，这样做是有原因的)，但是在导出导出的查询时，由于某种原因，它将请执行以下任一操作:

  1. 覆盖excel中已经存在的数据

  2. 与excel中已经存在的数据结合

  3. 删除电子表格中的所有数据

有什么方法可以控制将查询放入哪个电子表格?

我希望它可以像以下那样工作:

  • 工作表编号= outquery(CATEGORYxFILTER)
  • sheet1 = outquery(AxF)
  • sheet2 = outquery(CxD)
  • sheet3 = outquery(BxE)

由于查询条件每次被更改，因此发布查询代码将毫无意义.

但我会举一个更好的例子:

让x代表第一个参数 令y代表第二个参数 让A代表第一类 令B代表第二类

从*中选择*，其中A = x和B = y

说我有一个下表(这只是一个例子)

将该表命名为EXTABLE

NAME ------- BDAY ------- AGE ------- GENDER ------- COUNTRY

AMANDA ----- 07/04 ------ 21 -------- FEMALE -------美国

MAX -------- 09/17 ------ 30 -------- MALE --------- USA

SARA ------- 05/03 ------ 18 --------女性-------英国

MAX -------- 09/17 ------ 21 -------- MALE --------- ENGLAND

ALEXIS ----- 10/25 ------ 37 --------女性-------法国

PIERRE ----- 07/04 ------ 30 -------- MALE --------- FRANCE

我的查询有时可能很像

从*可能的年龄= 21 AND GENDER ="MALE"中选择*

但是下次我可能会查询该查询:

选择*来自稳定的国家/地区=英国"和BDAY ="05/17"

通过vba更改sql的原因是，不仅条件的参数在更改，而且参数所应用的类别也在更改

解决方案

尽管我不得不考虑一下，但我仍然可以确定您可以使用参数查询来做到这一点.

要获得您可以使用的功能，我想出了这一点(很抱歉，目前有多个查询):

首先，我创建了两个查询作为存储查询:

AgeGender 

PARAMETERS Person_Age Long, Person_Gender Text ( 255 );
SELECT *
FROM EXTABLE
WHERE Age = Person_Age AND Gender = Person_Gender;

CountryBday 

PARAMETERS Person_Country Text(255), Person_Bday DateTime;
SELECT      *
FROM        EXTABLE
WHERE       Country = Person_Country AND Bday = Person_Bday

这是导出两个查询的主要过程:

Public Sub ProcessQuery()

    Dim oXL As Object
    Dim oWrkBk As Object
    Dim oWrkSht As Object

    Set oXL = CreateXL
    Set oWrkBk = oXL.Workbooks.Add(-4167) 'xlWBATWorksheet - creates a workbook with 1 sheet.
    Set oWrkSht = oWrkBk.Worksheets(1)

    Export_To_XL "AgeGender", oWrkSht.Range("A1"), "Person_Age", 47, "Person_Gender", "Female"

    Set oWrkSht = oWrkBk.Worksheets.Add 'This will now have a reference of your new sheet.
    Export_To_XL "CountryBday", oWrkSht.Range("A1"), "Person_Country", "England", "Person_Bday", #5/3/1971#

End Sub

用于处理查询并将其导出到Excel的代码. ParamArray必须包含两个参数的集合-参数名称及其值.

Public Sub Export_To_XL(sQueryName As String, PasteRange As Object, ParamArray Params())

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field
    Dim pElement As Long

    On Error GoTo ERR_HANDLE

    Set db = CurrentDb
    Set qdf = db.QueryDefs(sQueryName)
    With qdf
        For pElement = LBound(Params) To UBound(Params) Step 2
            .Parameters(Params(pElement)) = Params(pElement + 1)
        Next
    End With
    Set rst = qdf.OpenRecordset

    With rst
        If Not (.BOF And .EOF) Then

            For Each fld In rst.Fields
                PasteRange.offset(, fld.OrdinalPosition) = fld.Name
            Next fld
            PasteRange.Resize(, rst.Fields.Count).Font.Bold = True
            PasteRange.offset(1).CopyFromRecordset rst
            PasteRange.Parent.Columns.Autofit

        End If
    End With

EXIT_PROC:

    Set rst = Nothing

        On Error GoTo 0
        Exit Sub

ERR_HANDLE:
    Select Case Err.Number

        Case Else
            MsgBox "Error " & Err.Number & vbCr & _
                " (" & Err.Description & ") in procedure Export_To_XL."
            Resume EXIT_PROC
    End Select

End Sub

您将需要代码来创建Excel实例:

Public Function CreateXL(Optional bVisible As Boolean = True) As Object

    Dim oTmpXL As Object

    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Defer error trapping in case Excel is not running. '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    On Error Resume Next
    Set oTmpXL = GetObject(, "Excel.Application")

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'If an error occurs then create an instance of Excel. '
    'Reinstate error handling.                            '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If Err.Number <> 0 Then
        Err.Clear
        On Error GoTo ERROR_HANDLER
        Set oTmpXL = CreateObject("Excel.Application")
    End If

    oTmpXL.Visible = bVisible
    Set CreateXL = oTmpXL

    On Error GoTo 0
    Exit Function

ERROR_HANDLER:
    Select Case Err.Number

        Case Else
            MsgBox "Error " & Err.Number & vbCr & _
                " (" & Err.Description & ") in procedure CreateXL."
            Err.Clear
    End Select

End Function

对，现在只需要弄清楚如何将100个查询合并为1个即可....

修改:
该SQL未经过测试，但我在考虑以下方面-

PARAMETERS  Person_Name Text(255), Person_Bday DateTime, Person_Age Long, 
            Person_Gender Text(255), Person_Country Text(255);
SELECT      *
FROM        EXTABLE
WHERE       IIF(ISNULL(Person_Name),TRUE,sName = Person_Name) AND
            IIF(ISNULL(Person_Bday),TRUE,Bday = Person_Bday) AND
            IIF(ISNULL(Person_Age),TRUE,Age = Person_Age) AND
            IIF(ISNULL(Person_Gender),TRUE,Gender = Person_Gender) AND
            IIF(ISNULL(Person_Country),TRUE,Country = Person_Country)

I have a query which changes based on the criteria I set. The query's result will be changed after exporting the query to a excel spreadsheet.

This is an example of how the query works

CATEGORY = A,B,C

FILTER = D,E,F

The outcome would be something like the following:

AxF

After exporting it to a spreadsheet, I will run the same query again, but with a different set of parameters, such as:

CxD

(Instead of sending the results to different queries, I rewrite the output query's sql in vba, and there is a reason for doing that), but when it comes to exporting the query that is returned, for some reason, it will do one of the following:

  1. override the data that is already in excel

  2. combine with the data that is already in excel

  3. erase all data in spreadsheet

Is there any way to control what spreadsheet a query is out put to?

i would like it to work like the following:

  • sheet# = outquery(CATEGORYxFILTER)
  • sheet1 = outquery(AxF)
  • sheet2 = outquery(CxD)
  • sheet3 = outquery(BxE)

Because the conditions for the query change every time it is called, posting my query code would be pointless.

but I shall give a better example:

let x represent first parameter let y represent second parameter let A represent first category Let B represent second category

select * from * Where A=x and B=y

say i have a table of the following (this is just an example)

let the table be called EXTABLE

NAME-------BDAY-------AGE-------GENDER-------COUNTRY

AMANDA-----07/04------21--------FEMALE-------USA

MAX--------09/17------30--------MALE---------USA

SARA-------05/03------18--------FEMALE-------ENGLAND

MAX--------09/17------21--------MALE---------ENGLAND

ALEXIS-----10/25------37--------FEMALE-------FRANCE

PIERRE-----07/04------30--------MALE---------FRANCE

MY QUERY MAY SOMETIMES BE SOMETHING LIKE:

SELECT * FROM EXTABLE WHERE AGE = 21 AND GENDER = "MALE"

BUT THE NEXT TIME I CALL THE QUERY IT COULD BE:

SELECT * FROM EXTABLE WHERE COUNTRY = "ENGLAND" AND BDAY = "05/17"

the reason for changing the sql through vba is because not only are the parameters of the conditions changing, but so are the categories to which the parameters are applied

解决方案

I'm still sure you can do it with parameter queries, although I'll have to have a think about it.

To get something you can work with I've come up with this (sorry, there's multiple queries at the moment):

First I created the two queries as stored queries:

AgeGender 

PARAMETERS Person_Age Long, Person_Gender Text ( 255 );
SELECT *
FROM EXTABLE
WHERE Age = Person_Age AND Gender = Person_Gender;

CountryBday 

PARAMETERS Person_Country Text(255), Person_Bday DateTime;
SELECT      *
FROM        EXTABLE
WHERE       Country = Person_Country AND Bday = Person_Bday

This is the main procedure to export the two queries: 

Public Sub ProcessQuery()

    Dim oXL As Object
    Dim oWrkBk As Object
    Dim oWrkSht As Object

    Set oXL = CreateXL
    Set oWrkBk = oXL.Workbooks.Add(-4167) 'xlWBATWorksheet - creates a workbook with 1 sheet.
    Set oWrkSht = oWrkBk.Worksheets(1)

    Export_To_XL "AgeGender", oWrkSht.Range("A1"), "Person_Age", 47, "Person_Gender", "Female"

    Set oWrkSht = oWrkBk.Worksheets.Add 'This will now have a reference of your new sheet.
    Export_To_XL "CountryBday", oWrkSht.Range("A1"), "Person_Country", "England", "Person_Bday", #5/3/1971#

End Sub

Code to process your queries and export them to Excel. The ParamArray must takes sets of two parameters - the parameter name and its value.

Public Sub Export_To_XL(sQueryName As String, PasteRange As Object, ParamArray Params())

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field
    Dim pElement As Long

    On Error GoTo ERR_HANDLE

    Set db = CurrentDb
    Set qdf = db.QueryDefs(sQueryName)
    With qdf
        For pElement = LBound(Params) To UBound(Params) Step 2
            .Parameters(Params(pElement)) = Params(pElement + 1)
        Next
    End With
    Set rst = qdf.OpenRecordset

    With rst
        If Not (.BOF And .EOF) Then

            For Each fld In rst.Fields
                PasteRange.offset(, fld.OrdinalPosition) = fld.Name
            Next fld
            PasteRange.Resize(, rst.Fields.Count).Font.Bold = True
            PasteRange.offset(1).CopyFromRecordset rst
            PasteRange.Parent.Columns.Autofit

        End If
    End With

EXIT_PROC:

    Set rst = Nothing

        On Error GoTo 0
        Exit Sub

ERR_HANDLE:
    Select Case Err.Number

        Case Else
            MsgBox "Error " & Err.Number & vbCr & _
                " (" & Err.Description & ") in procedure Export_To_XL."
            Resume EXIT_PROC
    End Select

End Sub

You'll need code to create an instance of Excel: 

Public Function CreateXL(Optional bVisible As Boolean = True) As Object

    Dim oTmpXL As Object

    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Defer error trapping in case Excel is not running. '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    On Error Resume Next
    Set oTmpXL = GetObject(, "Excel.Application")

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'If an error occurs then create an instance of Excel. '
    'Reinstate error handling.                            '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If Err.Number <> 0 Then
        Err.Clear
        On Error GoTo ERROR_HANDLER
        Set oTmpXL = CreateObject("Excel.Application")
    End If

    oTmpXL.Visible = bVisible
    Set CreateXL = oTmpXL

    On Error GoTo 0
    Exit Function

ERROR_HANDLER:
    Select Case Err.Number

        Case Else
            MsgBox "Error " & Err.Number & vbCr & _
                " (" & Err.Description & ") in procedure CreateXL."
            Err.Clear
    End Select

End Function

Right, just need to figure out how to combine 100 queries into 1 now....

Edit:
This SQL isn't tested, but I'm thinking something along these lines - 

PARAMETERS  Person_Name Text(255), Person_Bday DateTime, Person_Age Long, 
            Person_Gender Text(255), Person_Country Text(255);
SELECT      *
FROM        EXTABLE
WHERE       IIF(ISNULL(Person_Name),TRUE,sName = Person_Name) AND
            IIF(ISNULL(Person_Bday),TRUE,Bday = Person_Bday) AND
            IIF(ISNULL(Person_Age),TRUE,Age = Person_Age) AND
            IIF(ISNULL(Person_Gender),TRUE,Gender = Person_Gender) AND
            IIF(ISNULL(Person_Country),TRUE,Country = Person_Country)

这篇关于控制在msaccess中将查询导出到的电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆