VBA ADO Excel 2010 [英] VBA ADO Excel 2010

问题描述

我正在使用带有46个数据透视表的Excel文件.下面的代码将每个数据透视表的连接更改为新的现有连接.

Hi I am working in a excel file with 46 pivot tables. The code below changes the connection of each pivot table to a new existing connection.

Sub changeConnection()
  Dim pTable As Variant
  Dim sheet As Variant
  Dim workBookName As String 

  workBookName = "filename.xlsm"      

  For Each sheet In Workbooks(workBookName).Worksheets
    For Each pTable In sheet.PivotTables
       pTable.changeConnection Workbooks(workBookName ).Connections("connection name")
    Next pTable
  Next sheet

End Sub

我希望数据透视表的所有内容保持不变，但是我想在连接到的文件上输入密码.由于excel无法做到这一点，因此我使用ADO访问受密码保护的excel文件.

I want everything to stay the same for my pivot tables but I want a password on the file that I am connected to. Since excel can not do this I used ADO to access a password protected excel file.

Public Function readFile()
Dim xl As Object
Dim conn As New ADODB.connection
Dim recSet As ADODB.Recordset
Dim conString As String
Dim wkbName As String
Dim SQL As String
Dim DBPath As String

'Path to excel file
DBPath = "path\to\file.xlsm"
Set xl = GetObject(DBPath)

'Name of table
wkbName = "[IS$]"

conString = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"

'Query
SQL = "select * from " & wkbName

'Open connection
conn.Open conString

'Itterate over record set
Set recSet = New ADODB.Recordset
recSet.Open SQL, conn

'Print out col1 from table
Do Until recSet.EOF
    'process your data here
    Debug.Print recSet!ISData
    recSet.MoveNext
Loop
End Function

上面的代码将访问外部存储的受密码保护的工作簿内的表.然后使用记录集打印出所有项目的调试信息.

The code above will access a table inside of the password protected workbook stored externally. Then using a record set print out in debug all the items.

我实际上希望在第二段代码中使用我的变通办法，以便我可以替换所有数据透视表连接，以便我的数据源可以带有密码.我所有的数据透视表都指向相同的连接，因此使用相同的连接不会引起问题.

I want to essentially use my workaround in the second snippet of code so I can replace all my pivot table connections so my data source can have a password on it. All my pivot tables point to the same connection so using the same connection won't cause issues.

预先感谢，如果有任何需要澄清的地方，请发表评论.

Thank in advance and please comment if I should clarify anything.

推荐答案

IIR，没有数据提供者可以执行此操作.即使您尝试将密码存储在连接字符串中，该驱动程序也会给出无法解密文件"的错误信息.

IIR there isn't a data provider that can do this. That driver will give an error to the effect of "could not decrypt file" even if you attempt to store the password in the connection string.

第二部分代码基本上是解决此问题的工具，它依靠Excel来管理用户的凭据提示.它确实 不 解决了您无法在连接字符串中提供密码这一事实-这是一种变通方法.鉴于您无法提供适用于ADO的连接字符串，因此也无法将其提供给存储的连接字符串.

The second bit of code is basically a hack to get around this and it relies on Excel to manage the credential prompt from the user. It does not solve the fact that you can't supply a password in your connection string - it is a work-around. Given that you can't supply a connection string that works to ADO, you're not going to be able to supply it to the stored connection string either.

我建议后端使用实际数据库而不是Excel文件.这将为您提供更大的管理用户访问权限的灵活性.

I would suggest using an actual database for the back end instead of an Excel file. This will give you much more flexibility in managing user access.

这篇关于VBA ADO Excel 2010的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！