VBA SQL连接带有密码的excel表

betar

jokklx 发表于 2016-10-18 17:31
不行，这种情况下，SQL不能连接，用getobject都不行，只能用workbooks.open打开

版主提供的那个链接里面有个work around, 还没尝试过，似乎应该也不可以使用？

1. 'Open the Excel workbook to prompt for the password
   
2. Dim xl As Object
   
3. Set xl = GetObject(CurrentProject.Path & "" & "MASTER JAN 2009.xls")
   
4. 
   
5. 'Now open the workbook to read/write/whatever
   
6. Dim cnn1 As New ADODB.Connection
   
7. Dim rst1 As ADODB.Recordset
   
8. Dim strExcelName As String = "MASTER JAN 2009.xls"
   
9. Dim strWkBkName As String = "[MASTER 2008$]"
   
10. cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
    
11. "Data Source=" & CurrentProject.Path & "" & strExcelName & ";" _
    
12. "Extended Properties=""Excel 8.0;"";"
    
13. Set rst1 = New ADODB.Recordset
    
14. rst1.Open strWkBkName, cnn1, , , adCmdTable
    
15. Do Until rst1.EOF
    
16.     'process your data here
    
17.     rst1.MoveNext
    
18. Loop

复制代码

betar

有大神帮忙看看吗？代码应该怎么改才可以不报错？

betar

microyip 发表于 2016-10-19 15:18
一样是使用原来的语句啊

烦请大神详细说明一下如何连接已经打开的工作簿，我这边的用workbooks.open打开工作簿以后，按照正常的SQL操作会出现找不到目标的错误[Sheet1$A1:DH1048576]。万分感谢！

microyip

1. Sub test()
   
2.     Dim File As Workbook
   
3.     Dim cnn
   
4.     Dim SQL$
   
5.     Dim Path, Text1, SheetName As String
   
6.    
   
7.    
   
8.     Path = ThisWorkbook.Path & ""
   
9.     Text1 = "test.xlsx"
   
10.     SheetName = "Sheet1"
    
11.    
    
12.    
    
13.     With Sheets("Sheet1")
    
14.         .[A:E].ClearContents
    
15.    
    
16.         Set File = Workbooks.Open(Filename:=Path & Text1, Password:="123")
    
17.         
    
18.         Set cnn = CreateObject("adodb.connection")  'Create DB Connection
    
19.         cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;extended properties=Excel 12.0;Data Source=" & Path & Text1
    
20.         
    
21.         SQL = "select [Loan No#] from [" & SheetName & "$A:DH]"
    
22.         .[A65536].End(xlUp).Offset(1, 0).CopyFromRecordset cnn.Execute(SQL)
    
23.    
    
24.         cnn.Close     'Close SQL Connection
    
25.         Set cnn = Nothing    'Clean SQL from RAM
    
26.    
    
27.         File.Close True
    
28.     End With
    
29. End Sub

复制代码

betar

microyip 发表于 2016-10-20 13:42

有个小问题，这个$A:DH似乎不能支持超过65536行的数据，超过的部分不会被SQL连接捕捉到，也就不会输出。如果强制添加超过65536的行数，就会报错。比如如果写$A1:DH66666，就会提示找不到Sheet1$A1:DH66666.

microyip

betar 发表于 2016-10-20 17:04
有个小问题，这个$A:DH似乎不能支持超过65536行的数据，超过的部分不会被SQL连接捕捉到，也就不会输出。 ...

Excel使用SQL语言是有行数限制的

betar

microyip 发表于 2016-10-20 17:38
Excel使用SQL语言是有行数限制的

其实，如果目标文件没有密码，直接用Microsoft.ACE.OLEDB.12.0进行SQL连接是可以支持xlsx最大行数1048576的，所以不太明白为什么使用了workbooks.open打开文件后就不能支持了。

betar

betar 发表于 2016-10-20 17:50
其实，如果目标文件没有密码，直接用Microsoft.ACE.OLEDB.12.0进行SQL连接是可以支持xlsx最大行数1048576 ...

在另一个贴子里看到了版主的解答，的确有这样的限制。感谢！


原帖由 minjiwei 于 2011-6-7 18:17 发表

很不幸，我用的是
cn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=Yes';Data Source=" & ThisWorkbook.FullName
这个是怎么回事啊？

自连接不能突破65536行限制
把要连接的工作表另存为一个新的xlsx文件后再连接就可以了，如：
cn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=Yes';Data Source=" & ThisWorkbook.Path & "\123.xlsx"

补充内容 (2015-2-9 17:06):
2007后ADO存在一个Bug，连接工作簿处于打开状态，工作表如果指定区域，则只能查询65536行以内的数据，解决办法：
一、去掉区域，[明细$a1:p]改为[明细$]
或者
二、继续坚持指定区域([明细$a1:p])，放弃自连接，把数

betar

想到一个笨办法，可以先用workbooks.open把目标文件打开，另存为一个无密码的版本，再直接用SQL连接进行后续操作。

1. ActiveWorkbook.SaveAs Filename:=FilePath, FileFormat:=xlWorkbookDefault, CreateBackup:=False, Password:=""

复制代码

microyip

betar 发表于 2016-10-20 17:50
其实，如果目标文件没有密码，直接用Microsoft.ACE.OLEDB.12.0进行SQL连接是可以支持xlsx最大行数1048576 ...

在打开状态下，只支持65536行