在VBScript的sql字符串中使用excels命名范围 [英] Using excels named range in a sql string in VBScript

问题描述

我在这里和Google上搜索过，但我仍然无法解决我的问题。我试图在我的 .vbs 文件中使用一个excel的命名范围。下面的工作在VBA中的excel，但我不能得到它在一个 *。vbs 文件。

I have searched on here and google but I still cannot solve my issue. I am trying to use an excel's named range equivalently in my .vbs file. The below works in VBA in excel but I cannot get it to work in a *.vbs file.

ThisWorkbook.Sheets(1).Range("A1:B" & Range("B" & Rows.Count).End(xlUp).Row).Name = "DATA"
strSql = "SELECT * FROM DATA"

所以，我尝试过不同的引用方式我的命名范围 DATA 没有运气。

这是我现在的：

So, I have tried different variations of referencing to my named range DATA with no luck.
This is what I have now:

Set rng = ws.Range("A1:B2")
rng = "DATA"    
strSql = "SELECT * FROM DATA"

有一些不同的变化：参数ByVal，使用rng而不是DATA（字符串类型）， SELECT * FROM & rng等等。

Some different variations involved: taking parameter ByVal, using rng instead of DATA (string type), SELECT * FROM " & rng, etc..

运行时错误msg：

Microsoft（R）Windows Script Host Version 5.8版权所有（C）Microsoft
Corporation。保留所有权利。

Microsoft (R) Windows Script Host Version 5.8 Copyright (C) Microsoft Corporation. All rights reserved.

C： \\Users\admin\Desktop\UpdateSourceTbl.vbs（119， 5）Microsoft
JET数据库引擎：Microsoft Jet数据库引擎找不到
对象'DATA'。确定对象存在，并且您正确地拼写
其名称和路径名。

C:\Users\admin\Desktop\UpdateSourceTbl.vbs(119, 5) Microsoft JET Databas e Engine: The Microsoft Jet database engine could not find the object 'DATA'. M ake sure the object exists and that you spell its name and the path name correctly.

任何帮助，非常感谢！

Any help greatly appreciated!

临时解决方案：
我使用2行参数，可能不是最好的解决方案 - 但它的工作原理！我不能看到没有什么不对的公平

TEMPORARY SOLUTION: I used 2 parameters with row numbers, may not be the best solution - but it works! and i cant see nothing wrong with it to be fair

Call createAndInsertRecordSet(wb.FullName, ws.Name, i+1, j-1)
Sub CreateAndInsertRecordSet(ByVal fullname, ByVal wsName, ByVal stRow, byVal enRow )
strSql = "SELECT * FROM [" & wsName & "$B" & stRow & ":AX" & enRow & "]"

推荐答案

工作可以是不可预测的，有时我们有从前一段回去修改，修复，重用代码。今天发生了。我已经回去并转载了我的代码，并第一次工作。当我发布这个问题，看错误的线条或程序或奇怪的东西时，我一直在做错事。以下代码完美运行。

it：

- 打开工作簿

- 与工作簿建立连接以检索记录集中的数据

- 打开与数据库的连接并执行样本插入状态

运行代码后，我检查了临时数据库，这些值已插入，因此我可以确认这是我的工作解决方案对原来提出的问题。

Work can be unpredictable and sometimes we have to go back and modify, fix, reuse code from a while ago. It happened today. I have gone back and reproduced my code and got it working the first time. I must have been doing something wrong when I posted this question, looking at the wrong lines or procedures or something strange. The below code runs perfectly.
it:
- opens a workbook
- establishes a connection with the workbook to retrieve data in a recordset
- opens up a connection to a database and executes a sample insert statment
After running the code I have checked the Temporary databases, the values have been inserted, so I can confirm this is my working solution to the problem originally raised.

Option Explicit

Private Const adUseClient = 3
Dim xl, wb, ws, fPath, rng

fPath = "C:\Users\admin\Desktop\Book1.xlsm"

Call OpenFile()
Call InsertRecordset()
Call CloseFile()

Private Sub OpenFile()
    Set xl = CreateObject("Excel.Application")
    xl.Visible = False
    Set wb = xl.Workbooks.Open(fPath)
    Set ws = wb.Sheets(1)
End Sub

Private Sub CloseFile()
    wb.Saved = True
    wb.Close
    xl.Quit
    Set wb = Nothing
    Set xl = Nothing
End Sub

Private Sub InsertRecordset()

    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & wb.fullname & ";Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"";"
    Dim cn, rs, strCon, strSql, cn2

    ws.Range("A1:B2").Name = "DATA"
    strSql = "SELECT * FROM DATA"

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    cn.Open strCon          
    rs.Open strSql, cn      

    Set cn2 = CreateObject("ADODB.Connection")
    With cn2
        .CursorLocation = adUseClient
        .Open "Driver={SQL Server};Server=HELIUM\PRI; Database=TEMPORARY; UID=admin; PWD=password"
        .CommandTimeout = 0
        rs.MoveFirst
        Do While Not rs.EOF
            .Execute "INSERT INTO TEMPORARY.dbo.TEMP_TABLE ( [TEMP_COLUMN] ) VALUES ('" & rs.Fields(1) & "')"
            rs.MoveNext
            Loop
    End With

    ' Close connections
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    cn2.Close
    Set cn2 = Nothing
End Sub

这篇关于在VBScript的sql字符串中使用excels命名范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！