excel - Excel - 接收运行时错误 1004 ODBC 错误

Question

我对 VB 代码非常陌生，并试图解决问题。一些用户可以正常运行此宏，但有些用户会收到运行时错误 1004，一般 ODBC 错误。Debug 指向下面的最后一行。对我来说很奇怪，它被设置为简单地连接到文件共享的目录。有谁知道可能发生了什么？我已验证他们有权访问该共享。

  Option Explicit
  Sub CreatePolicyTable()
Dim qryTable As QueryTable
Dim rngDestination As Range
Dim strConnection As String
Dim strSQL As String
Dim strParam1 As String
Dim strParam3 As String

Worksheets("Pol Data").Range("A1:A1048576").EntireRow.ClearContents
Call Delete_Named_Ranges

'Define the connection string and destination range.
strConnection = "ODBC;Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
    "DBQ=\\My Folder\;" & _
    "Extensions=asc,csv,tab,txt;"
Set rngDestination = Sheet2.Range("A1")

'Create the SQL string.
strSQL = "SELECT * FROM My_File.csv"

'set the parameter strings
strParam1 = " WHERE state = '" & Sheet5.ComboBox1.Text & "'"
strParam3 = " ;"

'check & build variable parameters depending on whether combobox ticked by user
   strSQL = strSQL & strParam1 & strParam3

'Create the QueryTable.
Set qryTable = Sheet2.QueryTables.Add(strConnection, rngDestination)

'Populate the QueryTable.
qryTable.CommandText = strSQL
qryTable.CommandType = xlCmdSql
qryTable.BackgroundQuery = False
qryTable.Refresh

'Create Named Ranges
ActiveWorkbook.Names.Add Name:="PolData", RefersToR1C1:= _
    "=OFFSET('Pol Data'!R1C1,0,0,COUNTA('Pol Data'!C1),COUNTA('Pol Data'!R1))"
End Sub

Sub Refresh_All()
Call CreatePolicyTable
Worksheets("Competitive Analysis").PivotTables("1").PivotCache.Refresh
Worksheets("Competitive Analysis").Activate
End Sub

Sub Delete_Named_Ranges()
Dim wbBook As Workbook
Dim nName As Name
Set wbBook = ActiveWorkbook
For Each nName In wbBook.Names
    nName.Delete
Next nName
End Sub

标签： excelvba