如何禁用“VBA中的选择数据源窗口” [英] How to disable the "Select Data source window in VBA"

问题描述

我有一个具有宏功能的excel文件，用于将数据从excel上传到数据库。 excel中有一个按钮来运行这个代码。当我运行代码时，它有一个窗口弹出选择数据源。有没有人如何防止这种情况爆发。

谢谢

可悲的是，作为一个初学者，我无法提供图片，所以我会解释一下。

点击按钮。打开名为选择数据源的窗口，其中有三个选项：dBASE文件，Excel文件，MS Access数据库。我需要选择其中之一并继续。对我来说，这是非常好的，所以我点击这个并按OK。然后可以选择允许我选择工作簿。有没有办法预先做这个，所以我看不到这个窗口。

下面的代码显示了这个问题。

  Sub Collect_campaigns（）
 Dim qryCampaigns As String 
 Application.ScreenUpdating = False 
 qryCampaigns =SELECT * FROM test.tbl_test; 
工作表（广告系列）Visible = True 
 
表格（广告系列）选择
范围（A：F）选择
选择。清除
范围（A1）。选择
使用ActiveSheet.ListObjects.Add（SourceType：= 0，Source：= _ 
ODBC; DSN = TEST_DATASOURCE;，Destination：= Range （$ A $ 1））QueryTable 
 .CommandText = qryCampaigns 
 .Refresh BackgroundQuery：= False 
结束
  

解决方案

  With ActiveSheet.ListObjects.Add（SourceType：= 0，Source：= _ 
ODBC; DSN = TEST_DATASOURCE;，Destination：= Range（$ A $ 1））QueryTable 
  

如上面的代码所示，您的宏指的是名为TEST_DATASOURCE的ODBC DataSource。我只能假设你已经从其他计算机复​​制了这个文件，如果你也需要在你的机器上创建相同的ODBC数据源。

如果你的机器正在运行Windows您应该检查控制面板 - >管理工具 - >数据源（ODBC）。

I have a excel file with macro-enabled that is used to upload the data from excel into a database. There's a button in excel to run this code. When I run the code, it has a window pop-up "Select Data Source". Does anyone how to prevent this from popping up.

Thank You

Sadly as a beginner I cannot provide the picture so I will explain.

When the button is clicked. A window is opened named "Select Data Source" with three options dBASE Files, Excel Files, MS Access Database. I need to select one of them and continue on. For me it's excel so I click this and press okay. Then it has the option of allowing me to select the workbook. Is there a way to pre-do this before so that I don't see this window.

The code is shown Below, which gives this problem.

Sub Collect_campaigns()
Dim qryCampaigns As String
Application.ScreenUpdating = False
qryCampaigns = "SELECT * FROM test.tbl_test;"
Worksheets("Campaigns").Visible = True

Sheets("Campaigns").Select
Range("A:F").Select
Selection.Clear
Range("A1").Select
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "ODBC;DSN=TEST_DATASOURCE;", Destination:=Range("$A$1")).QueryTable
.CommandText = qryCampaigns
.Refresh BackgroundQuery:=False
 End With

解决方案

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=TEST_DATASOURCE;", Destination:=Range("$A$1")).QueryTable

As you can see in the code above, your macro refers to ODBC DataSource named TEST_DATASOURCE. I can only assume that you have copied this file from other computer, if so you also need to create same ODBC Datasource on your machine too.

If your machine is running Windows you should check "Control Panel-->Administrative Tools-->Data Sources(ODBC)".

这篇关于如何禁用“VBA中的选择数据源窗口”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！