将工作表/单元格引用分配给DBQ =？ [英] Assigning a Sheet/Cell reference to DBQ=?

问题描述

正如我的网名所示，我对这个VBA的东西非常环保。 我已经获得了我需要它的代码，以便将数据从访问MDB提取到excel中。 我唯一想要的是DBQ = to 使用MDB的位置作为用户在Sheet1
Cell A1中的输入。 我在这里搜索了答案，但似乎大多数人只是直接将MDB位置放入代码中。 提前致谢。

As my screenname suggests, I'm pretty green to this VBA stuff.  I've got my code about where I need it to pull data from an access MDB into excel.  Only thing I want is the DBQ= to use the location of the MDB as input by the user in Sheet1 Cell A1.  I've searched here for the answer but seems most people simply put in the MDB location directly into the code.  Thanks in advance.

Sub proSQLQuery1（）

Dim varConnection

Dim varSQL

Sub proSQLQuery1()
Dim varConnection
Dim varSQL

范围（" A1"）。CurrentRegion.ClearContents

Range("A1").CurrentRegion.ClearContents

varConnection =" ODBC; DSN = MS Access数据库; DBQ = C：\ OUTPUT.MDB; Driver = {Driver do Microsoft Access（* .mdb）}"

varConnection = "ODBC; DSN=MS Access Database;DBQ=C:\OUTPUT.MDB; Driver={Driver do Microsoft Access (*.mdb)}"

varSQL =" SELECT t_key.model_id AS Model，t_object.name AS Parent ，t_object_1.name AS Child，t_property.name AS Property，t_period_3.month_beginning AS [Month]，t_key.timeslice_id AS Timeslice，t_data_3.value AS [Value] FROM（（（（（t_data_3 INNER JOIN t_key
ON t_data_3 .key_id = t_key.key_id）INNER JOIN t_membership ON t_key.membership_id = t_membership.membership_id）INNER JOIN t_period_3 ON t_data_3.period_id = t_period_3.month_id）INNER JOIN t_property ON t_key.property_id = t_property.property_id）INNER JOIN
t_object ON t_membership.parent_object_id = t_object.object_id）INNER JOIN t_object AS t_object_1 ON t_membership.child_object_id = t_object_1.object_id WHERE（（（t_object_1.Name）='NG'）和（（t_property.Name）='生产'）和（（ t_key.timeslice_id）= 0））ORDER
BY t_property.property_id，t_membership.membership_id，t_data_3.period_id，t_key.timeslice_id;"

varSQL = "SELECT t_key.model_id AS Model, t_object.name AS Parent, t_object_1.name AS Child, t_property.name AS Property, t_period_3.month_beginning AS [Month], t_key.timeslice_id AS Timeslice, t_data_3.value AS [Value] FROM (((((t_data_3 INNER JOIN t_key ON t_data_3.key_id = t_key.key_id) INNER JOIN t_membership ON t_key.membership_id = t_membership.membership_id) INNER JOIN t_period_3 ON t_data_3.period_id = t_period_3.month_id) INNER JOIN t_property ON t_key.property_id = t_property.property_id) INNER JOIN t_object ON t_membership.parent_object_id = t_object.object_id) INNER JOIN t_object AS t_object_1 ON t_membership.child_object_id = t_object_1.object_id WHERE (((t_object_1.Name) = 'NG') And ((t_property.Name) = 'Production') And ((t_key.timeslice_id) = 0))ORDER BY t_property.property_id, t_membership.membership_id, t_data_3.period_id, t_key.timeslice_id;"

With ActiveSheet.QueryTables.Add（Connection：= varConnection，Destination：= ActiveSheet.Range（" A1"）））
.CommandText = varSQL

.Refresh BackgroundQuery：= False

结束

With ActiveSheet.QueryTables.Add(Connection:=varConnection, Destination:=ActiveSheet.Range("A1"))
.CommandText = varSQL
.Refresh BackgroundQuery:=False
End With

结束子

推荐答案

替换此行：

varConnection =" ODBC; DSN = MS Access数据库; DBQ = C：\ OUTPUT.MDB; Driver = {Driver do Microsoft Access（* .mdb）}"

varConnection = "ODBC; DSN=MS Access Database;DBQ=C:\OUTPUT.MDB; Driver={Driver do Microsoft Access (*.mdb)}"

如果Sheet1 A1具有"OUTPUT.MDB"然后使用

If Sheet1 A1 has "OUTPUT.MDB" then use

varConnection =" ODBC; DSN = MS Access数据库; DBQ = C：\" &安培;工作表（"Sheet1"）。范围（"A1"）。值和&英寸; Driver = {Driver do Microsoft Access（* .mdb）}"

varConnection = "ODBC; DSN=MS Access Database;DBQ=C:\" & Worksheets("Sheet1").Range("A1").Value & "; Driver={Driver do Microsoft Access (*.mdb)}"

如果Sheet1 A1具有"OUTPUT"然后使用

If Sheet1 A1 has "OUTPUT" then use

varConnection =" ODBC; DSN = MS Access数据库; DBQ = C：\" &安培;工作表（"Sheet1"）。范围（"A1"）。值和& " .MDB; Driver = {Driver do Microsoft Access（* .mdb）}"

varConnection = "ODBC; DSN=MS Access Database;DBQ=C:\" & Worksheets("Sheet1").Range("A1").Value & ".MDB; Driver={Driver do Microsoft Access (*.mdb)}"

这篇关于将工作表/单元格引用分配给DBQ =？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！