在vba-excel和Sql查询中需要帮助

Doubt in vba ADO and Sql query...

怀疑在vba ADO和Sql查询...

I have 2 sheets Namely adodc1,adodc2 (In one workbook)

我有2张，即adodc1，adodc2（在一个工作簿中）

In adodc1 has columns "Name","Dept" and some times its have "Sect" column

在adodc1中有“Name”，“Dept”列，有时它有“Sect”列

In adodc2 has"Name","Dept","sect" columns

在adodc2中有“Name”，“dept”，“sect”列

what I want is when i run Query..Vba needs to check whether adodc1 have the Sect column or not ..if it have union the two sheets has usual else

我想要的是当我运行Query..Vba时需要检查adodc1是否有Sect列..如果它有两个工作表通常的联合

want to return as empty value ..

想要作为空值返回..

The below Code taken from " "altered as per my needs

以下代码取自“”根据我的需要改变

What it will do is union name and Dept column from two sheets ..now i want query to check whether adodc1 have the Column "sect" or not ..if it has

它将做的是两张表中的联合名称和部门列。现在我想要查询以检查adodc1是否具有列“sect”..如果它有

union "Sect" as usual else .. union as empty value

工会“Sect”像往常一样..工会作为空值

Sub connecttoexcel()

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer

strFile = ActiveWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile & ";Extended Properties=""Excel 12.0 XML;HDR=Yes;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
'here i want some stuff
strSQL = "Select Name, Dept from [Adodc1$] Union Select Name, Dept from [Adodc2$];"
rs.Open strSQL, cn
Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs
Set rs = Nothing
End Sub

1 个解决方案

#1

Vba needs to check whether adodc1 have the Sect column or not

Vba需要检查adodc1是否具有Sect列

I suggest you use the OpenSchema method of the Connection object to discover whether the column exists e.g. something like:

我建议你使用Connection对象的OpenSchema方法来发现列是否存在，例如就像是：

Set rs = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, "Adodc1$")
rs.Filter = "COLUMN_NAME = 'Sect'"
If rs.RecordCount = 1 Then
   ' Column exists
   ...

when it comes to check for 50 columns it will be more difficult i think ...

当检查50列时，我认为会更难...

rs.Filter = "COLUMN_NAME = 'Sect' OR COLUMN_NAME = 'Name' OR COLUMN_NAME = 'Dept' ...

or test each one in a loop using an array etc.

或者使用数组等在循环中测试每一个。

Is it possible to use NZ Function inside of the sql query

是否可以在sql查询中使用NZ函数

The NZ() function is not a function of Access SQL, which is what is being used here to access Excel data. Rather, it part of the MS Access object model. In short, unless you are running this from an Access VBA project then NZ() is not available. But the workaround is trivial e.g.

NZ（）函数不是Access SQL的函数，这是用于访问Excel数据的函数。相反，它是MS Access对象模型的一部分。简而言之，除非您从Access VBA项目运行此项，否则NZ（）不可用。但是解决方法很简单，例如

Nz(Dept, '{{NONE}}')

has the same effect as

具有相同的效果

IIF(Dept IS NULL, '{{NONE}}', Dept)

I've read Access MVPs (Allen Browne?) say this is preferable to Nz() anyhow.

我读过Access MVP（Allen Browne？）说无论如何这比Nz（）更好。

#1